primary and foreign keys

[merlin777]

I'm an access (2007) novice and I'm trying to grapple with table relationships.

I understand the concept of dividing data into different tables to avoid repetition but I just can't quite get a grip on primary and foreign keys. I think the bit I'm missing is how to choose the foreign key.

I think the primary key is fairly straightforward - it's any unique field such as a customer ID. I'm not quite sure why you just don't use the record number which is always unique - that might give you an idea why I'm not understanding.

And I'm not sure how you choose what to relate the primary key to in another table. Does your choice of foreign key need to have the same name as the primary key?

Help.... brain hurts. :banghead:

I'll try to give you an example. I'm working towards an appointments database for medical appointments.

I want to end up with an appointment which includes:
patient name
therapist name
therapist job title
appointment time

I am thinking I need these tables:
Therapists table
therapist name
therapist job title

Patients table
patients name
NHS number
phone number

Therapist job titles table
senior therapist
therapist
junior therapist

appointments table
Patient's name
Therapist's name
Therapist's job title
appointment time

 

[Ranman256]

Access doesn't use record numbers. But You are correct, unique items ,like IDs should be the primary key...TPatients.PatientID. (Nhs)

Patients Name is a bad idea. Many folks have the same name, so Autonumber comes in.
It can be used as patientID if your facility doesn't have one.

All sub tables that contain data for that patient MUST have the PatientID (as long) and as a FK. Appt table will need :
ApptID (autoNum) pk
NHSid (long) as FK

Now you can have a relationship where Patients table is the master table,
And Appts is a child table. Same with the other child tables,where 1 master patient can have many child's in the sub table.

 

[CJ_London]

Primary and foreign keys are used to describe the relationship between records - The primary key is the 'parent' and the foreign key (in the 'other' table) is the 'family'. So in your context one therapist has many patients and is called a one to many relationship and is the first form of relationship.

But it may be that one patient can have many therapists (and you only need one in your entire patient list for this to become the rule). If this is the case then you don't have a one to many relationship, you have a many to many relationship which is the second form of relationship and handled through a linking table. In your case this is probably handled through your appointments table - one patient can have many appointments with many therapists and one therapist can have many appointments with many patients. But perhaps you have to register patients with therapists before they can have appointments?

The third and final form of relationship is a one to one relationship and is rarely required - for example one person has one address. You could put the address in a separate table for a one to one relationship, but more likely you will include it in the patient record.

So turning to your data sets, none of your tables have a recognisable primary key - the usual form is to use autonumber.

It is not advisable to use text since text can change. In your case, in theory you could use the NHS number as the primary key for patients, but what happens if the number changes, or is corrected or the patient does not have it? The thing about primary keys is they have to have a unique value and are used solely to uniquely identify a single record. In addition, I understand in the medical world, patient confidentiality requires that they cannot be identified, which they could through the NHS number so you need to use a different identifier.

With regards naming convention, it is entirely up to you. My personal preference is to name them with a suffix of PK and FK so I know 'which end' of a relationship the field belongs. Otherwise you can use a suffix of ID, key or anything you are comfortable with and name both the same, or different if you prefer. Whatever you decide, be clear and consistent across the whole application. Also just using ID whatever on its own is not recommended since it has insufficient meaning - use PatientID, AppointmentPK etc.

with regards the general naming of fields, avoid spaces and none alphanumeric characters - if you want 'Patient Name' rather than 'PatientName' to appear in your forms and reports, use the caption property for the field - that is what it is for.

Normally table names are described in the plural e.g. 'tblPatients' and the field names in the singular (PatientName).

So taking the above and apply to your data you would have something like

tblTherapistRoles
TherapistRolePK autonumber
TherapistRole text e.g. senior therapist, therapist, junior therapist


tblTherapists
therapistPK autonumber
therapistname text
TherapistRoleFK long - link to tblTherapistRoles for job title

tblPatients
PatientPK autonumber
patientname text
NHSnumber text
phonenumber text

tblappointments
AppointmentPK autonumber
PatientFK long - link to tblpatients for name and other details
TherapistFK long - link to tblTherapists for therapist name and on to job title
appointmenttime date/time
appointmentlength long - number of minutes

You will no doubt find you need to have additional tables to record things like therapist availability (normal working hours/days, time out for holidays etc), patient preferences and the like.

