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.

 

[merlin777]

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

 

[merlin777]

please ignore this thread....

 

[xenou]

A simple rule to follow (if you can) is that a foreign key in one table is the primary key in the other table - i.e., if OrderID is the Primary key of the Order table, then OrderID might work as a foreign key in the OrderDetails table.