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@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 - 07:14:23 CDT

Original text of this message