Tracking Residents Dr. Appointments

[LOST]

We have residents that have multiple doctors and Doctors that have multiple
residents as patients. So I am guessing that a third table needs to be
introduced to join these tables. True?

Also, we want to track all the dates of all the appointments that each
resident has.

This is what we have for tables:

Residents Table:
MedicareNumber(PK), FirstName, LastName, Building, RoomNumber, DOB,
Date(date of appointments: past, current and ongoing), Time (of
appointments), comments, MDID

Doctors Table:
MDID(PK), FirstName, LastName, Address, City, State, Zip, Phone, Fax,
Specialty

Third table contains?
MedicareNumber, MDID (do they both become the PK for this table?)

My other question is how to deal with all the multiple dates and times? Do
I create a separate table for that?

Thank you, any help would be appreciated.

 

[Steve McLeod]

You are describing a many to many relationship between doctors and patients.
I assume that resident and patient are the same people. So, yes, you need a
third (Associative) table to define the many to many relationship. This
table should contain the primary keys of both tables being associated and the
combined primary keys from the tables being related become the primary key of
the associative table.

You need a fourth table for appointments as this will have the association
defined in the third table and can repeat that association for each
appointment. So this table will have all the keys from the associative table
plus a date and time for the appointment which can serve to provide an unique
key along with that of the doctor and the patient.

 

[Larry Daugherty]

I suggest that all you need is three tables. Caregiver, Patient and
the junction/"associative" table. Which is really the Appointment
table. Put the date and time of the appointment and all other details
about the appointment into that third table and it's all done. The
two Foreign Key fields determine the dr. and patient.

I usually cheat and show the appointments in a subform based on the
Patient.

HTH

 

[LOST]

Okay, just to dummy it up for me...I would have my original 2 tables (minus
the date and time) and then I would have a third table with just the two
primary keys of the Resident and Doctors tables and then a fourth table with
the primary keys of the Resident and Doctors table plus a data and time
field. Do I combine the two primary keys from the original tables and make
them the primary keys for the third and fourth table? Does that make since.

Third table:
MedicareNumber (PK), MDID(PK)

Fourth Table:
MedicareNumber (PK), MDID (PK), Date, Time

 

[Steve McLeod]

This will be ok if there is one and only one appointment between any one
doctor and any one patient and you don't want a history of appointments or
you don't care how many relations there are between one doctor and one
patient.

 

[Steve McLeod]

Yes, that is what I would do, but see Larry Daugherty's comment and my reply.

The fourth table is to keep track of multiple appointments including a
history of past appointments. The third table (relating doctor to patient)
and fourth table (listing appointments for doctors and patients) have a one
to many relationship.

 

[Larry Daugherty]

Au contraire!

Add a record for *every* appointment into tblAppointment. So long as
the date and the time of the appointment is in each record you have
sufficient information to generate a report of all of the patient's
appoints in the database sorted by (maybe also grouped by) date and
further ordered by time, that shows the dr. name and the purpose of
the appointment. Also, you could show every appointment that this
patient has with a given dr. grouped on dr and ordered by date and
time - you could have more than one appointment with a given dr. on
the same day.

Any troubles, please post back.

HTH

 

[Larry Daugherty]

Just responded to another part of the thread and only then saw this
one.

FWIW I always use Autonumber Primary Keys. If you are using natural
keys, assumptions may differ...

The first thing to get clear when designing a database application is
the entities in play within the application and the relationships
between. Altogether that is called the "schema". The entities are
the *noun* kinds of things; people, places, concepts, etc.

Entities become your tables. You need exactly as many tables as you
have entities. Sometimes things you never considered at the outset
become entities. I digress....

IMHO, the first three tables are and ought to be tblCaregiver,
tblResident and tblAppointment.

I don't understand what

Third table:
MedicareNumber (PK), MDID(PK)

Fourth Table:
MedicareNumber (PK), MDID (PK), Date, Time

are or why they need to exist.

If they only exist to provide the Primary Keys for the other main
tables then they are unnecessary. Just enter the proper data into the
records in the main tables. The exception to that might be if you
have imported the data in those tables from other sources. Then it
gets into questions of whose data do you trust and when??

If the Medicare ID numbers are absolutely, positively guaranteed to be
unique across the world and for all time then they will serve for
tblResident Primary Key. Otherwise, I recommend using an Autonumber
for the PK and enter the other number as data. (Never use an
autonumber datatype for any other purpose)!

The same kind of advice and caution for the MDID.

As Steve has written, you have a many-to-many relationship. In those
relationships, a junction table is required. Records in hat junction
table hold all of the information about this intersection, In this
case, Appointment: The Foreign Keys which are the Primary Keys of the
"parent" tables, the date of the appointment, the time, Purpose?, appt
kept (yes/no or time), Notes about this appointment, anything else you
find necessary about this appointment. The junction table is always
the "busiest" of the tables in the relationship. For example, if you
have 50 caregivers and 100 residents then those tables will be fairly
small. Mow multiply the number of residents at an average at 100
appointments and you'll have 10,000 Appointment records.

If you still have issues then post back. Please include a more
complete of description of what you want your application to do (if it
goes beyond simply tracking appointments) and include a detailed
layout of your schema as you now have it want it to be.

HTH