Next: , Up: Queries which Join Records

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
     Abode: 42AbbeterWay
     Name: Mandy Nebel
     Dob: 21 February 1972
     Mobile: 0555 342123
     Abode: 42AbbeterWay
     Name: Bertram Nebel
     Dob: 3 January 1966
     Abode: 42AbbeterWay
     Name: Charles Spencer
     Dob: 4 July 1997
     Abode: 2SerpeRise
     Name: Dirk Spencer
     Dob: 29 June 1945
     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.