Re: [OT] "advanced" database design (long)

Hi,

vladimir konrad wrote:
> I think that I understand basic relational theory but then I had an
> idea. What I would like to know if this is sometimes done or that I am
> possibly mad... Also, I do not know the terminology for this kind of
> thing so I do not know where and what to look for.
>
> Basically, instead of adding field to a table every time there is a
> need for it, have a table split in two: one holds identity (id) and one
> holds the attributes (linked to this id). For example, modelling
> subject (person):

[example stripped]

> The advantage I see, is that to add new fields (here "attributes") no
> db development would be needed and user could do this.
>
> The disadvantages I see is that the model is hard to work with (i.e. how
> do I see subject (with attributes) as a table - could cross join be
> used for this?. Also, hand writing the queries for this would be hard
> (possibly needed if user would like to write custom reports).
>
> Do people do this kind of thing (or I took it too far)? If yes, I would
> be grateful for pointers to examples or any other info on this...

Yes, this is known as eg. Entity-Attribute-Value model (cf. wikipedia).

IMO most times its disadvantages (it can be very hard to write
performant queries compared to the traditional row based model) weigh
higher than you gain (in flexibility) in relational databases. But it
sure has its uses cases.

Ciao,
Thomas