Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one))
От | Antonios Christofides |
---|---|
Тема | Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one)) |
Дата | |
Msg-id | 20031104185125.GA5115@localhost обсуждение исходный текст |
Ответ на | Almost relational PostgreSQL (was: one-to-one) (Michael Glaesmann <grzm@myrealbox.com>) |
Ответы |
Re: Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one))
|
Список | pgsql-novice |
Josh Berkus wrote: > Another tip: beware of over-reliance on surrogate autoincrement > keys. They are convenient and necessary some of the time, but use > real keys where you can. The current industry reliance on "ID" > primary keys encourages sloppy thinking .... and sloppy schema ... by > DBAs. I have caught myself building tables without any real keys > this way. Speaking of this, I'm finishing the design of a database and I tried to follow this rule, but there were very few cases where I could. For example, I have a table with device types, the key to which could be the composite (manufacturer [a numeric id], modelname [a string]). I didn't want to use that, however, because the user might type the wrong modelname and later want to correct it. Not to mention that I've frequently seen a different model name on the cover and on the label of some machines. I decided to use natural primary keys only for four entity types (I have 21 in total): languages, countries, states of the US, and mime types (examples of keys are, respectively, "en", "UK", "CA", "image/jpeg"). You think that 4 out of 21 is a good score? Should I allow primary key changes? Since the RDBMS does not support cascade updates, I'd need to write triggers to do the job, and I would also have to make the constraints deferred. And I doubt it would apply to more than one or two entity types.
В списке pgsql-novice по дате отправления: