Re: Modeling question...

Alvin Ryder schrieb:
> 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.
Ok, I see the misunderstanding here.
Yes, you are right, ours aren't attributes that lead to foreign keys or even constraints, it really is arbitrary data that only gets displayed. I like your example.
Here's ours:
Team one thinks it wants to remember some implementation detail of the firewire port of a board. So they decide they want an attribute FIREWIRE_TYPE and they want this to be a number. That does not mean I (the database) am to do calculations with this number, just that they want some simple type checking in the input mask. This also does not imply that other pieces of data depend on that attribute being there or having a certain value.
Team two doesn't do pc boards today but some industrial stuff so they decide they want an attribute CANBUS_AVAILABLE for their project and want this to be boolean.
What none of the guys want is to sit on a huge heap of predefined attributes, consisting of each and every attribute ever entered or required by them.
So, each board project starts out afresh, with attributes added as the team members see fit. Each new project has some new attributes because technology advances or people think that different bits are important. (It's a bit less arbitrary because for each board family the teams meet up and decide upon the attributes for that family. This happens several times a year and of course, one attribute is always overlooked in the first spec.)

OTOH: Of course, each project has a name, belongs to a family, has assembly variants and stuff and all /these/ things are modeled properly (that is, not as EAVs) because they form relations and are the skeleton of the whole application.
The forgotten-password-type-attributes are just things dangling off some of the real entities.

> BUT if you want name/value pairs for everything then you're asking for
> something very different, you're asking for trouble.
I fully agree. This is also the deal I've insisted on with the customers. As soon as I have to do something with the data (apart from storing and retrieving) it's an entirely different quality and needs time, causes implementation costs, beta tests and all the things associated with an application change.
They have assured me that this definitely will not happen, so I think it will happen less frequently than once or twice per year, with decreasing frequency as the application matures. That means we can incorporate this into a normal software development cycle.

> You should try writing some queries using proper relations and then
> try the same with name/value pairs.

No, thanks. I know what you're driving at and I see the same problem. If customers need to store a bunch of named sticky notes, all right, but that means named sticky notes is all they get.

> 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!
No, the self joins work with "real" data, that is, properly modeled stuff. :-)

> You should serious follow the advice already given to you by the
> others, devise a proper relational model and avoid EAVs
Believe me, I do. It's just that sometimes it's kind of hard to explain the problem to people from a very different background. Thanks again for the lost password metaphor. It really hit the nail on the head.

Lots of Greetings!
Volker

-- 
For email replies, please substitute the obvious.