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.
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL. Continue Reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading