On 6/20/07, Richard Broersma Jr <rabroersma@xxxxxxxxx> wrote:
--- "Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx> wrote: > The value of a surrogate key is easy retrieval and really has nothing to > do with normalization or proper modeling. > > I often add a surrogate key, even when one is not required just so I > don't have to worry about have a 4 element where clause. I've often wondered about this. Since PostgreSQL allows FOREIGN KEYS to be referenced from UNIQUE (non-primary) natural keys, couldn't the schema be designed so that every table has a surrogate PRIMARY KEY and yet still maintain the relationships using UNIQUE natural keys. Would a design like this be practical?
yeah, although I prefer to throw the primary key on the natural. Either way, the natural key is identified...my major issue with the surrogate design style is that the natural key is often not identified which inevitably leads to a mess. I also find databases with natural keys to be much easier to follow and feel much 'cleaner' to me. People who've never seen a large database without surrogates will be amazed at how much more expressive the tables are. Surrogates have certain advantages but I classify them as an optimization, meaning they should be introduced at the last possible moment in the design. merlin
