Re: merging small tables

Galen,

you may improve your idea by using sequences that start at different values and hop in intervals > 1. E. g., sequence 1 starts at 1 and hops by 100 each time it is selected from. Sequence 2 starts at 2 and also hops by 100 etc. We implemented this as a simple but very efficient type and sanity checker.

Martin

Galen Boyer wrote:
>
> 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.
Received on Thu Aug 08 2002 - 12:03:12 CDT