Re: Three table database - period (?)

Jan Hidders wrote:

> Jens wrote:
 

> 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.

That's the main reason I was thinking about this. The schema is is in the application logic (objects) - I can change the objects dynamically without having to change the database.

As the attribute table can (and will) get really big I want to avoid expensive joins between the tables so I was thinking it might be better to make the join in the application where the attributes are really needed and I can put in some intelligence in handling them. This really is not a join per se. I would just be using the indexes to find the right attributes.

>
> 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

Yes, this looks better. I would change Name to NameID and add one table containing all the names available if I wanted to homogenize the attributes and save some more space.

>
> That saves some space (no NULLs) and you can add new types of
> attributes without changing the existing tables.

Exactly.

Thanks,
Jens Received on Tue Jan 30 2001 - 20:38:44 CET