A -> B -> C -> D -> E -> F
There is some debate in our team about how best to accomplish this. Two approaches have been discussed. One approach is to have a unique ID (UID) for each PK/FK combination and then do selects for each table based only on this key.
Another approach is to use a UID only at the top table and then have sequence numbers (0,1,2,3, ...) for each additional table as you go down. Therefore, to access the top table all we need is a key.
To select rows from the second table we would need the composite key (FK, SEQ_NO1).
To access the third table, C, we would need the composite key (FK, SEQ_NO1, SEQ_NO2).
To access the 6th table we would need the composition key (FK, SEQ_NO1, SEQ_NO2, SEQ_NO3, SEQ_NO4, SEQ_NO5). In this case we need all the sequence numbers to fully specify the records we want from the table.
The belief is that DB2 will already have these records in the same set of pages for the second approach but will not have them for the first approach. So, page caching comes into play for second approach but not the first.
Hence it is reasoned the second approach is better than the first approach. Do these arguments have any merit?
Implementing your primary and foreign keys with surrogate keys will usually make your database easier to maintain over the long-term. A natural key-like customer number or employee number may be impacted by a business change. An identity column attribute or sequence object can be used to have DB2 automatically supply the values for your surrogate key columns.
This was first published in June 2009