Re: Modeling question...
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 09 Jul 2008 09:14:23 -0300
Message-ID: <4874aba0$0$4069$9a566e8b_at_news.aliant.net>
>> 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...
Date: Wed, 09 Jul 2008 09:14:23 -0300
Message-ID: <4874aba0$0$4069$9a566e8b_at_news.aliant.net>
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. Received on Wed Jul 09 2008 - 14:14:23 CEST