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.

ChRoMe

bpmurray wrote: »

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.

I find it amusing the complete rage on this thread, after near 15 years in IT. This story doesn't even scrape the surface.

stevenmu

Like others, I'm not sure this is a great idea, but it looks like it'll go ahead one way or the other so I'll offer some advice.


I agree with everyone that Access isn't really up to this task, even Microsoft pretty much openly admit that these days. A web application running with a proper database is clearly the better option.

I also think that you need to have a really good think about maintenance and support on the system. Think about who will be responsible for maintaining and supporting the system on an ongoing basis. Who will be performing backups, who will be called if the system goes down etc. You've mentioned that the hospital do have some level of IT support, you will need to have a good talk with them about what you intend to do, and see what they can or will take on.

For example, if you want to go with PHP running on Apache and with a MySQL database and they have no knowledge of any of those, they may not be willing to do any level of support, which means you would have to arrange your own backup/restore solution, and any time a service stops or goes down you will have to fix it. Whereas if you go with .Net running on IIS with SQL Server, they may be willing and able to look after backups, rebooting the server, restarting services etc.

It's possible, and quite common in fact, that their IT will insist on a certain platform/technologies being used, so you really need to talk to them sooner rather than later.


You clearly won't have much experience with best practices for developing these kind of applications, so when you do settle on a base platform/technology it'll be very important for you to do quite a bit of research into what best practices are out there.

When you do have a base platform/technology you should also spend a bit of time investigating what frameworks are available to assist you in that. For example if you are going with .Net/IIS/SQL Server, then ASP.Net's MVC framework will assist you quite a lot. It will help you structure your application according to best practices and will help you to add standard bits of functionality quickly and easily, things like authentication and security for example. It won't stop you doing stupid things, but it'll hopefully encourage you in the right direction. (I'm not familiar with the equivalent frameworks for PHP, but I know some of them are well thought of, mature, and will help you a lot too)

You could possibly take this a step further and use something like ASP.Net LightSwitch, which will do nearly all of the hard work of building the application for you.


Another area where your lack of experience will be an issue is when it comes to requirements gathering. This can be a huge minefield even for experienced developers. The best advice I can give in short is to spend a lot of time talking to the users/owners and finding out what their requirements are, and getting a good understanding of what they expect and how they will be using the system to do their jobs. Draw up a document listing their requirements, and your proposed solution design to solve those requirements, and get them to agree to it before you give them any time/cost estimates, and definitely do this before you start any real coding.

You'll also need to take into account that the first time they try to use the system they will come up with a whole new bunch of requirements or change requests, so in your estimates you will need to allow time for them to test the system (including you training them on the system), time to fix any bugs, and time to implement any change requests, and at least one more round of testing.

LukeQuietus

SELECT * FROM hospital_management WHERE iq >= 1;
No rows found...

CheesePlease, if that makes absolutely no sense to you then simply graciously decline this job and let them get a professional database developer / administrator to do it. You seem perfectly nice and all but it could be someone you care about who this farce ends up hurting.
Data is integral in how a system functions. Especially a hospital or anything holding potentially personal and important information. Relational Database Management concepts are extremely complex. It's nothing like making a few spreadsheets. You have things like normalization of the data from 1NF through 3NF and beyond to ensure it's integrity. What engine you need to use (e.g. does it need to be transactional in which case A.C.I.D. proof), Joins, subqueries and then you need to know a programming language which can interpret the data and present it to who needs to see it and on the other end take it from the input / client and send it to the database. Java, PHP, .NET, or Ruby depending on the system.

They're not something you can install and set up like a desktop program. Database driven systems are custom designed and planned from the ground up to meet the job at hand because every job or database can have vastly seperate needs.

keith16

LukeQuietus wrote: »

SELECT * FROM hospital_management WHERE iq >= 1;
No rows found...

I get it!

It's a simple select query. Show me all rows (represented by *, although this is bad practice) from the hospital_management table, where the field iq is greater than or equal to 1.

The query fails to return any rows, cos all the management are idiots and none of them have an IQ equal to or greater than 1.

I have to agree with the query in this case.

croo

keith16 wrote: »

