| > |
QUESTION POSED ON: 13 April 2009
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?
|
|
