Relationships and Primary Keys

[Nona]

I want to set up a database that will track medical appointments -- 3 Tables
- Patient Names, Health Care Professional Names, and Appointment
Dates/Times.

I would like for the data entry form to have 2 list boxes - one for
selecting the patient's name and the other for selecting the name of the
health care professional. Other fields in the Data Entry form would be for
input of the date and times of the apt.

I am having problems with knowing how to set up the primary keys and the
relationships between the tables. Here's my thought process:

The Patient Names table will have a unique ID with a one to many
relationship to the same ID in the appointment table.

The name of the health professional in the Health Care Pro table would have
a one to many relationship with the name of the health prof entered in the
apts table.

The primary key in the Patient Names table should be the Unique ID. But what
should be the primary key in the other two tables?

Is this the best way to set up this database?

Thanks for your help!

 

[Arvin Meyer [MVP]]

Have a look at this sample database:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=141

 

[Nona]

Thanks for your reply. Unfortunately I can't find the Active X file that is
required (mscomct2.ocx). I am using Access 2000. The most similar name I
found was one called "mscomctL.ocx. Could that possibly work?

 

[John W. Vinson]

I want to set up a database that will track medical appointments -- 3 Tables
- Patient Names, Health Care Professional Names, and Appointment
Dates/Times.

I would like for the data entry form to have 2 list boxes - one for
selecting the patient's name and the other for selecting the name of the
health care professional. Other fields in the Data Entry form would be for
input of the date and times of the apt.

I am having problems with knowing how to set up the primary keys and the
relationships between the tables. Here's my thought process:

The Patient Names table will have a unique ID with a one to many
relationship to the same ID in the appointment table.

The name of the health professional in the Health Care Pro table would have
a one to many relationship with the name of the health prof entered in the
apts table.

You should certainly NOT use the name of the health care prof as a linking
field, or as a primary key. Names are not unique! My name is John W. Vinson; I
was once at a university where there was a professor named John W. Vinson. You
very well could have two profs who happen to have the same name.

Instead, use a "surrogate key" as the primary key of Health Care Professional
Names (btw that's a bad name for a table; I'd avoid blanks and make the name
shorter, e.g. Professionals).

The Appointments table could also have an autonumber PK, or you could use a
three-field composite key consisting of the PatientID, the ProfessionalID, and
the VisitDate. If you use the surrogate autonumber primary key, you could put
a unique Index on these three fields to prevent doublebooking a patient.

 

[Arvin Meyer [MVP]]

Probably not. mscomct2.ocx should be at:

C:\Windows\System32\mscomct2.ocx

If you can't find it on your machine, check others. It was dated in the year
2000, so it may be on older machines. Make sure you register the file.

Since, it essentially is a calendar file, you may want to use this calendar:

http://www.datastrat.com/Download/Calendar2K.zip

instead. Or any another calendar. You should be able to find plenty of them
at http://www.lebans.com or http://www.allenbrowne.com
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Paul Shapiro]

The appointments table PK should probably be just ProfessionalID and
VisitDateTime, unless the business model allows double-booking appointment
times. The patientID isn't needed since the PK should be unique without it.

 

[Michael Gramelspacher]

I want to set up a database that will track medical appointments -- 3 Tables
- Patient Names, Health Care Professional Names, and Appointment
Dates/Times.

I would like for the data entry form to have 2 list boxes - one for
selecting the patient's name and the other for selecting the name of the
health care professional. Other fields in the Data Entry form would be for
input of the date and times of the apt.

I am having problems with knowing how to set up the primary keys and the
relationships between the tables. Here's my thought process:

The Patient Names table will have a unique ID with a one to many
relationship to the same ID in the appointment table.

The name of the health professional in the Health Care Pro table would have
a one to many relationship with the name of the health prof entered in the
apts table.

The primary key in the Patient Names table should be the Unique ID. But what
should be the primary key in the other two tables?

Is this the best way to set up this database?

Thanks for your help!

Here is a schema for a database I did as an example. I cannot vouch that it is correct.

CREATE TABLE Doctors (
doctor_num VARCHAR(10),
doctor_name VARCHAR(50),
CONSTRAINT pk_Doctors PRIMARY KEY (doctor_num)
);
CREATE TABLE DoctorSchedule (
doctor_num VARCHAR(10)
CONSTRAINT fk_DoctorSchedule_Doctors
FOREIGN KEY (doctor_num)
REFERENCES Doctors (doctor_num)
ON UPDATE CASCADE,
calendar_date DATETIME,
appt_time DATETIME,
avail_status SMALLINT DEFAULT 1,
CONSTRAINT pk_DoctorSchedule
PRIMARY KEY (doctor_num, calendar_date, appt_time)
);
CREATE TABLE Patients (
patient_num VARCHAR(10),
patient_name VARCHAR(50),
CONSTRAINT pk_Patients PRIMARY KEY (patient_num)
);
CREATE TABLE PatientAppointments (
patient_num VARCHAR(10)
CONSTRAINT fk_PatientAppointments_Patients
FOREIGN KEY (patient_num)
REFERENCES Patients (patient_num)
ON UPDATE CASCADE,
doctor_num VARCHAR(10),
calendar_date DATETIME,
appt_time DATETIME,
CONSTRAINT fk_PatientAppointments_DoctorSchedule
FOREIGN KEY (doctor_num, calendar_date, appt_time)
REFERENCES DoctorSchedule (doctor_num, calendar_date, appt_time),
CONSTRAINT pk_PatientAppointments
PRIMARY KEY (patient_num, doctor_num, calendar_date, appt_time),
CONSTRAINT unique_time_slot
UNIQUE (doctor_num, calendar_date, appt_time)
);

What it comes down to is matching up a patient with a time slot in a doctor's schedule.

Creating the DoctorSchedule table is quite easy. It requires a Calendar table with every calendar
date for perhaps the next five years. It requires a table with appointment times. Example: 08:00
AM, 08:15 AM, etc. Now you can combine Doctors, Calendar and AppointmentTimes and fill the
DoctorSchedules table by running one Insert query.

I do not know if this example helps or not. You would need access to a doctors schedule in order to
know where a doctor is available for a particular date and time.

 

[Nona]

Thanks to all of your suggestions. I'll have to spend some time working
through these! I appreciate very much your time and expertise. Will post the
results later!