Re: ForeignKey-Constraint with more than 1 tables

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sat, 29 Mar 2008 13:40:31 +0100
Message-ID: <60ef4$47ee38bf$524b5c40$3839@cache6.tilbu1.nb.home.nl>

Shakespeare wrote:

> "Frank van Bortel" <frank.van.bortel_at_gmail.com> schreef in bericht 
> news:baf50$47ed42e1$524b5c40$3239_at_cache6.tilbu1.nb.home.nl...
>> Andreas Mosmann 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)?
>>>
>>> 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)
>>>
>>> Many thanks
>>> Andreas Mosmann
>>>
>>>
>>>
>>>

>> The "standard" solution would be to introduce a type column in
>> your trees table. That would indicate what type the relation is;
>> park, street, forest or whatelse. Giving 4 options, a CHAR(1)
>> NOT NULL column would suffice.
>> A simple check constraint (col value in ('P','F','W','S')) would
>> cover that.
>>
> 
> I think you should find the solution where the problem is: it is not in the 
> trees table, but in the location types. Normally one would model a supertype 
> for locations, keeping the problem away from the trees table or other 
> possible referncing tables.
> 
> <Snip>
> 

Well, you already indicated this is a typical ARC problem. Designer used to allow 4 resolution methods: - single table (or supertype) implementation - Every sub type its own table (explicit subtyping);

   supertype attributes are replicated in every table. - supertype and subtype have their respective tables;

   supertype attributes exist in subtype table (as above),    but also in supertype table (implicit subtyping). - super and subtypes have their tables, but no replication

   takes place; foreign keys are used: the ARC implementation.

Arc implementations require joins when assembling data - you may not want this (large forests?)

-- 

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

Received on Sat Mar 29 2008 - 07:40:31 CDT