11.1 Foreign Keys

A better way would be to separate the addresses and people into different record sets. The first record set might look like this:

%rec: Person
%type: Dob date
%type: Abode rec Residence


Name: Alfred Nebel
Dob: 20 April 2010
Email: alf@example.com
Abode: 42AbbeterWay

Name: Mandy Nebel
Dob: 21 February 1972
Email: mandy@example.com
Mobile: 0555 342123
Abode: 42AbbeterWay

Name: Bertram Nebel
Dob: 3 January 1966
Email: bert@example.com
Abode: 42AbbeterWay

Name: Charles Spencer
Dob: 4 July 1997
Email: charlie@example.com
Abode: 2SerpeRise

Name: Dirk Spencer
Dob: 29 June 1945
Email: dirk@example.com
Mobile: 0555 342123
Abode: 2SerpeRise

Name: Ernest Wright
Dob: 26 April 1978
Abode: ChezGrampa

and the second (following in the same file), like this:

%rec: Residence
%key: Id

Address: 42 Abbeter Way, Inprooving, WORCS
Telephone: 01234 5676789
Id: 42AbbeterWay 

Address: 2 Serpe Rise, Little Worning, SURREY
Telephone: 09876 5432109
Id: 2SerpeRise

Address: 1 Wanter Rise, Greater Inncombe, BUCKS
Id: ChezGrampa

Here you can see that there are two record sets viz: Person and Residence. There are six people, but only three residences, because some residences accommodate more than one person. Note also that the Residence descriptor has the entry %key: Id whilst the Person descriptor has %type: Abode rec Residence. This is because Abode is the foreign key which identifies the residence where a person lives.

We could have declared the Id field as %auto. This would have had the advantage that we need not manually update it. However, we decided that the Abode field values in the Person records are better as alphanumeric fields, so that they can contain human readable values. In this way, it is self-evident by reading a Person record where that person lives. Yet since the Id field is declared using the %key special field name, you can be sure that you don’t accidentally reuse an existing key.