need help in Relationship between tables

Question

Hello all, I am designing the database for "Patient Information System". I have table with names Table Name : Patient(a patient is the person who visit the hospital for treatment/consultation) Id,Name,Address,DateOfBirth,Phone,EmergencyContact,DateOfRegistration

Table Name : Doctor( a doctor will be assigned to each patient.) Id,Name,Address,Phone

Table Name : Bed(if patient type is "In Patient", a bed will be assigned to the user.) Id,BedName,RatePerDay,BedType

Table Name : Visit(a patient can visit the hospital any number of times.) Id,PatientType,DoctorId,BedId,DateOfVisit,DateOfDischarge,Symtomps,Disease,Treatment

Somebody please help me how to design the relationship between these tables i mean which one primary key which one reference key plz....plz......

Thanks, Masum

Answer 1

If you normalise your data properly, to 3rd Normal Form, which I assume is one of the things you should be learning for this task, then you should be able to easily spot the Primary Keys, foreign keys, and relationships.

There is a good tutorial on normalisation here: http://www.phlonx.com/resources/nf3/

Answer 2

Between Doctor and Patient theres N:N, a Patient can have multiple doctors and a doctor checks up many patients. Cause it`s an N:N relationship you have to come up with an junction table with an composed primary key.

For the table "Doctor" you can use an Doc_ID and so for "Patient" a Patient_ID as primary keys.

If you really want to make a "Bed" table, then theres an 1:1 realtionship i think between Patient and Bed :)

Answer 3

From the looks of it, Visit is the center, holding all of the reference (foreign) keys. So, since each visit has a bed, a doctor, and a patient, these are all 1:m relationships:

Visit.PatientType => Patient.Id
Visit.DoctorId    => Doctor.Id
Visit.BedId       => Bed.Id

(Unless PatientType is something else -- then, you don't currently have a foreign key for Patient.Id.)

And, with Visit mediating, all other relationships are m:n (e.g., a patient can be treated by many doctors and a doctor can treat many patients).

Patient <= Visit => Doctor
Patient <= Visit => Bed

etc.

Answer 4

So here's what I have for you (ignoring the extended attributes of each table):

• Patient -> PatientType -- PatientType describes Patient
• Bed -> Patient_Bed <- Patient -- A patient may have many beds in a single visit
• Patient -> Visit -- A patient participates in many visits, but a visit is paritally defined by the patient participating.
• Doctor -> Patient_Doctor -- A multiple doctors may pariticpate in a single visit
• Visit -> Patient_Doctor -- Associates a doctor and a patient with each visit

If I missed any, let me know. I can email you the Visio file if you like.