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

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