Design question

Okay, it's still early here and maybe the coffee hasn't fully kicked in
yet, but either way I'm feeling kinda stupid.

Here's the problem I have (and hopefully this doesn't ramble to much).

In my database of mineral sites, I have my main table DEPOSITS (and =
about
36 other data tables, plus 20 lookup tables). One of the child tables is
ROCKS.

The ROCKS table just lists the various kinds of "Host" or "Associated"
rocks at the site (whether they 'host' the ore or are 'associated' with
the waste, non-ore rocks).=20

For simplicity and accuracy of queries, there is another tables called
AGES, where all of the age information is kept, providing just one
location instead of two, to query against (this will become more =
apparent
in a bit). Sometimes, the scientists may want to find everything in a
given area that has an age of something like "Upper Triassic", =
regardless
of what kind of entity it applies to.

In the AGES table, I have a field called AGE_TP, that can have 5 =
different
values, "Associated Rock", "Associated Rock Unit", "Host Rock", "Host =
Rock
Unit" and "Mineralization".

Here's my structure for AGES (key fields):
Dep_id     number(12)
Rock_line  number(4)
Age_line   number(4)
Age_tp     varchar2(20)
Other data fields

The key fields in the ROCKS table are dep_id and rock_line, and for the
DEPOSITS table, dep_id.

Here's where I'm getting stuck. The dep_id+rock_line will give me a
foreign key to the ROCKS table. However, the ROCKS table does not =
contain
entries for 'Mineralization'. The rows in the AGES table that pertain to
'Mineralization' will be instead related to the DEPOSITS table. And of
course, there can (and usually are), multiple ages for each kind of
age_tp.

So, I'm trying to figure out the easiest (and best) way to enforce some
kind of referential constraints bewteen the DEPOSITS, ROCKS, and AGES
tables. Here's the two best options I've come up with, but I keep =
finding
faults with my logic on getting them properly enforced.

1. Add a field to the AGES table so I can have a unique key for both the
mineralization rows and the rows related to the ROCKS table. However, I
fail to see where I can come up with a primary key field unless I create
another field that doesn't relate to anything, so I'd have two =
additional
fields. I could at least have foreign key constraints, and my primary =
key
key constraint, then the two additional unique keys?

2. Create a duplicate of the AGES table solely for the mineralization
records, and then create a view with a UNION ALL joining both age tables
together. This gives me my one point to search. Then, create an INSTEAD =
OF
trigger, with the logic to determine which table to delete, insert or
update. Any thoughts on the performance issues this would raise?

3. Several other not so well thought out ideas that I discarded as
unworkable or unmanageable.

Any ideas, suggestions, or recommendations?

Thanks.

------------------------------------------------------------
Bill Ferguson
U.S. Geological Survey - Minerals Information Team
PO Box 25046, MS-750
Denver, Colorado 80225
Voice (303)236-8747 ext. 321 Fax (303)236-4208

~ Think on a grand scale, start to implement on a small scale ~
--
http://www.freelists.org/webpage/oracle-l

Other related posts: