Appointment system Design

[monkeyhead]

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 detailsAppointment:- 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,

[R_Badger]

Let me just say you're on the right track, and don't be disheartened, you stopepd at the right point to ask the question!

1.

Firstly in the doctors table should I have a field for DoctorID and use that as a primary key instead of the doctors name?

You are better off using DoctorID (old cliche what if you had two John Smiths?)

2.

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?

Yes you can, the idea of storing data relationally is that with the relevant ID you can read all the appropriate data from that (kind of like a record card for filing cabinets)

The problem I think you may encounter is the appointment scheduling.

I would personally add a duration field to the appointment table. You should then (with a bit of careful querying) be able to find out exactly when someone is available.

I would also reserve some patientIDs for things like meetings, holiday, etc

A few other pointers (if you don't mind me going beyond the OP question)

Avoid using reserved words as names for fields i.e name, time, date.

To normalise it as far as possible try to keep fields to as little data as possible e.g. First Name, Last Name

My suggested table structure would be more like:

Patient:-	Doctor:-	Appointment:-	Availability:-
patientID Patient_First_name	DoctorID doctorName	App_ID App_time	Avail_time doctorName
Patient_Mid_name Patient_Last_name address line 1 address line 2 address line 3 postcode	room	App_date patientID doctorName Duration

There are load of people on here with good info and hopefully someone will chip in with something I have missed (I always feel like I have)
---End of wall of text---