Re: creating a foreign key referencing a non-primary key

"Asya Kamsky" <asya_at_bayarea.net> wrote in message news:105ht6j9f17lda9_at_corp.supernews.com...
| In article <4058d8eb$0$20658$afc38c87_at_news.optusnet.com.au>,
| Howard J. Rogers <hjr_at_dizwell.com> wrote:
| >Well, there's always the possibility of correcting the design, of course.
I
| >mean, I can't tell exactly what is going on here in your mind, but you've
| >got two completely seaparate entitites, IDs and Versions, but one table
| >that's combined the two. You should have a separate table for IDs only;
| >another separate table for Versions only. And a third table that pulls
the
| >two of them together to show what versions are valid for which IDs.
| >For example:
| >Table 1: Oracle 8i, Oracle 9i, Oracle 10g
| >Table 2: Release 1, Release 2, Release 3, Release 4
| >Table 3: Oracle 8i Release 1, Oracle 8i Release 2, Oracle 8i Release 3,
| >Oracle 9i Release 1, Oracle 9i Release 2, Oracle 10g Release 1.
| >Just my opinion, of course.
|
| The problem with this is that there is other information that's
| related to each ID-version combination and essencially I want
| to represent two types of relationships, tight ones (where
| an entity is tightly coupled with a specific id/version combo
| and its attributes) and a loose one where it's coupled with
| the id only (and then it picks up the latest version's attributes).
|
| Perhaps this is a problem that can't be modeled simply in a
| relational database. I can (and obviously) will use triggers
| to assure existence of id, sort of as a foreign key surregate.
|
| I was hoping there'd be a more straightforward way...
|
| Thanks.
| --
| Asya Kamsky
|
| Come see my band, Monday March 29th, 11pm(sharp) @ Tongue and Groove in SF
| http://www.evite.com/asya@bayarea.net/rock

there is a more straight-forward way to do this -- i agree with howard that you do not seem to be modeling this correctly -- it appears that you might have your PF/FK relationships backwards

if the data are 'coupled' with the id, then the ID identifies the attributes (which is probably why it's called an id), and should be the primary key of the table where those attributes appear

if other data are 'coupled' with the id/version, then you've got version-specific attributes in a dependent table

however, you also mention that you have data that is related to the latest version -- if that is really so, then if the attributes are single-valued they belong in a table with the id/version pk, and if they are multi-valued, then they belong in a table that is dependent on the id/version table, and has the combination of id/version as a FK -- but i'm guessing it's not really true, that you are actually attributes (or detail rows) related just the ID, but want to view it in the context of the latest version. here's a test -- if an id has 3 version, are you also keeping these types of attributes or details for, not just the latest version, but the prior two versions?

please don't take offense, but, based on your description, i think you may need to review basic data modeling and normalization -- there should be no need for triggers in the simple (and common) scenario that you are describing

;-{ mcs Received on Fri Mar 19 2004 - 19:02:10 CST