On Feb 15, 2008, at 1:43 PM, Scott Marlowe wrote:
Generally speaking, I tend towards using the real value as the key and foreign key in lookup tables, but occasionally using an artificial numeric key is a better choice.
Something to consider here... any table that will have either a lot of rows or a lot of "type" fields will likely be better off with a phantom key (such as a serial) rather than storing text values in the base table. As an example, we have a 500G database at work that currently doesn't use any phantom keys for this kind of thing. I recently estimated that if I normalized every field where doing so would save more than 1MB it would reduce the size of the database by 142GB. Granted, about half of that is in a somewhat unusual table that logs emails (a lot of the emails have the same text, so the gain there is from normalizing that), but even discounting that 75G is nothing to sneeze at in an OLTP database.
-- Decibel!, aka Jim C. Nasby, Database Architect decibel@xxxxxxxxxxx Give your computer some brain candy! www.distributed.net Team #1828
<<attachment: smime.p7s>>
