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




--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Hi Andreas,

Maybe it is a solution to create a unique ID for each location. A new
table TTreeLocations with LocationID, LocationType (Street,
Park, ...), ObjectID (this being the ID of street, park etc.) would be
a way to do this.

Best,

Hans

Hi Andreas,

Maybe it is a solution to create a unique ID for each location. A new
table TTreeLocations with LocationID, LocationType (Street,
Park, ...), ObjectID (this being the ID of street, park etc.) would be
a way to do this.

Best,

Hans

Hi Andreas,

Maybe it is a solution to create a unique ID for each location. A new
table TTreeLocations with LocationID, LocationType (Street,
Park, ...), ObjectID (this being the ID of street, park etc.) would be
a way to do this.

Best,

Hans

Hi Andreas,

Maybe it is a solution to create a unique ID for each location. A new
table TTreeLocations with LocationID, LocationType (Street,
Park, ...), ObjectID (this being the ID of street, park etc.) would be
a way to do this.

Best,

Hans

Hans Mayr schrieb am 28.03.2008 in
<02275d11-1d19-4260-b782-fe648b10b689 (AT) e39g2000hsf (DOT) googlegroups.com>:

Hi Hans,

Soory, but I can not find the difference. If I understand correct you
suggest to create a table TTreeLocations with a primary key LocationID
and an ObjectID that now has to refer different tables, street, park ...

It is no problem to use 1 sequence for all referred tables street, park
and so on, but how can I write a constraint, that ensures, that my
ObjectID is to be found in (exactly) one of these tables?

Thank you
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Hans Mayr schrieb am 28.03.2008 in
<02275d11-1d19-4260-b782-fe648b10b689 (AT) e39g2000hsf (DOT) googlegroups.com>:

Hi Hans,

Soory, but I can not find the difference. If I understand correct you
suggest to create a table TTreeLocations with a primary key LocationID
and an ObjectID that now has to refer different tables, street, park ...

It is no problem to use 1 sequence for all referred tables street, park
and so on, but how can I write a constraint, that ensures, that my
ObjectID is to be found in (exactly) one of these tables?

Thank you
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Hans Mayr schrieb am 28.03.2008 in
<02275d11-1d19-4260-b782-fe648b10b689 (AT) e39g2000hsf (DOT) googlegroups.com>:

Hi Hans,

Soory, but I can not find the difference. If I understand correct you
suggest to create a table TTreeLocations with a primary key LocationID
and an ObjectID that now has to refer different tables, street, park ...

It is no problem to use 1 sequence for all referred tables street, park
and so on, but how can I write a constraint, that ensures, that my
ObjectID is to be found in (exactly) one of these tables?

Thank you
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Hans Mayr schrieb am 28.03.2008 in
<02275d11-1d19-4260-b782-fe648b10b689 (AT) e39g2000hsf (DOT) googlegroups.com>:

Hi Hans,

Soory, but I can not find the difference. If I understand correct you
suggest to create a table TTreeLocations with a primary key LocationID
and an ObjectID that now has to refer different tables, street, park ...

It is no problem to use 1 sequence for all referred tables street, park
and so on, but how can I write a constraint, that ensures, that my
ObjectID is to be found in (exactly) one of these tables?

Thank you
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Hi Andreas,

you have a foreign key constraint from TTrees on TTreeLocations and a
foreign key constraint from each TStreet, TPark etc. on
TTreelocations. The tuple LocationType and Object ID is unique in
TTreeLocation. That should make it safe.

Best,

Hans