tables help/advise

[PRodgers4284]

I have been asked to design a database at work, i was handed a sheet of paper that holds info about a single person and it is split into 3 sections, person history, person management, person organisation, inside these sections is made of a series of questions with a yes/no option and a reason field etc, there is only ever one record for a person. What im wondering if can i just use one table to store the info, or would i need to use another table etc and create a relationship? Sorry about the question but i feel im trying to over complicate things, any help would be much appreciated.

 

[David Eagar]

I would suggest you would need three tables. 1 employee may have more 'history' entries than another and I assume may move around the 'organisation' more or less than another.

 

[PRodgers4284]

I would suggest you would need three tables. 1 employee may have more 'history' entries than another and I assume may move around the 'organisation' more or less than another.

Hey thanks for the reply, the table stores info for a patient within a hospital, its basically an electronic monitoring form if the patient gets sick during their stay, its for docs and nurses to complete on the wards. Their are no personal details as such, just a patient id no with the various question fields within the table at present.

 

[boblarson]

Just remember - you should design it such that if it is at all possible for someone to, for example, move to another room (or two) (and I know that has happened to my wife before in the hospital), you should be able to account for it. Same with other stuff.

 

[David Eagar]

I have found that databases often end up with more features than originally envisioned. By having seperate tables, you have much greater flexibility to add new fields as you think of them, without storing lots of empty spaces as would happen with one table

 

[PRodgers4284]

Its kind of hard to explain, the table stores a patient id number for when they stay and then each question is completed based on issues surrounding that particular visit to the hospital. If the patient has to come back and stay for example in a years time a form must be completed again using a different patient id as the issues surrounding this visit will be different. The database will basically be used to pull stats from for clinical audits and create various queries about each question etc, it is used to find out what are the main causes of contracting illness/sickness from the hospital. Hope that makes sense.

 

[boblarson]

It does make sense and only you will know your full business requirements. We, on the other hand, do not know but are asking questions and throwing things out there to hopefully jog your memory, if necessary, to aid in your figuring out the correct design which will work for you. It may be that all of your stuff should be in one table, but based on past experience, we are just saying - be ware and think well before moving down that path.

 

[David Eagar]

The use of multiple id's for the same patient is by normalisation rules, a no-no. If 1 patient id is linked to multiple visits it a) makes searches easier and b) is more efficient in data storage.

I guess it's up to you how you want to structure it.

 

[DCrake]

From someone with NHS clinical audit experience, you would need 1 table to hold the patient ID I would use the NHS no as the PK as this never changes.

Then you need a wards table to hold the details of each ward

Then you need an incident table that hold all the details contained on the form. I assume the form is the same irrespective of which ward the patient is on.
This would hold the PatientNHS No, the Ward ID and the Incident Number as a three part primary key.

One patient can have more than one incident on one ward, likewise they can have more than one inpatient stay. You need to take into account that the patient could be moved from a general ward to ICU then back to a different general ward during one inpatient visit. That being so there is scope for a minimum of 3 incidents.

David

 

[PRodgers4284]

From someone with NHS clinical audit experience, you would need 1 table to hold the patient ID I would use the NHS no as the PK as this never changes.

Then you need a wards table to hold the details of each ward

Then you need an incident table that hold all the details contained on the form. I assume the form is the same irrespective of which ward the patient is on.
This would hold the PatientNHS No, the Ward ID and the Incident Number as a three part primary key.

One patient can have more than one incident on one ward, likewise they can have more than one inpatient stay. You need to take into account that the patient could be moved from a general ward to ICU then back to a different general ward during one inpatient visit. That being so there is scope for a minimum of 3 incidents.

David

Hey david, thanks for the reply, i have the following fields in my table:

EpisodeID (records an autonumber for each of the patient visits - unique identifier)
PatientRef
Trustname
Date
Reason

Then i have a fields for each of the sections which are patient history, patient management, patient environment, theres roughly about 15 - 20 questions in each section, I have each question in the table field as:

PHQuestion1, 2 etc (Patient History)
PMQuestion1, 2 etc (Patient Management)
PEquestion1, 2 etc (Patient Environment)