Re: Modelling objects with variable number of properties in an RDBMS

In message <1130787477.961032.188680_at_g44g2000cwa.googlegroups.com>, vc <boston103_at_hotmail.com> writes
>
>Bernard Peek wrote:
>
>> >> An EAV database could be used to collect
>> >> metadata during the early development stages, and that metadata could
>> >> then be used to build the final database system, replacing the temporary
>> >> EAV model.
>> >
>> >Please elaborate on this, because I can't make head nor tail of it.
>>
>> In the early stages of agile development you probably have only a vague
>> idea of what entities and attributes are going to be required in the
>> final system.
>
>This is an odd statement. How can you even define an entity if you
>have no clue what attributes it consists of ? Besides, it's been
>known how to handle the entity evolution for, like, ages. One can use
>schema evolution DLL statements (ALTER TABLE), inheritance (see
>Tutorial D), etc.

You can't define an entity until you have some attributes that don't match an existing entity. Discovering an attribute that isn't in any existing entity tells you that you have a new entity.

>
>> By building a system where the users can add entities and
>> attributes without involving the developer you allow the users to define
>> what the final system needs.
>
>So what you get is an unstructered pile of string of characters split
>into three subpiles. There is no notion of relationship too.
>
>To sum up briefly: you have no logical model, no relationships, no
>constraints, poor performance due to the need for excessive joins and
>character stings conversions, no data types. What's the gain ? With
>this approach, you do not need a relational database. Just three
>indexed flat files would suffice for this kind of stuff.

That's the traditional justification for the EAV system. In theory it's the perfect database structure because it can hold any data and its metadata. In practise it always falls apart if you let it go on too long because users aren't usually trained in data modelling. But if you only use it for prototyping there's limited scope for the users to break things before the developer returns to tidy it up.

>
>>After collecting the metadata you have the
>> information required for the data modeller to put together a properly
>> structured relational data model.
>
>What exactly do you mean by "metadata" ?

I mean the knowledge of the entities, attributes and relationships embedded in the data.

>
>"to put a properly structured relational data model", is it not what
>database design modelling is about ? Why not just do it with a pencil
>a piece of paper instead of wasting time on "EAV" ?

Because you want to do this on the fly while the prototype is in use. To turn your pencil and paper instructions into a modified system you need a skilled developer, and you don't always have one available looking over the users' shoulders while they work.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.