Re: Three table database - period (?)

Hi
Jens, that is the widely used approach for an object-oriented database design, that fits into a relational database, e.g. "Components" (by Fabasoft, www.fabasoft.com).
Angelika

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.
> >
> > You may even have a name for this 'little monster' :)
>
> It's very close the a meta data model. 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) and finally a problem may be the fact that you don't
> really have a database schema anymore. So you don't really now in
> advance what attributes of what type an entity is going to have, for
> example. But in some cases that can also be an advantage.
>
> Btw, I would advice to introduce a separate table for every type of
> attribute that you have, give attributes a name, and move the Type
> column to the EntityAttribute table:
>
> Entity(ID)
>
> EntityAttribute(EntityID, Name, AttributeID, Type)
> Pr.Key: EntitiyID, Name
>
> NumberAttr(ID, Number)
> Pr.Key: ID
>
> StringAttr(ID, String)
> Pr.Key: ID
>
> DateAttr(ID, Date)
> Pr.Key: ID
>
> ...
>
> That saves some space (no NULLs) and you can add new types of
> attributes without changing the existing tables.
>
> --
> Jan Hidders
Received on Tue Jan 30 2001 - 20:24:33 CET