Scenario B: two related tables

So, what happens if we model the person and hat information in line with the good practice of having a separate table for each entity? We would end up with two tables:

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

Entering the two sets of information into the two separate tables allows us to avoid all of the problems mentioned above, and crucially, will allow us to run our queries safe in the knowledge that the correct number of records will be returned every time.

As we shall see, tables are related by a relationship which connects one field in one table with one field in the second table, both fields containing the same information. In our People and Hats tables, the field used in the relationship is the PersonID field, where the ID number of the person is added to the record in the Hat table for those hats belonging to that person (so person number 3, Hercule Poirot, has his ID number associated with the records of the two hats that he owns (more on this in Section D4).

 

Return to Handbook

Last modified: Thursday, 25 July 2013, 1:40 PM