On Mon, 22 Nov 2004 16:54:56 -0800, Josh Berkus <josh@xxxxxxxxxxxx> wrote: > Alexandre, > > > What is the common approach? Should I use directly the product_code as > > my ID, or use a sequantial number for speed? (I did the same for the > > company_id, this is a 'serial' and not the shor name of the customer. > > I just don't know what is usually done. > > Don't use SERIAL just because it's there. Ideally, you *want* to use the > product_code if you can. It's your natural key and a natural key is always > superior to a surrogate key all other things being equal. > > Unfortunately, all other things are NOT equal. Here's the reasons why you'd > use a surrogate key (i.e. SERIAL): > > 1) because the product code is a large text string (i.e. > 10bytes) and you > will have many millions of records, so having it as an FK in other tables > will add significantly to the footprint of the database; Thanks for those tips. I'll print and keep them. So in my case, the product_code being varchar(24) is: 4 bytes + string size (so possibly up to 24) = possible 28 bytes. I did the good thing using a serial. For my shorter keys (4 bytes + up to 6 char) I will use the natural key. This is interesting, because this is what I did right now. The "transparent surrogate keying" proposal that is discussed bellow in the thread is a very good idea. It would be nice to see that. It would be easier for the DB admin and the coder; the moment this is not slowing the system. : ) Best regards. -- Alexandre Leclerc
