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

In article <Se2dndNN5PO2BcbdRVn-ig_at_comcast.com>, Mark C. Stock <mcstockX_at_Xenquery .com> wrote:
>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

I think that it's my explaination that's lacking.

>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

I don't have a clean separation of such data. I have a set of attributes associated with id/version combination. Any of them can change from version to version. Any of them can stay the same from version to version.

I suppose for strict normalization I should have a separate table for each attribute and then a relationship to indicate which is which in relation to ids and versions. That doesn't seem practical however.

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

Yes, I'm keeping them for all three versions. I may have other entities associated with older versions, so I have to keep them (as the id/version is a foreign key in those other entities) but I also have entities associated with the latest version.

>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

I don't take offense, I very well may not have described the problem in a way that was clear.

If there is no need for triggers in the simple and common scenario that I have, I hope you are able to help me past the blind spot I'm having trying to model this -- all solutions I can come up with don't seem very elegant to me.

--
Asya Kamsky

Come see my band, Monday March 29th, 11pm(sharp) @ Tongue and Groove in SF
http://www.evite.com/asya@bayarea.net/rock