Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Developing tables in a parent-child relationship in DB2

When developing tables in a parent-child relationship, use a primary key and a foreign key along with a unique ID to make your database easier to maintain over time.

I have a question about table design and performance. We have six tables in a parent-child (one-to-many) relationship with each other and each child table has an FK up to its parent.

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?
In the second approach, I believe that all of the sequence numbers would add complexity in terms of how the data is accessed and management of the sequence numbers themselves. The approach of using primary key and foreign key along with a unique ID should perform fine, assuming that you provide the optimizer will all of the necessary indexes.

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)