On the topic of 'natural' versus 'synthetic' primary keys, I am generally in the camp that an extra ID field won't cost you too much, and while one may not need it for a simple table (i.e. id, name) one might add any number of columns later, and you'll be glad to have it.
I am, however, against using sequences (or serial integers in Postgres) for reasons of scaling and replication across multiple copies of a database running on different servers.
My preferred method is to give every table an ID column of UUID type and generate a UUID using the uuid-ossp contrib module. This also prevents someone not familiar with the database design from using an ID somewhere they should not (as is possible with natural PKs) or treating the ID as an integer, not an identifier (as is all too common with serial integers).
On Mon, Apr 1, 2013 at 9:35 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Mon, Apr 1, 2013 at 1:22 AM, Jasen Betts <jasen@xnet.co.nz> wrote: > On Mon, Apr 01, 2013 at 07:08:15PM +1300, Tim Uckun wrote: >> > >> > how about using an enum instead of this table? >> > >> > >> That's an interesting idea. Are enums mutable? > > > since 9.1 you can add values. > > http://www.postgresql.org/docs/9.1/static/sql-altertype.html
It's an interesting idea, but I don't think enums are designed to act as a primary key except in cases where the data is basically static and is relatively small. For starters, any manipulation of the enum requires a lock.
enums can be a real life saver when you need custom ordering built into a string, especially if that ordering is floated over a composite index. merlin
