Re: Modeling question...

On Sep 12, 3:29 am, Volker Hetzer <firstname.lastn..._at_ieee.org> wrote:
> JOG schrieb:
>
>
>
> > On Jul 25, 3:33 pm, Volker Hetzer <firstname.lastn..._at_ieee.org> wrote:
> >> JOG schrieb:
>
> >>> On Jul 25, 3:05 pm, Volker Hetzer <firstname.lastn..._at_ieee.org> wrote:
> >>>> Bob Badour schrieb:
> >>>>>>> Ooooh! Reinventing EAV with levels...
> >>>>>> Possibly. I had a look at
> >>>>>>http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htmanddidn'tfind
> >>>>>> anything exciting.
> >>>>>> All my attributes (key value pairs) are (for the purpose of this
> >>>>>> discussion) strings, so the Data tables hierarchy ends with
> >>>>>> EAV_Objects in the first image of that link.
> >>>>>> My problem is that, that I haveTest  three different "Objects_1"
> >>>>>> tables and I'd like to avoid having to replicate the EAV_Objects-Table
> >>>>>> for each "Objects_1"-Table.
> >>>>>> OTOH, I could have the "level" entities all be children of an id table
> >>>>>> and
> >>>>>> put the key value pairs into a child of that table. I need to try this
> >>>>>> out.
> >>>>>> Thanks for providing the pointer!
> >>>>>> Volker
> >>>>> Just to be clear, I was more than offering a pointer. I was also
> >>>>> ridiculing the idea of EAV.
> >>>> I got that. :-)
> >>>> But "we want to be able to create and delete attributes" is a customer
> >>>> requirement. I think it's different from "I am too lazy to do a proper data
> >>>> model". There are plenty of "normal" attributes left to model ERD like.
> >>>> Lots of Greetings!
> >>>> Volker
> >>>> --
> >>>> For email replies, please substitute the obvious.
> >>> What's wrong with drop/add column?
> >> All the things that are wrong if an application requires DDL during its normal
> >> state. No undo, no scalability, limits on the number of attributes, limits on
> >> the structure of the attribute names, the same attributes in each
> >> project/pcb/etc. and so on.
>
> > If one is changing trying to change the attributes that entities
> > possess, than one is necessarily altering the propositions that can be
> > stated about them. This necessitates a change in relation predicates,
> > which means it is absolutely a DDL issue. To think otherwise seems to
> > somewhat miss the point of the relational model.
>
> Bit late but I was on holiday...
> I am not trying to change the attributes that an entity possesses but
> I am allowing each business object or user object or whatever you may
> call it to contain an attribute collection. There are no changes in
> relation predicates since any attribute name is just contents, like
> its value.
> I think we are talking at cross purposes.
>
> I'm curious, what do you people do when a customer comes and says,
> "I want to store <thing> and I want to add, change and remove key
> value pairs and I want to name them freely.".
> Are you telling them that a database can't do it? That whatever the
> reason, it's stupid? That no more than 255-minus-housekeeping
> attributes are allowed because, say, oracle can't do more columns?
> That no attributes can contain more than 22 or whatever characters?
> What do you say when they ask the some intern and he comes up
> with a <thing_id>,<attribute_name>,<attribute_value> table attached
> to the <thing> table by a one-to-many relation and tell you this
> is what they want?
>
> Lots of Greetings!
> Volker
>
> --
> For email replies, please substitute the obvious.- Hide quoted text -
>
> - Show quoted text -

Volker,

If you just want something like forgotton password question/answer data:-
User_Name='JOE', Question = 'Fav color', Answer = 'pink', OtherStuff='blah'...
User_Name='JIM', Question = 'Fav color', Answer = 'purple', OtherStuff='blah'...
User_Name='BILL', Question = 'How much money do I have', Answer = 'way too much'

then that's OK, the entire table is not structured around name/value pairs. Users are allowed to have different question/answer pairs.

BUT if you want name/value pairs for everything then you're asking for something very different, you're asking for trouble. It doesn't only go against fundamental principles of the relational model, you might say "so what that's so airy fairy" but many database vendors have gone out of their way to implement according to that theory so the consequences will become very practical very quickly:-

-You can forget about decent performance for one.
-You can forget about maintainable queries as well.
-The application code will also suffer.

You should try writing some queries using proper relations and then try the same with name/value pairs. Especially try joins involving name/value pairs. Yuck. I don't know this guy but at a glance those queries are started to look part, but I've seen much worse (http:// tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design- mistakes.html)

Now the multi-level part. Normally self-joins can be used to achieve a tree like structure but you need to be careful because many databases can choke if you don't implement them the way they like. Now you wanted name/value pairs with levels - ouch!

You should serious follow the advice already given to you by the others, devise a proper relational model and avoid EAVs Received on Fri Sep 12 2008 - 04:11:58 CEST