Re: ForeignKey-Constraint with more than 1 tables
Date: Sat, 29 Mar 2008 12:51:27 +0100
Message-ID: <47ee2d46$0$14359$e4fe514c@news.xs4all.nl>
"Ed Prochak" <edprochak_at_gmail.com> schreef in bericht news:dad7fd84-e4ed-4a30-995f-8bc475d6e5b2_at_c19g2000prf.googlegroups.com...
> On Mar 28, 5:04 am, Andreas Mosmann <mosm..._at_expires-31-03-2008.news- > group.org> wrote:
>> Hi ng,
>>
>> (Oracle 9.2.0.7i)
>>
>> I talk about a database concerning real Trees.
>> There is a table TTrees.
>>
>> A single Tree belongs either to a forrest or to a street or to a park or
>> whatelse.
>>
>> So there exist f.e. a table TStreets and a table TForrests.
>> in the table TTrees actually exist a field for each Table, f.e.
>>
>> CIDFORREST
>> CIDSTREET
>>
>> Exactly 1 of them is not NULL, all the others are definitely NULL.
>>
>> Now my Questions:
>> Is there a way to substitute this by only 1 Field, f.e. CID_BELONGS_TO
>> _AND_ to build a FK-constraint like
>>
>> TForrests.CID=TTrees.CID_BELONGS_TO OR
>> TStreets.CID=TTrees.CID_BELONGS_TO ...
>> ?
>>
>> Is there a possibility to build a constraint, that observes that _exact_
>> 1 of the fields (CIDFORREST, CIDSTREET ..) is not null? (I know, I could
>> use triggers for it)?
Actually, this is a typical 'ARC' situation in ERD modelling. ARC's can be resolved in (I think at least 3) different standard ways; using only one column in a referencing table is (as far as I know) not one of them. If you need one column for special reasons (e.g. reporting) it could be calculated by an NVL over the three columns.
One of the standard options is to create a 'supertype' for STREETS, FORREST, WHATELSE and put the common fields in one table, plus an indicator column for the type of location, and the different fields (columns) in separate child tables. It looks a bit the same as your solution, but you move the problem away from your TREES table (which ca have a single column foreign key to the supertype now, or two if you take an indicator column as well to trees) to the place where your actual problem lies: in the possible Location types.
The other options are:
- Creating three different tables for locations, resulting in your multiple
foreign key column problem
- Putting all locations in one big table, with common columns AND typical
columns for each type of location (moving your multi fk column problem to a
sparsely filled table)
>>
>> Is there a better way to build this database? (The Columns of TTrees are
>> 95% equal, no matter if it stands on a street or in a forrest, so there
>> is only 1 table)
>>
Right. That is because a tree is a tree, your problem (as stated above) is in the location, not in trees itself.
>> Many thanks
>> Andreas Mosmann
>>
>> --
>> wenn email, dann AndreasMosmann <bei> web <punkt> de
>
> I am curious. What advantage do you see in this single column > solution? What data model rule are you trying to enforce here? >
One advantage could be (i'm not defending this solution though) that the number of possible location types is not dictating the number of foreign key columns. Maybe the'whatever' type is later split into 'swamp', 'hill' , 'whatever', forcing new foreign keys into the trees table.
> I see disadvantages. e.g. find all trees that belong to forests now > requires a table join where the two column model did not.
This could be solved by an extra 'indicator column', but thus giving an extra column as well.....
> > If you are truly focussed on this solution, a trigger can enforce the > rule. > Ed
Shakespeare Received on Sat Mar 29 2008 - 06:51:27 CDT