lender <crlender@xxxxxxxxx> wrote: > So, my first main question would be: is it "normal" or desirable > to have that many tiny tables? Yes. > And is it a problem that many of the tables have the same (or a > similar) column definitions? No. > To summarize, the second question is whether we should ditch the > artificial numeric IDs and just use the "code" column as primary > key in the new tiny tables. This one becomes more of a judgment call, but I generally lean toward using the visible unique identifier (your "code") as the primary key and ditching a numeric "id". The only two contra- indications would be if the "code" values have a significant chance of being changed or if you will have extreme numbers (billions) of narrow rows which must reference the table. <soapbox-rant> I occasionally hear someone maintaining that having a meaningless sequential ID column as the primary key of each table is required by the relational model. At those moments I swear I can actually hear E.F. Codd turning in his grave. It was a requirement of old pre-relational databases from the 60's and 70's, and some equally primitive ORMs still like to have one, but a big point of relational databases is that you don't need to navigate artificial linkages between tables -- the relationship can generally be determined by the fact that they contain common data elements. If these are natural, meaningful values which are visible to the user it often allows complex queries to be much better optimized, since they aren't forced through a single navigational linkage. </soapbox-rant> -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
