Design question

From: William B Ferguson <wbfergus_at_usgs.gov>
Date: Wed, 10 Nov 2004 07:40:29 -0700
Message-ID: <OF0DDB6D30.28F52C71-ON07256F48.004B9CA4@cr.usgs.gov>

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.

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?
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?
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

Received on Wed Nov 10 2004 - 08:36:46 CST