Re: Three table database - period (?)

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