Re: Modeling question...
From: Volker Hetzer <firstname.lastname_at_ieee.org>
Date: Tue, 08 Jul 2008 21:36:51 +0200
Message-ID: <g50fkj$j5i$1_at_nntp.fujitsu-siemens.com>
>> 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.
Date: Tue, 08 Jul 2008 21:36:51 +0200
Message-ID: <g50fkj$j5i$1_at_nntp.fujitsu-siemens.com>
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.Received on Tue Jul 08 2008 - 21:36:51 CEST