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

> ...*most especially* when they are the only unique key.

There are usually other keys which should be unique, and this should
certainly be reflected in the db design. On the other hand, designers should
not strive to find and enforce combinations that won't actually necessarily
be unique, such as the above-cited example of first 5 letters of last name +
last 4 of SSN. (There are certainly more than 10,000 Smiths in the US. In
fact: there will be more than 10,000 Smiths in each of most of the 50
states!)

> If I base a master sales table on account_number and date/time, then
> every CPA in the country will descend on me with calculators
> sharpened if I decide to update the SALE_DATE column.

But if the company is sold/merged, it is likely that accounts will get new
account numbers, and even possible that account numbers will not be unique
across the union of the (formerly) two companies' accounts thus absolutely
requiring account number changes. This is exactly the kind of thing I'm
talking about, and why I think account # + date/time would be a lousy
primary key. It's fine to treat it as a key, but certainly not the primary.

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