Designing Databases for Historical Research

D. Relationships

D4. Primary and Foreign Keys

Primary and Foreign Keys are absolutely crucial to the running of a relational database because they serve as the ‘anchors’ that keep relationships connected to the relevant tables. The keys are fields that serve a particular purpose within a table: they are not used to capture information drawn for the sources, but instead they are used to keep track of the information that is necessary for the database to know which records in one table are connected to records in the related table.

One of the ‘rules’ of database design (Section C5) states that ‘each complete record must be unique’, meaning that when taken as a whole, each record in a table must be unique. With historical sources, this can sometimes be problematic, as quite often the same information can crop up repeatedly over a period of time – such as, for example, when an individual is named as a witness to a number of wills across a decade. To make sure that the records of a table obey this particular rule of databases, it is necessary to guarantee that each record will be unique, and if the nature of our historical information prevents us from being able to guarantee this, we are forced to cheat. In doing so, we actually achieve a number of useful effects in the design of our tables.

The way that we guarantee that each record in a table will be unique is to add a field into which we can enter (or have the database enter automatically) a unique identifier, a value which will be different for every record that is added to the table. Normally this is a sequential number, such as the values in the various ID fields in the People and Hats database (Section D2). A sequential number value applied to each record that will be different for each record will guarantee by itself that every record as a whole will be unique – because the ID value will never be duplicated from one record to the next.

However this field which provides us with a unique value for every record also serves to act as the Primary Key field for a table, this being the field that acts as the anchor for the ‘one’ side of a one-to-many relationship. The field that acts as an anchor on the other side of the relationship that exists within the table that is on the ‘many’ side of the relationship is known as the Foreign Key. The Foreign Key field will not contain a unique value for every record: because it is on the many side of the relationships, the same ID value is likely to occur in more than one record. Both the Primary and Foreign Key fields contain the same information.

Consider the People and Hats database again:

Person table:

PersonID

Forename

Surname

Occupation

Residence

Age

Gender

HairColour

Income

1

Samuel

Spade

Builder

Peckham

23

M

Blonde

£25,000

2

Philip

Marlowe

Accountant

Dulwich

35

M

Dappled

£34,533

3

Hercule

Poirot

Plumber

Chelsea

26

M

Bald

£250,000

4

Miss

Marple

Doctor

Surbiton

58

F

Grey

£1.50

Hat table:

PersonID

HatID

HatType

HatColour

HatMaterial

1

1

Baseball cap

Black/red

Polyester

2

2

Bowler

Pink

Satin/fur

2

3

Crash helmet

Green

Kevlar/fur

3

4

Stetson

Brown

Leather

3

5

Captain’s hat

White/blue

Silk/braid

4

6

Nightcap

Grubby yellow

Rags

The relationship is one-to-many, a person can have many hats, and the relationship is anchored on the PersonID field which is present in both tables. The PersonID field contains the unique ID number that is assigned to every person who is entered in the Person table – because every person is only entered once in the Person table, the PersonID field in the Person table is unique, and it is the Primary Key. In the Hat table, because a person can own more than one hat, the Person ID value will not be unique – the same ID number will crop up every time a record contains a hat owned by that person (as is the case for persons 2 and 3). The PersonID field in the Hat table is therefore the Foreign Key.

When designing the database, there is a stage in the process where you will need to think about what fields will appear in your tables (see Section E2), and it is very important when doing so that you remember to identify the Primary and Foreign Keys for your tables. Every table you design should have a Primary Key field – a field with the datatype ‘autonumber’ which will generate a unique value for every new record you add. Not every table will have a Foreign Key field, only those that are on the ‘many’ side of a one-to-many relationship. When you do add a Foreign Key field to a table, remember that it will contain the same information (that is, the ID numbers) drawn from the field that is the Primary Key for that relationship. Without the Key fields, the database software will not be able to correctly manage the relationship, with the result that it will not be able to identify which records from the one table are connected to the which from the other, a situation that will make performing analysis or even simple retrieval of information all but impossible.