Re: Modeling question...

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 08 Jul 2008 15:07:01 -0300
Message-ID: <4873acc6$0$4052$9a566e8b@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... Received on Tue Jul 08 2008 - 13:07:01 CDT

Original text of this message