Re: DB design advice: lots of small tables?

lender <crlender(at)gmail(dot)com> 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