On Wed, Aug 26, 2015 at 9:45 AM, Igor Neyman <[email protected]> wrote:
> *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Melvin Davidson > *Sent:* Tuesday, August 25, 2015 8:18 PM > *To:* Adrian Klaver <[email protected]> > *Cc:* Jerry Sievers <[email protected]>; John R Pierce < > [email protected]>; [email protected] > *Subject:* Re: [GENERAL] PostgreSQL Developer Best Practices > > > > …. > > Before ANYONE continues to insist that a serial id column is good, > consider the case where the number of tuples will exceed a bigint. > > Don't say it cannot happen, because it can. > > …………………… > > *Melvin Davidson* > > > > Now, it’s easy to overcome this limitation. > > You just make concatenated PK (id1, id2) with both columns of BIGINT type. > > > Easy, yes, but at this point I'd probably resort to converting to a length-limited text field (so as ensure toasting never occurs). In general, I see the main advantage of artificial PK in NO NEED to change > multiple child tables, when NATURAL key changes in the parent table. And I > never saw a system where NATURAL key wouldn’t need to be changed eventually. > > So, my conclusion: use artificial PK (for db convenience) and unique > NATURAL key (for GUI representation). > > > I haven't really had a chance to implement this formally but I've had similar thoughts along these lines. One nice thing about this, in theory, is that you can have a different lifecycle and usage policy for those GUI identifiers and they can be made to be inherently changeable. A unique tag that you can remove from one entity and reuse on a different one should the need arise. David J.