Designing your database structure is a whole different topic, but get a big bit of paper/board whatever and draw it out, work out where the data comes from and how it is going to get into the db, how the data is managed once it is in the db and what you want out and how you get it.

 

[merlin777]

Access doesn't use record numbers. But You are correct, unique items ,like IDs should be the primary key...TPatients.PatientID. (Nhs)

Patients Name is a bad idea. Many folks have the same name, so Autonumber comes in.
It can be used as patientID if your facility doesn't have one.

All sub tables that contain data for that patient MUST have the PatientID (as long) and as a FK. Appt table will need :
ApptID (autoNum) pk
NHSid (long) as FK

Now you can have a relationship where Patients table is the master table,
And Appts is a child table. Same with the other child tables,where 1 master patient can have many child's in the sub table.

Thanks for that. May I ask what you mean by 'TPatients.PatientID. (Nhs)'?

How do you decide which should be the master table? In this case i wouldhave thought the appointments table should be the master as all the other tables feed into it?

 

[merlin777]

Primary and foreign keys are used to describe the relationship between records - The primary key is the 'parent' and the foreign key (in the 'other' table) is the 'family'. So in your context one therapist has many patients and is called a one to many relationship and is the first form of relationship.

But it may be that one patient can have many therapists (and you only need one in your entire patient list for this to become the rule). If this is the case then you don't have a one to many relationship, you have a many to many relationship which is the second form of relationship and handled through a linking table. In your case this is probably handled through your appointments table - one patient can have many appointments with many therapists and one therapist can have many appointments with many patients. But perhaps you have to register patients with therapists before they can have appointments?

The third and final form of relationship is a one to one relationship and is rarely required - for example one person has one address. You could put the address in a separate table for a one to one relationship, but more likely you will include it in the patient record.

So turning to your data sets, none of your tables have a recognisable primary key - the usual form is to use autonumber.

It is not advisable to use text since text can change. In your case, in theory you could use the NHS number as the primary key for patients, but what happens if the number changes, or is corrected or the patient does not have it? The thing about primary keys is they have to have a unique value and are used solely to uniquely identify a single record. In addition, I understand in the medical world, patient confidentiality requires that they cannot be identified, which they could through the NHS number so you need to use a different identifier.

With regards naming convention, it is entirely up to you. My personal preference is to name them with a suffix of PK and FK so I know 'which end' of a relationship the field belongs. Otherwise you can use a suffix of ID, key or anything you are comfortable with and name both the same, or different if you prefer. Whatever you decide, be clear and consistent across the whole application. Also just using ID whatever on its own is not recommended since it has insufficient meaning - use PatientID, AppointmentPK etc.

with regards the general naming of fields, avoid spaces and none alphanumeric characters - if you want 'Patient Name' rather than 'PatientName' to appear in your forms and reports, use the caption property for the field - that is what it is for.

Normally table names are described in the plural e.g. 'tblPatients' and the field names in the singular (PatientName).

So taking the above and apply to your data you would have something like

tblTherapistRoles
TherapistRolePK autonumber
TherapistRole text e.g. senior therapist, therapist, junior therapist


tblTherapists
therapistPK autonumber
therapistname text
TherapistRoleFK long - link to tblTherapistRoles for job title

tblPatients
PatientPK autonumber
patientname text
NHSnumber text
phonenumber text

tblappointments
AppointmentPK autonumber
PatientFK long - link to tblpatients for name and other details
TherapistFK long - link to tblTherapists for therapist name and on to job title
appointmenttime date/time
appointmentlength long - number of minutes

You will no doubt find you need to have additional tables to record things like therapist availability (normal working hours/days, time out for holidays etc), patient preferences and the like.

Designing your database structure is a whole different topic, but get a big bit of paper/board whatever and draw it out, work out where the data comes from and how it is going to get into the db, how the data is managed once it is in the db and what you want out and how you get it.

How do you decide which should be the master table? In this case i wouldhave thought the appointments table should be the master as all the other tables feed into it?[/QUOTE]

Wow - that's fantastic. I really appreciate your help. I think i learnt more from your one post than from an afternoon of googling and reference books.

May I check a couple of things? This first query might explain why I cant quite grip this.

Where you have said, for instance, 'TherapistFK long - link to tblTherapists for therapist name and on to job title' - How does that work in practice? i.e., this is one field in tblappointments but it's going to have at least the name and job title?

also, what does 'long' refer to?

thanks again. Finally starting to see some light!

 

[merlin777]

