Are three primary keys less effecient than two?

[Dale]

I want to create two tables from my source data. The source data contains
patient registrations and encounters based on date and time of registration.
I want to split the data, create a patients table and and an encounters
table. The patient table is straight forward, create a table based on the
unique registration id. For the encounters, the table will be based on the
registration id and date and time. Is it more efficient to convert the date
and time to a numeric number (customx) and link the tables by registration
id from the patient table to the encounters table by registration id and
customx. Or is it just as efficient to link to the registration id and date
and time in the encounters table. In other words reduce the primary keys
from 3 to 2 in the one table.

 

[John Vinson]

I want to create two tables from my source data. The source data contains
patient registrations and encounters based on date and time of registration.
I want to split the data, create a patients table and and an encounters
table. The patient table is straight forward, create a table based on the
unique registration id. For the encounters, the table will be based on the
registration id and date and time. Is it more efficient to convert the date
and time to a numeric number (customx) and link the tables by registration
id from the patient table to the encounters table by registration id and
customx. Or is it just as efficient to link to the registration id and date
and time in the encounters table. In other words reduce the primary keys
from 3 to 2 in the one table.

Just a jargon alert: A table can, by definition, have ONLY ONE primary
key. That one key might consist of one field or it might consist of
ten fields - but it's one Primary Key.

I'm questioning the basis of your question on two fronts here. An
Access Date/Time field by design stores both a date portion and a time
portion. In fact, it's stored as a Double Float number, where the
integer portion of the number is the number of days since December 30,
1899; and the fractional portion is the time (e.g. 0.5 is noon, 0.75
is 6pm). As such it is wasteful and unnecessary to have separate date
and time fields.

More importantly, if you're joining your Registrations table to the
Encounters table, you join ONLY on the RegistrationID. Whether the
Encounters table has the RegistrationID as a portion of its primary
key, or has a separate "surrogate" key, is altogether irrelevant -
you're joining just on the one field. The Registration table would NOT
contain any information about the date and time of an encounter (since
it's relsted to many such encounters), so there is nothing other than
the one field that you CAN use for the join.

If you *do* have a need to join tables using multifield keys (for
instance, if the Encounters table with a two-field Primary Key is
linked one-to-many to a Billing table with multiple bills per
encounter), Access is perfectly capable of doing the join on two,
three, or ten fields. It's a matter of considerable discussion on this
group whether it's better to do so or to create a single-field
"surrogate" key such as an autonumber; good arguments can be
marshalled in support of either view.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Dale]

Thanks John,
My data is an extract that already has the date and time fields split,
perhaps I will combine the two in the "final" table, at least thats one less
field to worry about.

I think I've misunderstood the role of primary keys when building the table
relationships. For some reason I was under the impression that to create a
one to many table relationship, the tables had to be connected by each
of their primary keys with those primary keys being related to each other,
hence I would create the same primary key in the subtable but because that
created duplicates (many encounters per patient) I would also create a
secondary primary key. If I've understood you, you are saying I can have
two different primary keys in each table, not related to each other and
still create a one to many relationship by connecting from one primary key
to the related field in the subtable even if it is not the primary key for
that table. And in fact have since done so...but to go back was I
mistaken...are not the primary keys from table to table supposed to be
related?

 

[John Vinson]

I think I've misunderstood the role of primary keys when building the table
relationships. For some reason I was under the impression that to create a
one to many table relationship, the tables had to be connected by each
of their primary keys with those primary keys being related to each other,
hence I would create the same primary key in the subtable but because that
created duplicates (many encounters per patient) I would also create a
secondary primary key. If I've understood you, you are saying I can have
two different primary keys in each table, not related to each other and
still create a one to many relationship by connecting from one primary key
to the related field in the subtable even if it is not the primary key for
that table. And in fact have since done so...but to go back was I
mistaken...are not the primary keys from table to table supposed to be
related?

You did in fact misunderstand the role of primary keys.

It is *possible* to link two tables, primary key to primary key. Since
the PK field is by definition unique within the table, this always
gives you a one-to-one relationship - only one record in the second
table can possibly match.

Much more commonly, you will link from the Primary Key of the "one"
side table to a matching "Foreign Key" in the "many" side table. If
the PK is a single field, so is the foreign key; if it's three fields,
the foreign key is three matching fields. The Foreign Key might be a
part of a multifield primary key but this is by no means essential.
Frequently you'll just have a Long Integer (or other appropriate
datatype) field PatientID in a child table, and the join will be from
the primary key PatientID field in the main table to the PatientID in
the child table.

There is no such thing as a "secondary primary key". It's either
primary... or it's not. Each table should (MUST, in fact, to be a real
table) have its own Primary Key, which uniquely identifies the records
*in that table*. The Primary Key of one table would only very rarely
be the Primary Key of another table!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Dale]

Thank you John for taking the time to assist me in being better at what I
do...
Good thing this isn't my day job