Unanswered: Health record for family,, question about tables
I have just started creating a program to keep track of my family's health records ..I want it to show date visit to which doctor and what was the illness and prescription ..
I 'll write what tables I have and I want to know is this correct or am I doing something wrong?
Patient ID (autonumber)
Table Bill(or Appointment)
PatientID (should I write Patient Name??) and should I set it to text or autoNumber
DoctorID (or should it be Name?)
First question, how should I make relation ship between
table BILL and table Patient is it one to many? I so confused because each bill should have only one patient and one doctor but in general each patient can go to many doctors and viseversa. I have same question for all tables,
Second question, when I link tables, should the table for example Patients have the field Doctor ID or Doctor Name? if it is Doctor ID should I set to text and then it will retrieve it from the original table or to number or what??
In addition, is it okay to add table Major?
I created it ,because I want to have a form that allows me to go to each major, for example, when I my child has ear infection , I want to open a form called ENT and there, I can see records of last visits to ENT + enter data for the current visit. Is this table necessary in order to achieve this point ?or should I delete this table as there will some way to create a form doing what I want using this field (like a query?)
I hope someone can advice me since it has been very very long time since I studied Entity Relation Ship Diagram and I am so confused now ...
The two tables would be related one-to-many on PatientID. If PatientID is an autonumber, it would be Long Integer in the bills table (and you would not store the name). Same for DoctorID and the doctors table.
I'm not clear on what the Major table is for.
Other thoughts; both sickness and prescription should be related tables, as presumably the possibility exists that there could be more than one of each. In other words, in one appointment I might get diagnosed with both an ear infection and the flu. I might get prescribed an antibiotic and something else.
Thanks a lot for the help
regarding sickness and prescription, it is okay to make it memo instead of creating tables?
regarding major, when I open the main form, i want to see different buttons, one for ENT form (it will show all records of ENT visits) and another one for Dentist (it will show all records of Dentist) etc...Can i do it with a form based on query without using this major table??
Is it okay? Sure; it just wouldn't be a normalized structure.
As to opening a form restricted to certain types of entries without that table, sure, as long as the main table contains the appropriate data (like ENT). You could use this technique behind the buttons: