On 03/05/11 08:25, Jeff Davis wrote: > You can generate your own keys, and if you hand them out to customers > and include them on paperwork, they are now a part of the reality that > your database models -- and therefore become natural keys. Invoice > numbers, driver's license numbers, etc., are all natural keys, because > they are known about, and used, in reality. Usernames are, too, the only > difference is that you let the user choose it. I've repeatedly run into situations where I generate a key that seems entirely sensible, making a generated primary key part of the business processes ... then external constraints force me to change the format of that key or start accepting keys from outside. "Oh, we need to move to 14-digit client IDs because <x-system> that we interact with requires them". "We want invoice numbers to include a Luhn check digit, can you add that?". Etc. I'm now strongly in favour of keeping an internal key that users never see, and having separate user-visible identifiers. The users can demand that those identifiers change format or generation method and it's an easy change in only one place. Want two different keys? I can do that too. Record a key that matches some external system? That's easy. Want to be able to edit/override/rename keys? Yep, that's fuss free too, and it won't affect my audit history (which uses the real internal keys) or have to cascade to foreign key relationships in append-only ledger tables. I use a mix of surrogate and natural keys, depending on the situation. I see little point in surrogate keys for simple lookup tables, but find them invaluable in audited tables with lots of foreign key relationships that interact with other business systems. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
