database grinds drogheda area

CheesePlease

I have been tasked with the job designing a database with microsoft access. I have only basic experience with database design and hoped to get some help with it. If there was someone in the drogheda area who could give grinds to help get me started?

Graham

If you post specifics about the areas you're struggling with there's loads of people here able to help.

amen

Why ms access ? What are your requirements in terms of users, backups reliability etc

CheesePlease

amen wrote: »

Why ms access ? What are your requirements in terms of users, backups reliability etc

i'd say they will have about 20 computers accessing the database. they have microsoft access already installed so I thought just to go with that. I think its quite an old version. maybe 2003. I know they all run xp. The database will be based on a form filled out by a patient admitted to hospital. The form is quite complex with multiple options for answers. one for admittance and one for discharge. They currently don't have a server on the network, well there is a remote server that runs the intranet but I don't have access to that.

ChRoMe

CheesePlease wrote: »

i'd say they will have about 20 computers accessing the database. they have microsoft access already installed so I thought just to go with that. I think its quite an old version. maybe 2003. I know they all run xp. The database will be based on a form filled out by a patient admitted to hospital. The form is quite complex with multiple options for answers. one for admittance and one for discharge. They currently don't have a server on the network, well there is a remote server that runs the intranet but I don't have access to that.

I would *strongly* advise you to try and find a web based solution where you can create your own forms. What you have just described is going to be incredibly painful and most likely very expensive considering the setup.

Ziycon

Also be very conscious that the information being stored is very personal information so the route you go for storing the information would need to be secure, if at all possible I would try use an existing PC of theirs that is on-site or try persuade them to get a server for this specific project that will reside on-site and use a more modern database.

CheesePlease

ChRoMe wrote: »

I would *strongly* advise you to try and find a web based solution where you can create your own forms. What you have just described is going to be incredibly painful and most likely very expensive considering the setup.

firstly excuse me if my questions seem quite obvious, I'm quite a novice in this whole area. Where specifically could you see the costs occuring? Would it be a good idea to persuade managment to purchase a server to run the database off? if so would access be a viable option? I'm beginning to think this task may be beyond me due to lack of experience. I've just finished a ordinary degree in computers but our course only brushed upon databases in first year. thanks for the replies

Graham

I definitely wouldn't be recommending Access as a back end database. Although Access can theoretically support multiple users/sessions, it can get slow very quickly and if many are updating simultaneously its not unknown for the database to become corrupt.

Did you suggest access to them or have you just managed to land a contract for a hospital with the tightest/stingiest IT budget ever?

Amirani

I'd definitely recommend a web-based database built in MSSQL or MySQL. Probably the latter if you have budgetary concerns.

Have you done much HTML or PHP in your degree?

ChRoMe

Graham wrote: »

Did you suggest access to them or have you just managed to land a contract for a hospital with the tightest/stingiest IT budget ever?

Its a fresh grad being tasked with this, there is obviously no budget.

ChRoMe

Ziycon wrote: »

Also be very conscious that the information being stored is very personal information so the route you go for storing the information would need to be secure, if at all possible I would try use an existing PC of theirs that is on-site or try persuade them to get a server for this specific project that will reside on-site and use a more modern database.

Yeah, CheesePlease, what security requirements do you have to fulfill considering this is personal medical information?

Ziycon

@CheesePlease: Also have a look at http://www.dataprotection.ie/docs/A-Guide-for-Data-Contollers/696.htm, this will give you an idea of things you need to keep in mind for storage of personal data.

Tom Dunne

CheesePlease wrote: »

firstly excuse me if my questions seem quite obvious, I'm quite a novice in this whole area. Where specifically could you see the costs occuring? Would it be a good idea to persuade managment to purchase a server to run the database off? if so would access be a viable option? I'm beginning to think this task may be beyond me due to lack of experience. I've just finished a ordinary degree in computers but our course only brushed upon databases in first year. thanks for the replies

What people here are talking about is the classic three-tier architecture. Please tell me you studied that in college.

Anyway, given the number of people involved (20), MS Access would just fall over.

What is being suggested is a database (MySQL is free, also consider SQL Server Express or Oracle XE, also free to the best of my knowledge). To connect to this database, there is going to be some kind of web server. Again, MS IIS is one option, Apache is another. Your 20 client computers will connect to this database/website using nothing more than a web browser - much easier to maintain that a load of MS Access clients.

Now, that's he easy part - the infrastructure.

The hard part is the programming of the above to meet the business requirements. As in, designing the software application that the clients will access via the web browser, along with the database to securely store the data, back it up, and so on. A free option would be PHP, but there are many options available. The software code would sit on the middle tier, this is essentially the application. It would service requests from the clients and store data in the database.

