CODEWITHSUNDEEP
sqldatabasems-accessrdbms
mjsey
mjsey

Reputation: 1978

Access Appointment System

I'm trying to produce a Dr-patient appointment system. So far I have four tables:-

 Patient:-         Doctor:-           Appointment:-             Availability:-
 patientID         doctorName         time                      time
 name              room               date                      doctorName
 address                              patientID
                                      doctorName

All relationships are 1 to manys, with the many side coming out of the appointment table.

Patient:- The table for patient details

Doctor:- The table for doctor details

Appointment:- the table for appointments

Availability:- The table which stores timeslots each doctor is available

However this is all relatively new stuff to me and I'm getting quite thrown. Firstly in the doctors table should I have a field for DoctorID and use that as a primary key instead of the doctors name? Even though there's only likely to ever be a handful of records in that table.

Secondly if I was to change to DoctorID in all tables instead of the doctorName would I still easily able to access the doctorsName if and when required? This part is what confuses me, maybe I'm just over thinking these things.

The reason I ask is for example say I was to produce a report of an appointment which showed the doctorID, I should be able to get the doctors name for the report based on the relationship right? Likewise for the patient based on patient ID.

I'm guessing I also have enough information in the tables to check against and prevent appointment clashes.

Many thanks,

Upvotes: 2

Views: 1329

Answers (1)

Arion
Arion

Reputation: 31239

You really should use a doctorsid instead of a doctorsName. Because if you are planing to join these tables together then it is much better to join on ints then on varchars.

And another question if you are still going with the varchar solution what will happened if the doctor change its name (marriage or so). The you need to update the realted tables and the doctor table. If you then are going with the int you just have to change it in one place (the doctor table)

Yes and when you are going to produce you appointment report. You need to get the doctors name by joining the tables.

So your table structure should look something like this:

 Patient:-         Doctor:-           Appointment:-             Availability:-
 patientID         DoctorId           AppointmentTime           AvailabilityTime
 PatientName       room               AppointmentDate           DoctorId
 address           doctorName         patientID
                                      DoctorId

Upvotes: 3

Related Questions