Feedback on Complaint Database

[Sola]

Hey, all. I am looking for some feedback before I start building. I am designing a database to log complaints against doctors and perform followup. The tables and abbreviated fields are:

tblComplaint: ComplaintID, ComplaintType, ComplaintDetails, ComplaintResolved
tblDoctor: DoctorID, DoctorName, DoctorContactInfo
tblPatient: PatientID, PatientName, PatientContactInfo
tblFollowUp: FollowUpID, FollowUpType, FollowUpDetails

I see two options for the db structure.

The first option is to put DoctorID, PatientID and FollowUpID in tblComplaint so that the one-to-many relationships have the "one" on the side of the IDs in the Doctor, Patient and FollowUp tables.

The second option is to put ComplaintID in the Doctor, Patient and FollowUp tables so that the one-to-many relationships have the "one" on the ComplaintID in tblComplaint.

My natural inclination is to use the first option, because it pulls all the relevant info together for each individual complaint. So, I guess my question is, is there any reason I should be considering option two instead?

 

[Bob Miller]

Hey, all. I am looking for some feedback before I start building. I am designing a database to log complaints against doctors and perform followup. The tables and abbreviated fields are:

tblComplaint: ComplaintID, ComplaintType, ComplaintDetails, ComplaintResolved
tblDoctor: DoctorID, DoctorName, DoctorContactInfo
tblPatient: PatientID, PatientName, PatientContactInfo
tblFollowUp: FollowUpID, FollowUpType, FollowUpDetails

I see two options for the db structure.

The first option is to put DoctorID, PatientID and FollowUpID in tblComplaint so that the one-to-many relationships have the "one" on the side of the IDs in the Doctor, Patient and FollowUp tables.

The second option is to put ComplaintID in the Doctor, Patient and FollowUp tables so that the one-to-many relationships have the "one" on the ComplaintID in tblComplaint.

My natural inclination is to use the first option, because it pulls all the relevant info together for each individual complaint. So, I guess my question is, is there any reason I should be considering option two instead?

I would put DoctorID and PatientID in the tblCompaint and ComplaintID in the tblFollowup.
Logic:
When you get a complaint you will want to know who the patient is that is complaining and against what doc.

Later, when a followup occurs you will want to ID which complaint is being followed up.

With this setup you can lookup all the complaints against one doc, all the complaints by a patient, all the follow ups on any complaint, etc.

Your first option doesn't take into consideration that there may be more than one follow up to a complaint.
Your second option allows for only one complaint per doc and one complaint per patient.

 

[Sola]

Hi, Bob, and thanks for the feedback!

You were spot on - there's definitely going to be more than one followup per complaint. I don't know why I was thinking all the relationships could only be one-way.

I have started the db build, and it's working great. Thanks again for your help!