Sorry you asked now?

CheesePlease

Ziycon wrote: »

@CheesePlease: Also have a look at http://www.dataprotection.ie/docs/A-Guide-for-Data-Contollers/696.htm, this will give you an idea of things you need to keep in mind for storage of personal data.

They don't have an IT section within the hospital, only one which covers the larger area. I have been approached by managment as they know I have done the college course. I've been told that they may be willing to purchase a server.
So you think access is not very scalable. Wouldn't it be secure once the database is kept within the network in the hospital? Using a cloud based service wouldn't be an option I'm told.
I would like to try and implement this myself as it might lead to other projects if I can manage it.

So mysql would be my best bet? what sort of set up would this entail? Again thanks for the replies.

ChRoMe

CheesePlease wrote: »

well I do work in the hospital in question and have been studying part time for 3 years and now just finished the degree. I work in the lower rungs of support services (not related to IT) and have been doing the college course to get ahead (out of my current role). They don't have an IT section within the hospital, only one which covers the larger area. I get the feeling they are seldom here and this would never get done if it was handed to them. I have been approached by managment as they know I have done the college course. I've been told that they may be willing to purchase a server.
So you think access is not very scalable. Wouldn't it be secure once the database is kept within the network in the hospital? Using a cloud based service wouldn't be an option I'm told.
I would like to try and implement this myself as it might lead to other projects if I can manage it.

So mysql would be my best bet? what sort of set up would this entail? Again thanks for the replies.

Yup, mysql is your best bet considering that situation.

What programming languages did you use at university.

Scotty #

Please tell me this is a hypothetical scenario for a college project or something??

We don't actually have a real hospital in this country using a porter who's done a night course in 'IT' to design and build the patients records system. Surely not!!

ChRoMe

Scotty # wrote: »

Please tell me this is a hypothetical scenario for a college project or something??

We don't actually have a real hospital in this country using a porter who's done a night course in 'IT' to design and build the patients records system. Surely not!!

Doesn't surprise me in the slightest, but to be honest fair play to CheesePlease if they pull it off it would be great for their entrance into IT.

Good luck CheesePlease!

CheesePlease

Scotty # wrote: »

Please tell me this is a hypothetical scenario for a college project or something??

We don't actually have a real hospital in this country using a porter who's done a night course in 'IT' to design and build the patients records system. Surely not!!

Thats quite dismissive Scotty. Guess your the glass half empty kind of guy. Good luck with that.

Scotty #

CheesePlease wrote: »

Thats quite dismissive Scotty. Guess your the glass half full kind of guy. Good luck with that.

Cheeseplease by your own admission you haven't a clue what you are doing. What happens when your database messes up and returns the wrong records for a query? Who will be held accountable? It would be absolutely scandalous if you are left responsible for a hospitals patient records system. That's not a personal dig at you but you are clearly not qualified.

amen

OP is this for a college project or for a real job ?

If for a real job are you currently working in the IT dept of the hospital or working on some other section of the hospital and this is a way for you to get into the IT Dept?

While in theory this is simple n-tier application you really have a lot to consider from security, data protection, reliability (available most likely 365 days 24*7, clustered app servers, clustered db servers), backups(how to do them, restore time), data integrity, history of the data, user security i.e. who access which record when and what was changed/viewed, data concurrency (what happens when the same record is opened by two or more people etc), physical server access/security, os/database patches etc

Don't forget you will also need to maintain the code, allow for enhancments, performance tune the db etc.

Where are you going to use as a source code repository ?

Yup, mysql is your best bet considering that situation.

Why ? MS SQL Express might just as good in this scenario although it would depend on the size of the database.

fergalr

CheesePlease wrote: »

give grinds to help get me started?

CheesePlease wrote: »

filled out by a patient admitted to hospital. one for admittance and one for discharge.

CheesePlease wrote: »

I'm quite a novice in this whole area.I'm beginning to think this task may be beyond me due to lack of experience. I've just finished a ordinary degree in computers but our course only brushed upon databases in first year.

OP, you haven't told us exactly what the system is to do.
Is it supposed to manage medical data, in the hospital?

If you think building a hospital medical records system might be a bit outside your skills, then you are probably right.

That kind of project should keep an experienced developer awake at night, wondering if they are certain everything is ok, wondering if they have managed the risks properly.

CheesePlease wrote: »

They don't have an IT section within the hospital, only one which covers the larger area. I have been approached by managment as they know I have done the college course.

The management sound like they don't know what they are doing.

