Re: merging small tables

On Sat, 3 Aug 2002, stjepan.brbot_at_zg.hinet.hr wrote:
> I have a lot of small tables of type [no,itemname] having few (3-10)
> records and those tables will never be bigger! Regarding the
> performance and rationality of using DB space, is it better to create
> a new table of type [domain,no,itemname] and merge all records from
> small tables and distinguishing them by domain?

I had this design flaw for a couple of my earlier designs. It certainly seemed to simplify the data model. :-( The problem is that on the FK relationships to this one table, the only thing you can guarantee is that the id inserted into the child table exists in the lookup table. You can't guarantee that the correct type for the child table was inserted. With a table per type, you are quaranteeing this.

An even more solid quarantee is to use a single sequence number for the lookup ids, which makes sure that one doesn't inadvertently insert an id in the child which exists in the parent but was meant to exist in some other parent (although, this is less likely of an occurrence).

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.