Medical Records Database

[whizzkid1892]

I have to design a database for a doctors surgery for my college project. Here is the brief of the project:

A local doctors’ surgery, Cedarwood Medical, would like to convert their paper-based medical/prescription records onto a database system, so that it is quicker and easier to find all patients who have been prescribed a particular drug at any one time.

Cedarwood Medical would like to hold their patient details, all drugs prescribed to a particular patient, visit details which should include which doctor they have seen and what drug(s) were prescribed on that visit. The surgery would like to record all medical conditions a patient has been diagnosed with, current and past.

The database is not to be used for booking appointments, as Cedarwood Medical already has a booking system in place, which they are happy with.

Each drug given to a particular patient will have specific instructions, which are based on the patients’ condition(s), what other drugs they are taking etc.

Cedarwood Medical would like to be able to extract information from the new system, including (but NOT limited to):
• All patients in a specific age group
• All patients prescribed a specific drug, in a specific date range
• All patients who have visited a specific doctor
• All drugs allocated on a specific date, and in a specific month
• All patients with a specific medical condition.

I have attempted to normalise so that I can produce tables in access and the relationships between them.

UNF

PATIENT ID
PATIENT TITLE
PATIENT FORENAME
PATIENT SURNAME
PATIENT SEX
PATIENT DOB
PATIENT ADDRESS
PATIENT TEL NO
PATIENT NOTES
DOCTOR ID
DOCTOR FORENAME
DOCTOR SURNAME
DOCTOR SEX
DOCTOR NOTES
PRESCRIPTION ID
PRESCRIPTION DATE
DRUG ID
DRUG NAME
DRUG SIDE EFFECTS
DRUG DANGERS
MEDICAL HISTORY ID
MEDICAL CONDITION ID
ILLNESS
SYMPTOMS
TREATMENT
VISIT ID
VISIT DATE
VISIT TIME

1NF

PATIENT ID
PATIENT TITLE
PATIENT FORENAME
PATIENT SURNAME
PATIENT SEX
PATIENT DOB
PATIENT ADDRESS
PATIENT TEL NO
PATIENT NOTES

DOCTOR ID
DOCTOR FORENAME
DOCTOR SURNAME
DOCTOR SEX
DOCTOR NOTES
PRESCRIPTION ID
PRESCRIPTION DATE
DRUG ID
DRUG NAME
DRUG SIDE EFFECTS
DRUG DANGERS
MEDICAL HISTORY ID
MEDICAL CONDITION ID
ILLNESS
SYMPTOMS
TREATMENT
VISIT ID
VISIT DATE
VISIT TIME

2NF

PATIENT ID
PATIENT TITLE
PATIENT FORENAME
PATIENT SURNAME
PATIENT SEX
PATIENT DOB
PATIENT ADDRESS
PATIENT TEL NO
PATIENT NOTES

DOCTOR ID
DOCTOR FORENAME
DOCTOR SURNAME
DOCTOR SEX
DOCTOR NOTES

PRESCRIPTION ID
PRESCRIPTION DATE
DRUG ID
DRUG NAME
DRUG SIDE EFFECTS
DRUG DANGERS

MEDICAL HISTORY ID
MEDICAL CONDITION ID
ILLNESS
SYMPTOMS
TREATMENT
VISIT ID
VISIT DATE
VISIT TIME

3NF

PATIENT ID
PATIENT TITLE
PATIENT FORENAME
PATIENT SURNAME
PATIENT SEX
PATIENT DOB
PATIENT ADDRESS
PATIENT TEL NO
PATIENT NOTES

DOCTOR ID
DOCTOR FORENAME
DOCTOR SURNAME
DOCTOR SEX
DOCTOR NOTES

PRESCRIPTION ID
PRESCRIPTION DATE

DRUG ID
DRUG NAME
DRUG SIDE EFFECTS
DRUG DANGERS

MEDICAL HISTORY ID

VISIT ID
VISIT DATE
VISIT TIME

PRESCRIPTION ID
DRUG ID

MEDICAL HISTORY ID
MEDICAL CONDITION ID

DOCTOR ID
VISIT ID

MEDICAL CONDITION ID
ILLNESS
SYMPTOMS
TREATMENT

Could someone tell me if I have done this right because when I tried to put this into access the relationships sometimes come up as interderminate (which I dont know what that means). Any help would be greatly appreciated.

[Scott Price]

I'm not sure what 'interderminate' means either, actually :-)

Perhaps you mean 'indeterminate'?

Something doesn't look quite right with what you have listed below the 3rd Normal Form section of your table structure. You have several tables listed as only have one field: the foreign key of another table... That's likely why you are getting an indeterminate relationship warning from Access.

Remember when designing your table structure you need to be able to separate between subjects and characteristics. An example of subjects in your project would be Doctors, Patients, Prescriptions. An example of characteristics would be Title, FrontName, BackName, Street Address, City, ZIP, DOB, Instructions etc.

Remember also that Subjects become tables, characteristics become fields in the tables.

Break each Subject down according to its relationship with the other Subjects... One Doctor can have multiple Patients, but can One Patient have multiple Doctors? If only the former is true you have a one to many relationship between Doctors and Patients, however I think you will have to admit that really the latter is true! This means you have a Many to Many relationship between Doctors and Patients, which will require a simple linking table which breaks this down into two One to Many relationships.

Next clarify in your mind to whom the Prescription is related, and the nature of its relationship: to Doctors or to Patients? One to Many, One to One, Many to Many?

If you think carefully about this, you'll see that while Doctors can write Prescriptions, the real relationship is to the Patient who is receiving it! After all the requirement is not to find which prescriptions the Doctor writes, but which Prescriptions the Patient has... Thus you'll be changing the prescription to be related to the Patient, not the Doctor. What is its relationship? One Patient will have multiple prescriptions? One Prescription will have multiple patients?

An extremely good book that I would highly recommend you buy off Amazon.com is Database Design for Mere Mortals 2nd Edition by Mike Hernandez. You'll get far more from working through his book than the few dollar investment you make to buy it.

Any other questions, don't hesitate to ask.

Regards,
Scott

[Stewart Ross]

Hi. Scott has given a very good, detailed reply and you have plenty to do, I'm sure! I notice that you have tended not to carry forward the primary key from an entity when you are going to 2NF.

As Scott identified, Prescription, for instance, has just an ID and does not refer to the patient receiving the prescription. Similarly your visit link table has a doctor ID but no patient ID. Doctor visits Patient, not just Doctor makes a visit.

To assist you avoid this vanishing key issue, when you are looking at relationships between entities such as patient, doctor, prescription and so on ask yourself what that relationship represents. For example, Patient is treated by Doctor, Patient is prescribed Prescription, and so on.

Explicitly defining the kind of relationship will help you recognise whether a relationship is one to one, one to many, or many to many. A Patient can have many Doctor visits, and a Doctor can visit many Patients - so this is a many to many relationship which has to be resolved by use of a linking entity - Doctor Patient Visit would be one name for it. Thinking of these as bi-directional relationships makes it less likely you will overlook carrying forward the primary keys of the many-to-many tables as foreign keys into the linking tables.

Good luck with your project

Regards

Stewart

[NeoPa]

Indeterminate basically means is not determinable, can not be determined, ambiguous.

For help on general principles of normalisation, please read Normalisation and Table structures and/or How to structure your tables.