Doctor visits Patient, not just Doctor makes a visit. Similarly your visit link table has a doctor ID but no patient ID. 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.Īs Scott identified, Prescription, for instance, has just an ID and does not refer to the patient receiving the prescription. You'll get far more from working through his book than the few dollar investment you make to buy it.Īny other questions, don't hesitate to ask. What is its relationship? One Patient will have multiple prescriptions? One Prescription will have multiple patients?Īn extremely good book that I would highly recommend you buy off is Database Design for Mere Mortals 2nd Edition by Mike Hernandez. Thus you'll be changing the prescription to be related to the Patient, not the Doctor. 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. 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? 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. Remember also that Subjects become tables, characteristics become fields in the tables.īreak each Subject down according to its relationship with the other Subjects. An example of characteristics would be Title, FrontName, BackName, Street Address, City, ZIP, DOB, Instructions etc. An example of subjects in your project would be Doctors, Patients, Prescriptions. Remember when designing your table structure you need to be able to separate between subjects and characteristics. That's likely why you are getting an indeterminate relationship warning from Access. You have several tables listed as only have one field: the foreign key of another table.
Something doesn't look quite right with what you have listed below the 3rd Normal Form section of your table structure. I'm not sure what 'interderminate' means either, actually :-) I have attempted to normalise so that I can produce tables in access and the relationships between them.Ĭould 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).
Ěll patients who have visited a specific doctor.
Ěll patients prescribed a specific drug, in a specific date range.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.Įach 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.Ĭedarwood Medical would like to be able to extract information from the new system, including (but NOT limited to): The surgery would like to record all medical conditions a patient has been diagnosed with, current and past. Here is the brief of the project:Ī 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.Ĭedarwood 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. I have to design a database for a doctors surgery for my college project.
0 kommentar(er)
