Modeling question...

Bob Badour schrieb:
> Volker Hetzer wrote:
>
>> Hi!
>> Not sure if this is the right group but I've come across a problem I'm
>> at a loss to model properly. Here's the setup:
>> A model contains three entities ("Level"s) describing projects:
>> - Project Family records, each referencing several
>> - Project records (with different attributes), in turn referencing
>> - Sub projects, with different attributes again.
>> Those three Project levels are connected by straight forward 1 to n
>> relationships.
>> But the problem is that they all have a bunch of key/value pairs.
>> So, a project family can have a key/value-pair StartDate=20080615.
>> But each project and subproject can have a different StartDate.
>> On the other hand, sub projects, projects and family don't need to have
>> the same key/value pairs.
>>
>> Now, the simple solution is to have three key_value tables and be done
>> with
>> it. However what I'd very much prefer is just one key_value table with
>> some
>> kind of "level" attribute, 0 being family, 1 being project and 2 being
>> sub project. The primary keys on the level entities are all numeric so
>> this would perhaps work but I have no idea how to get the foreign key
>> constraints done because the key_value table would have three parents.
>>
>> How does one model relationships in which
>> - one child table has several parent tables
>> - each parent record can refer to several child records
>> - each child record belongs to exactly one parent record in exactly
>> one of
>> the several parent tables?
>> Is there a declarative way to enforce consistency?
>>
>> Lots of Greetings!
>> Volker
>
> Ooooh! Reinventing EAV with levels...
Possibly. I had a look at
http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm and didn't find 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
--
For email replies, please substitute the obvious.
 >> Stay informed about: Modeling question... 

Volker,

> How does one model relationships in which
- one child table has several parent tables
- each parent record can refer to several child records <

I would use relationship tables for them. These will contain the keys
of the parent and the child table as foreign key and will compose the
primary key of these two columns.

> - each child record belongs to exactly one parent record in exactly one of the several parent tables? <

There, you could insert a column containing the key of the primary
table into the child table as a foreign key if you have such a direct
relationship.

I would not put all the relations into one table only. You could not
not properly enforce foreign key and other constraints there and would
create a collection of unrelated things in it what will be hard to
manage. Having a lot of relationship tables should basically not be an
issue, if they are named consistently and meaningfull.

brgds

Philipp Post
 >> Stay informed about: Modeling question... 

Volker Hetzer wrote:

> Bob Badour schrieb:
>
>> Volker Hetzer wrote:
>>
>>> Hi!
>>> Not sure if this is the right group but I've come across a problem
>>> I'm at a loss to model properly. Here's the setup:
>>> A model contains three entities ("Level"s) describing projects:
>>> - Project Family records, each referencing several
>>> - Project records (with different attributes), in turn referencing
>>> - Sub projects, with different attributes again.
>>> Those three Project levels are connected by straight forward 1 to n
>>> relationships.
>>> But the problem is that they all have a bunch of key/value pairs.
>>> So, a project family can have a key/value-pair StartDate=20080615.
>>> But each project and subproject can have a different StartDate.
>>> On the other hand, sub projects, projects and family don't need to have
>>> the same key/value pairs.
>>>
>>> Now, the simple solution is to have three key_value tables and be
>>> done with
>>> it. However what I'd very much prefer is just one key_value table
>>> with some
>>> kind of "level" attribute, 0 being family, 1 being project and 2
>>> being sub project. The primary keys on the level entities are all
>>> numeric so
>>> this would perhaps work but I have no idea how to get the foreign key
>>> constraints done because the key_value table would have three parents.
>>>
>>> How does one model relationships in which
>>> - one child table has several parent tables
>>> - each parent record can refer to several child records
>>> - each child record belongs to exactly one parent record in exactly
>>> one of
>>> the several parent tables?
>>> Is there a declarative way to enforce consistency?
>>>
>>> Lots of Greetings!
>>> Volker
>>
>>
>> Ooooh! Reinventing EAV with levels...
>
> Possibly. I had a look at
> http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm and didn't find
> 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.
 >> Stay informed about: Modeling question... 

On Jul 25, 3:05 pm, Volker Hetzer <firstname.lastn....DeleteThis@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.htmand didn't find
> >> 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?
 >> Stay informed about: Modeling question... 

On Jul 25, 3:33 pm, Volker Hetzer <firstname.lastn... DeleteThis @ieee.org> wrote:
> JOG schrieb:
>
> > On Jul 25, 3:05 pm, Volker Hetzer <firstname.lastn... DeleteThis @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't find
> >>>> 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.

> Sorry, but in my opinion DDL is for installation and maintenance. End users
> shouldn't trigger DDL neither directly nor indirectly.

No need to be sorry. If you want to make the same EAV mistakes that
countless have before you then that's up to you. All best, J.

>
> Lots of Greetings!
> Volker
> --
> For email replies, please substitute the obvious.
 >> Stay informed about: Modeling question... 

Bob Badour schrieb:
>>> Ooooh! Reinventing EAV with levels...
>>
>> Possibly. I had a look at
>> http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm and didn't find
>> 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.
 >> Stay informed about: Modeling question... 