CheesePlease wrote: »

Thats quite dismissive Scotty. Guess your the glass half empty kind of guy. Good luck with that.

Honestly, Scotty is right.

You should not apply 'glass half full' kind of thinking to medical records databases.


What are the consequences if this database loses, or worse, corrupts data? Will it affect people's medical care?

Will you face a lawsuit? Allegations of negligence if its your software that goes wrong? Those aren't abstract concerns if you are talking about medical data.

Graham wrote: »

I definitely wouldn't be recommending Access as a back end database. Although Access can theoretically support multiple users/sessions, it can get slow very quickly and if many are updating simultaneously its not unknown for the database to become corrupt.

This is also my understanding of Access - its not geared for this sort of work.



OP: It sounds like you should probably say that the job is beyond you.
Failing that, please do a thorough risk assessment, of what the consequences are if stuff goes wrong.

Constructive suggestions:
Could you do some sort of a partnership with an experienced developer? Possibly you'll be able to find an experienced developer to make the contract work; you having a contract is quite a good bit to bring to the table.

Can you at least get an experienced developer, or team, to review and sign off on your work?



Honestly, when Sparks here talks about how other fields of engineering are regulated, and how software needs to go that way too... well, this sort of scenario makes that case pretty strongly.

TonyStark

Yeah everyone is right on this one. Seems to be a consensus forming on the direction of this project.

You need to take a few steps back with this project. What I've found is people generally have the view that access is this magic out of the box database solution. It's generally viewed by plebs as 'easy'... Whatever that means. Yet from my own experience the use I've seen would be the same as using an excel spreadsheet...only with the power of reporting and some (generally) badly designed forms. Meh.

You need to take this task back to a planning stage. Define your functional requirements for the system. Have a look at all the technological solutions there. Propose an industry best practice approach.

Having recently hacked together an ASP.NET application to replace such a access database it's a matter of becoming very firm and defined with requirements. As an example some functionality I've written into it had to be removed and then added back in. Hence the joys of using source control, build server and automatic deployments :-) my greatest learning that those on the business side of the house don't know their are from their elbow, but you need to be prepared for that.

For example in six months time there is a hardware failure on the box holding the server. Database corrupts there is no backup. It wasn't in the requirements for the project because there was no time left to put it in place. Do you think your manager will admit to putting you under pressure and take the fall or say you enthusiastically took on the project and they thought you knew what you were doing because you have the IT qualification?

Glass half empty is tough love on this occasion. Sorry.

CheesePlease

Thanks for all your input, very much appreciated.

ChRoMe

amen wrote: »

Why ? MS SQL Express might just as good in this scenario although it would depend on the size of the database.

You just answered your own question, 1gig size limit (or whatever it is)

Procasinator

ChRoMe wrote: »

You just answered your own question, 1gig size limit (or whatever it is)

10 GB per database, a single CPU and 1GB of RAM.

ChRoMe

Procasinator wrote: »

10 GB per database, a single CPU and 1GB of RAM.

Fair enough, last time I looked it was 1GB, they must be feeling the pressure from open source.

Procasinator

ChRoMe wrote: »

Fair enough, last time I looked it was 1GB, they must be feeling the pressure from open source.

Yeah, it has been increased since older versions. I don't think it was ever as low as 1 GB in DB size, but it was only 4 GB until the 2008 R2 version.

You could also have been thinking about the 1 GB of RAM limitation which seems to have remained constant in all versions.

croo

SQL Server (and oracle) are still resource hogs though...

Graham

Have the local management that are looking to procure this solution spoken to the central ICT unit/Dept? You may find your final solution is homeless if they won't allow it to be installed.

I would imagine they already have the infrastructure in place for systems like this and as they will (most likely) be left looking after your solution, it may be worth talking to them.

fergalr

Graham wrote: »

Have the local management that are looking to procure this solution spoken to the central ICT unit/Dept? You may find your final solution is homeless if they won't allow it to be installed.

I would imagine they already have the infrastructure in place for systems like this and as they will (most likely) be left looking after your solution, it may be worth talking to them.

This is a really good point. You might find that there are some very risk adverse IT staff - even more so than the posters here - who have to do a final sign off on any software before it goes live.

You might find that you are invited to build something by the management, but then you can't get it signed off on by the IT people, and don't the the project completed, and don't get paid.

bpmurray

This is sheer lunacy. It is almost certainly illegal under the data protection act - you can't just put together a system containing that kind of data without very strict controls, including limiting physical access to the server, *PROVEN* secure access to the system, etc. This is a system that will need to be built to standards similar to HIPAA, and that doesn't come cheap.