Re: Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)

You should use No.Meaningful.Info.Integer.Data for the foreign keys.  If you use
the actual Customer Name for a primary key and foreign keys, and the customer
later changes their name or you have to correct a typo, you risk breaking the
links unless you have all your cascading triggers set up just right.  Use a
meaningless ID and your links stay the same even if the text changes.

Also, you'll probably use the ID's a lot as join criteria in your queries.
Joining on two integer fields should be faster than joining on two character
fields.  If you have any intersection tables for many-to-many relationships that
just store a pair of foreign keys, the intersection table will be much smaller
if it is just storing a pair of integers, as opposed to a pair of
varchar(100)'s.  Just multiply the difference per row by the number of rows
you're likely to have to estimate how much you'll save.  Yes, the varchar's
probably won't take up a whole 100 characters every row, but I imagine there's
also overhead to keep track of the length.




Gonzo Rock <GonzoRock%Excite.com@interlock.lexmark.com> on 07/27/2001 02:02:15
PM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)



Both Techniques have a unique foreign key back to the parent tables but one uses
No.Meaningful.Info.Integer.Data for the ForeignKey while the second uses
Human.Understandable.ForeignKeys

Is one recommended over the other??? Sure appreciate the commentary before I get
in too deep with all these tables.

Thanks!



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org