Philipp Post schrieb:
> Volker,
>
>> How does one model relationships in which
> - one child table has several parent tables
> - each parent record can refer to several child records <
>
> I would use relationship tables for them. These will contain the keys
> of the parent and the child table as foreign key and will compose the
> primary key of these two columns.
>
>> - each child record belongs to exactly one parent record in exactly one of the several parent tables? <
>
> There, you could insert a column containing the key of the primary
> table into the child table as a foreign key if you have such a direct
> relationship.
>
> I would not put all the relations into one table only. You could not
> not properly enforce foreign key and other constraints there and would
> create a collection of unrelated things in it what will be hard to
> manage. Having a lot of relationship tables should basically not be an
> issue, if they are named consistently and meaningfull.
The idea we have right now looks like this:

Parent-child-table-chain (we do motherboards):
"project"->"schematic"->"pcb"->"variant"->"version"
This is a one-to-many-relationship from left to right.
Each table has as primary key an id ("owner_id") that gets taken from
the same sequence, so the id's in those tables are all different.

"Attributes" is a table with three columns: "owner_ID", "Name", "Value", with
parent_id and "Name" being the primary key.

Now, every of the parent-child tables has the attributes table as additional
parent. As you see, we reversed the parent-child relationship between
attributes and the other tables. Now, the attribute table has a
zero-or-one relationship with each of the parent-child tables.

The only weakness I see is that I see no way to enforce that the
parent-child-chain-tables share the same primary key-space, i.e. that
each id shows up in no more than one table.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.
 >> Stay informed about: Modeling question... 

"Volker Hetzer" <firstname.lastname.RemoveThis@ieee.org> wrote in message
news:g6cmjq$9s9$1@nntp.fujitsu-siemens.com...
> Bob Badour schrieb:
> >>> Ooooh! Reinventing EAV with levels...
> >>
> >> Possibly. I had a look at
> >> http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm and didn't find
> >> 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.
>

Here's the problem with the customer requirement that "we want to be able to
create and delete attributes": the same customers nearly always want to be
able to derive value from the data in the database by using it with standard
queries to drive standard reports, charts, or extracts that can be fed to
another process. After all, that's inherent in database data, isn't it?
Well, not if the customers can each invent their own attributes, it isn't!

If you've never been down this road before I suggest you stay in the same
job long enough to see what happens when you tell the customers that each of
them has to design their own queries, because each of them defined their own
attributes. What happens isn't pretty!

> Lots of Greetings!
> Volker
> --
> For email replies, please substitute the obvious.
 >> Stay informed about: Modeling question... 

JOG schrieb:
> On Jul 25, 3:05 pm, Volker Hetzer <firstname.lastn....DeleteThis@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.htmand didn't find
>>>> 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.
Sorry, but in my opinion DDL is for installation and maintenance. End users
shouldn't trigger DDL neither directly nor indirectly.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.
 >> Stay informed about: Modeling question... 

"David Cressey" <cressey73.RemoveThis@verizon.net> writes:

> "Volker Hetzer" <firstname.lastname.RemoveThis@ieee.org> wrote in message
> news:g6cmjq$9s9$1@nntp.fujitsu-siemens.com...
>> Bob Badour schrieb:
>> >>> Ooooh! Reinventing EAV with levels...
>> >>
>> >> Possibly. I had a look at
>> >> http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm and didn't find
>> >> 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.
>>
>
> Here's the problem with the customer requirement that "we want to be able to
> create and delete attributes": the same customers nearly always want to be
> able to derive value from the data in the database by using it with standard
> queries to drive standard reports, charts, or extracts that can be fed to
> another process. After all, that's inherent in database data, isn't it?
> Well, not if the customers can each invent their own attributes, it isn't!
>
> If you've never been down this road before I suggest you stay in the same
> job long enough to see what happens when you tell the customers that each of
> them has to design their own queries, because each of them defined their own
> attributes. What happens isn't pretty!

I would have to agree very strongly here with David's warning. I also
suspect that the customer's 'requirements' may actually represent a
misunderstanding on their part or at least poorly expressed
requirements. I would strongly recommend digging deeper and finding out
more about why they feel this requirement is necessary. It reminds me of
many clients I have had who bring me a solution to implement rather than
bringing me the requirements and letting me come up with a solution in
conjunction with them. This is a frequent and difficult problem faced by
developers. You need to dig deeper and understand why they believe they
need the ability to add/drop attributes.

regards,

Tim


--
tcross (at) rapttech dot com dot au
 >> Stay informed about: Modeling question... 

Tim X wrote:

> You need to dig deeper and understand why they believe they
> need the ability to add/drop attributes.

It wouldn't having anything to do with engaging ill-informed "experts"
to advise them in the first place. That just couldn't be it.

--
Roy
 >> Stay informed about: Modeling question... 

Roy Hann <specially RemoveThis @processed.almost.meat> writes:

> Tim X wrote:
>
>> You need to dig deeper and understand why they believe they
>> need the ability to add/drop attributes.
>
> It wouldn't having anything to do with engaging ill-informed "experts"
> to advise them in the first place. That just couldn't be it.

Well, couldn't tell without seeing how many zeros are on the rhs of the
hourly fee they charge! Alternatively, you need to listen to one of
their presentations and have a "bullshit bingo" card in front of
you. Thats always an entertaining way to assess the "expert consultants"
brought in to help define some important controls to reduce the
unmitigated risk levels to an acceptable residual level while
ensuring key business requirements are met in a timely manner and the
solution is extensible, with minimal maintenance requirements and at a
total cost of ownership that is significantly less than any alternative
vendor solution while maintaining business best practice and
ensuring......

see http://turing.une.edu.au/~norm/bsbingo.cgi


--
tcross (at) rapttech dot com dot au
 >> Stay informed about: 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.
 >> Stay informed about: Modeling question...