 |
 |
Archives of the TeradataForum
Message Posted: Tue, 27 Sep 2005 @ 09:38:01 GMT
| Subj: | | Re: Design Considerations for FS-LDM/3NF model |
| |
| From: | | Victor Sokovin |
| | The FS-LDM makes extensive use of system generated keys (SGK), so while the UPI typically appears to be on the PK, this is simply because
the PK is the SGK (anyone wishing to begin a long and involved philosophical discussion of generated vs. natural keys, please try to resist).
There are a number of very good reasons for using SGK's, but for my money the most compelling is the space consideration involved in migrating the
natural key data to each row of every dependent table. | |
The problem with arguing with this is that the approach sells too well. It does not require much thinking, seems to be easy to follow and
universal enough, and it even might work. For a couple of months, that is. When the problems arrive, they are often perceived as *new* problems.
No thinking at the LDM/PDM phase certainly helps to perceive all problems as new, not related to the LDM/PDM.
| | It's important to remember that in Teradata, the main purpose of the primary index on a table is distribution, then all other
purposes come after that. The best PI is one that produces a relatively even distribution across the AMPs, which helps to ensure efficient query
processing in parallel. | |
This sounds like a TD-centric point of view. It is best for, say, running abstract queries for the purpose of DB performance benchmarking.
On a real life commercial project things can be different. It is often difficult to find folks willing to properly maintain essential business
keys in the DWH. Try to persuade them to understand and maintain the surrogate keys and the relations between natural and surrogate keys. The
words "system generated" used at the initial project phase somehow create an illusion that there will be no maintenance required. It's again a
very strong selling point but it does not help "a few months later".
Sorry, could not put in a few arguments but I'll try to stay short and practical rather than long and philosophical.
Regards,
Victor
| |
