Re: ForeignKey-Constraint with more than 1 tables
Date: Sat, 29 Mar 2008 13:40:31 +0100
> "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 126.96.36.199i)
>>> 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
>>> 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.
>>> 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 upReceived on Sat Mar 29 2008 - 07:40:31 CDT