Re: IS it a good practice to use SERIAL as Primary Key?

>>> insert a new address, and update the users table to the new address_id
>>
>> Which changes the user's "primary key". My point was that having the address
>> id be part of the primary key is wrong.
>
> As I said, you don't *have* to do it that way. I was just giving an
> example. You could just as easily grab the address id, insert that into
> an archive table with a date stamp and then just update the address
> itself. Thus *not* changing the "Primary Key".

Thus making it more difficult to deal with historical data, and also
reducing the "address id" in the "user" row to nothing more than an
additional auto-generated number referencing address data that might as well
just be put into the user row, because that would be no less normalized
anyway than this single address row whose contents keep changing to
represent different addresses over time.

Using the address id as part of the user primary key forces this choice
between a user primary key which changes, or funkiness in tracking
addresses. Whether you change the id stored in the user row, or whether you
update the address row, you are still constructing a primary key from data
that is expected to change.

Even the justification for it is thin. The address is certainly one good way
to figure out which of two users with the same name is being referred to,
but that doesn't make it part of a reasonable primary key. Phone numbers,
credit card used, order history--all of these could help discriminate
between users and not one of them has any place in the users' primary key.

--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 722-0567 voice