Re: Three table database - period (?)

Vadim Tropashko wrote:
> In article <956s24$3vb$1_at_news.tue.nl>,
> hidders_at_win.tue.nl (Jan Hidders) wrote:
> > Jens wrote:
> > > Hi
> > >
> > > I have been wondering about the merits / pitfalls of the
> > > following table structure:
> > >
> > > Entity(ID)
> > >
> > > EntityAttribute(EntityID, AttributeID)
> > >
> > > Attribute(ID, Type, EntityID, Number, String, Date, Blob,
> > > ...)
> > >
> > > This describes an entity that has some attributes linked to
> > > it through the EntityAttribute table. The attribute table's
> > > Type field describes which of the attribute fields is
> > > actually used.
> > >
> > > I don't seem to need any more tables as I can describe
> > > anything with these three (or can I?). I'm also concerned
> > > about speed and space consumption but the flexibility this
> > > seems to give me is tempting.
> >
> > The drawbacks are indeed the
> > speed (more joins neccessary, constraint checking is going to be a lot
> > harder and will more often require joins) and space (lots of extra IDs
> > in lots of places)
>
> Performance implications are probably much more severe than just more
> joins. In traditional schema, for example, I could create composite key
> index -- what do I do in metamodel?

Nothing, you just define the one index on every attribute. :-) But that is "only" going to slow things down with a factor n where n is the number of attributes in the composite key.

-- 
  Jan Hidders