Oh, btw, you are quite right about NHS numbers and hospital numbers as primary keys - patients just never remember them or get them mixd up so they aren't really safe to use.

 

[Galaxiom]

also, what does 'long' refer to?

Long is Long Integer. It is a 32 bit whole number and stores integers between negative 2^31 + 1 and positive 2^31 - 1. (Basically a bit more than plus and minus two billion.)

 

[CJ_London]

Where you have said, for instance, 'TherapistFK long - link to tblTherapists for therapist name and on to job title' - How does that work in practice? i.e., this is one field in tblappointments but it's going to have at least the name and job title?

TherapistFK in ttblAppointments links to TherapistPK in the tblTherapists - you can 'travel along' that link to get the name, then 'travel along' the link to tbltherapistroles to pick up their job title.

Or you could travel the other way from say tblTherapists to tblAppointments to find out what appointments they have, then travel on to tblPatients to find out who with.

The point is you can 'travel' both ways along a relationship regardless of its type. In a one to many relationship, travelling from a single record primary key you can end up with many records in the foreign table, but travel the other way from the foreign table and you will end up with only one primary table record.

How do you decide which should be the master table? In this case i wouldhave thought the appointments table should be the master as all the other tables feed into it?

You can argue the appointments table is the master table at the moment but it only looks like that because it is a many to many join table with some times in it in a relatively simple database. As you develop, you will no doubt find you need other tables - perhaps patient ailments or treatments which will have just as much data, if not more 'feeding into them'.

So there is no real 'master table' (which is Excel thinking) - just a collection of data linked together in a rational way that meets the business needs. Any query just uses whatever tables are required for the purpose in hand. So reporting on therapists appointments to find out when they are free, you start with the therapist table and connect to the appointment table. If you wanted to know what appointments they already have and who with, you would also include the patients table.

Assuming your patient table included a date of birth field and you wanted to know how may patients between the ages of 20 and 30 had appointments you would start with the patient table and include the appointments table.

and so on

also, what does 'long' refer to?

further to Galaxiom's comment, the autonumber is also a long datatype - and both ends of a relationship need to be the same datatype.

Also, no need to repeat the whole thread, just copy and paste the bits you want to continue a discussion on and surround with the quote codes

 

[merlin777]

once again, many thanks. Ithink plenty of sstrong coffee and i'll be spending the day trying to get my head around all that.....

 

[Ranman256]

there are plenty of books on Access table design.

 

[merlin777]

there are plenty of books on Access table design.

.... and I have quite a few of them! This is the one topic they all explain very poorly or gloss over. I've been a fairly advanced excel user for a long time and I think its shaped my thinking in a way that's unhelpful when understanding access.

 

[Minty]

.... and I have quite a few of them! This is the one topic they all explain very poorly or gloss over. I've been a fairly advanced excel user for a long time and I think its shaped my thinking in a way that's unhelpful when understanding access.

This is pretty common for "power" excel users, one good way of trying to alter the thinking is that excel tends to be set out as left to right data.
Access need to be thought of a vertical storage. Any time you name a table or field in it with what could be considered data, eg. Appointment1, Appointment2 etc. you know you are heading in the wrong direction.

 

[merlin777]

This is pretty common for "power" excel users, one good way of trying to alter the thinking is that excel tends to be set out as left to right data.
Access need to be thought of a vertical storage. Any time you name a table or field in it with what could be considered data, eg. Appointment1, Appointment2 etc. you know you are heading in the wrong direction.

Thanks, Minty. Appreciate the advice.

 

[stopher]

Have a read of this:
https://www.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html

 

[The_Doc_Man]

Here's my two cents' worth.

There is usually no "master table." There are ENTITY tables and relationships among/between entities. You will run across this term A LOT and also lots of variants thereof. What do we mean by ENTITY? It is merely that you are tracking something with your database as a model of the actual nature of your business. So you gather the descriptions of like objects / persons / actions into a single table per entity.

In your model, Patients are one type of entity. So you have a table of patients because you group similar entries to the same table. Therapists are another type of entity. You have a separate table of therapists because the properties of all therapists (with respect to the business model) must be similar to each other - but different from patients. Appointments would also be an (abstract) entity because your business tracks this concept and probably bills patients through this information.

