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

> Shenanigans!
>
> That problem occurs regardless of whether or not you use surrogate keys.
> You have exceeded the scope of the example.

Yes the "problem occurs" in that this is something that needs to be tracked,
but the suggested schema presents peculiar problems for what otherwise is a
pretty simple thing.

Reasonable solution: every address is kept, so each address is a row in an
address table, with its own id. Shipment rows simply record the id of the
address current at the time of shipment. The user row simply records the id
of the most-recent address as the now-current one. That's rational.

But if you use the address id as part of the user's primary key, well now
you have a primary key that keeps changing as users move. On the other hand,
if you don't change the address key but the contents of the address row
itself, in order to preserve this bizarre notion of primary key, you have to
keep a copy somewhere of the same address with a different id in order to
use that for shipments.

>> You would update the address, the address id wouldn't change. If you
>> want to keep track of old addresses you would keep an archive table
>> associated with the user.id.
>
> But what about historical data that referenced the address? If you move
> today, I still want to know where I shipped last week's orders.

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