Show me all rows (represented by *...

The * indicates all columns not rows!

ChRoMe

croo wrote: »

The * indicates all columns not rows!

Still A+ for enthusiasm

keith16

croo wrote: »

The * indicates all columns not rows!

How about

SELECT * from the fùcks_given table

WHERE number_of_fùcks >= 0

Hmmm...no rows returned here either

Procasinator

keith16 wrote: »

How about

SELECT * from the fùcks_given table

WHERE number_of_fùcks >= 0

Hmmm...no rows returned here either

If there was no fùcks given, shouldn't there be at least one row that has has number_of_fùcks = 0?

If 0 fùcks are not being stored, you probably didn't need the WHERE clause (unless you can give negative amount of fùcks).

keith16

Procasinator wrote: »

If there was no fùcks given, shouldn't there be at least one row that has has number_of_fùcks = 0?

If 0 fùcks are not being stored, you probably didn't need the WHERE clause (unless you can give negative amount of fùcks).

You would think so but but it is an MS Access db that I queried and it fell over.

realgolfgeek

croo wrote: »

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

Really, how is SQL Server a resource hog ?

Procasinator

realgolfgeek wrote: »

Really, how is SQL Server a resource hog ?

In a lot of ways, databases are designed to be resource hogs by default. They'll tend to grab as much memory as they can so they can cache data in memory and improve performance.

stevenmu

FWIW it's very easy to configure limits for the resources that SQL Server uses. I'd imagine it's the same story for Oracle or any other 'resource hogging' database server.

croo

realgolfgeek wrote: »

Really, how is SQL Server a resource hog ?

The suggestion was the OP could run a web based solution on a spare PC... any PC I've installed SQL Server or Oracle on has taken a big hit in performance where as with something like postgresql or mysql (on linux) you'd hardly know it was there and it would need to be a very heavily used application to have an impact that requires you to start tuning the db.

stevenmu

The normal deployment scenario for the full versions of SQL Server (and I'd guess for Oracle as well) is for it to sit on a dedicated server. Therefore, by default it will take up any available resources.

If required, it takes literally seconds to configure limits to the resources it will use, and it's done through a simple, easy to use GUI. In most cases all you will need to do is enter the maximum amount of RAM you want it to be able to use, if needed you can limit CPU and IO usage just as easily. I definitely wouldn't class this as "tuning the db" which conjures up images of pouring over schema diagrams, execution plans and stored procedures, building indexes and changing field types.

I'm not 100% sure, but IIRC the Express versions of SQL Server, which were recommended to the OP, are designed to cater for desktop scenarios and are more throttled by default.


edit: oh, and one thing I've found is that when running a SQL Server/IIS application on a single box, the two of them tend to reach a pretty happy equilibrium where each gets the resources it needs to perform well. This might not leave a lot of resources for a developer/user to log in and use the server for something else so it might feel sluggish to them, but it means your application runs as well as possible, which is the important thing really.

croo

Each to their own .. I'd prefer linux & postgresql over windows & SQL Server and I believe that solution allows me to get more from the hardware.

As for the dedicated server point; I agree but, I repeat, it was being suggested to the OP (by others) that he might use a "spare PC" to run all the services for a web based application as the solution. I would expect a PC's resources (being used as a server) to be extremely limited.

stevenmu

I do get what you're saying, but SQL Server is fine to run on a spare PC being used as a server.

SQL Server is a resource hog in the sense that it will grab whatever resources it can to run as fast as it can, and if a user is trying to use that PC as normal the PC will naturally feel slow because SQL Server is hogging resources (RAM in particular).

But that doesn't mean that it actually requires lots of resources, it runs perfectly fine on lower resource machines.

ChRoMe

stevenmu wrote: »

I do get what you're saying, but SQL Server is fine to run on a spare PC being used as a server.

SQL Server is a resource hog in the sense that it will grab whatever resources it can to run as fast as it can, and if a user is trying to use that PC as normal the PC will naturally feel slow because SQL Server is hogging resources (RAM in particular).

But that doesn't mean that it actually requires lots of resources, it runs perfectly fine on lower resource machines.

Exactly, not to mention the fact that the use cases that were briefly described do not sound like a huge amount of concurrent users. No need to premature optimization so to speak, I'd throw it on the spare box and see how it performs rather than trying to second guess problems that might not even exist.

realgolfgeek

Big misconception here that SQL is a resource hog.

It's only a resource hog if it's (as I call it), a next next next installation.

Obviously depending on the amount of transactions,
but if you have your memory configured correctly, have things like lock pages in memory turned on, perform volume maintenance tasks turned on, regular update statistics, rebuild indexes, proper TempDB configuration,
proper database design, proper indexing on your tables, proper stored procedure coding etc etc ...
it's actually NOT a resource hog as many people may think.

Again, depends on volumes here but I'm dealing with DB's over 500 TBs on a daily basis with SQL Server and the resources on the systems are not stressed at all.

croo

Well I give ye one point - SQL Server would be better than MS Access

stevenmu

Haha, I think that's something we can all agree on.