As to primary and foreign keys, it becomes easier to decide which is which if you look at the most elementary entities. Patients will certainly need a PK. Therapists will need a PK. Appointments might or might not have a PK depending on viewpoints and usage, but they certainly will have two FK fields because they establish a relationship between two other entities - and here is the key point. Access REQUIRES that you have a PK and FK involved for any table that has "family" relationships. And in that case, the PK identifies the parent of the family and the FK is part of each family member's records. Each member has an FK that contains the same value as the PK of the family's parent. The child record points to its parent. Therefore, FK and PK have FUNCTIONAL importance. The Access rule is that the "one" side of a "one/many" relationship MUST have a PK and the "many" side can reference its parent by copying the parent's PK to the child's FK.

There is a huge dichotomy regarding choices of keys. Certain keys might not be appropriate for regulatory reasons; others are simply impractical. In general, your machine's hardware works best with certain key sizes because the x86 chip's comparison instructions work best with those sizes. So for Access, the two or three most common key sizes are WORD, LONG, and SHORT TEXT (where the length of the short text does not exceed 4 bytes). Not that you couldn't have other sizes, but these three are probably most efficient.

When you use an AUTONUMBER key you are using what is sometimes called a SYNTHETIC key or a SURROGATE key. If the table is fully normalized, then each record is unique and the key value will be unique. Thus, your generated key can stand in for the record (which is the whole point of NORMALIZATION). Since a LONG field allows you to have up to 4 billion unique values, having a LONG field as a PK is probably pretty good. You won't exhaust the value space for that keyspace too soon.

However, sometimes text is OK. For example, if you have a table of USA states, a 2-character state abbreviation exists for every state, courtesy of the U.S. Postal Service. There, a NATURAL key exists. You could scratch your head here and ask why this is a natural key, since USPS made up some the abbreviations. The answer is that there is already a valid, commonly used key for states and the computer does not have to make up a new key (think: Autonumber) for you. The abbreviations are already "baggage" when talking about individual states so YOU don't have to make them up, and that is why they are NATURAL keys.

In a department store, the infamous SKU number could be a PK for a sales/inventory system, and thus MIGHT be natural. The number on your insurance card is probably a NATURAL key. On the other hand, when a bank takes your on-line transaction, the transaction "confirmation" number is probably synthetic.

There are those who swear by surrogate keys and those who swear at them. It is sort of like the "tomato / tomahto" debate or "Republican / Democrat" debate. It truly does not matter which one you choose as long as your choice is a valid candidate for being a PK. If it is mutable over its lifespan, it is not a good candidate. If it is not guaranteed to be unique, it is not a good candidate. (Consider those last two reasons and then think about a woman who marries and takes her husband's last name, which happens to be SMITH - so "LastName" as a PK probably won't work.)

 

[gemma-the-husky]

with regard to PK, FK - this is relevant when you have a one to many situation.

One record in the main table relates to many records in the subsidiary table. So in the case, the main table has a field(s) that constitute the PK of that table, and this value needs to be included in the subsidiary. In the subsidiary table this value will generally be indexed and it then constitutes the FK.

In any system the master table/subsidiary table changes depending on the circumstances - as DOCMan said, there is no absolute de facto master table.

The important think is that you can only manage a one-to-many relationship. You cannot have a many to many relationship. You have to devolve the many to many into 2 one-to-many relationships.

Take a system that manages students and courses. A student can take many courses, and a course will have many students. So you have a many to many.

To model this you need

Students table
Courses tables
Student-Courses junction table

The latter table contain details of the course-students registrations.

And you get


Student 1 ----- many Registrations many ----- 1 Courses.

You query the registrations table to find
a) all the courses a single student takes
b) all the students enrolled on a given course

and information such as
c) the courses sorted by attendance count

and so on.

 

[Lightwave]

Stick with it Merlin - its easy to overthink concepts when first approaching them.

Defining mathematical concepts without using numbers is abstract and inefficient and this is why it is so very often difficult to understand things like algebra using text.

The same problem exists when trying to describe machines with absolutely no reference to previous knowledge.

The original description of a plane was
"A heavier than air flying machine"

it gets worse if you can't use the word flying and machine.

"A heavier than air object with the ability to travel while jumping through the air for prolonged periods using an invisible energy source probably based on combustion"

I would say to anyone who is doing loads of reading on a computer science topic and think they are getting nowhere. Sit down with the numbers and try and do it on paper.

 

[The_Doc_Man]

merlin, look at your other thread under GENERAL to see my response that relates to your problems with relationships.