Re: How to make a Foreign key in this scenario

"Sean" <sean_at_kewi.net> wrote in message
news:bc264b7c.0202081333.357ca92f_at_posting.google.com...
> Trying to make a many to many table relationship. One of
our tables
> contains files with versions (like 1, 2, 3, etc). We want
to then tie
> the access to the file based on a user's username (hence
the many to
> many table) and not a version in particular, but the file
in general.
> The caveat here is that we can not make a FK from Table 1
to Table 3
> because we're not bringing the entire Primary key from
Table 1 over.
> You'll get a "ORA-02270: no matching unique or primary key
for this
> column-list" error.
>
> Is there a way to enforce integrity in Table 3 making sure
there is a
> matching value in Table 1 ? (without the version column
of course).
>
>
> Table 1
> --------------
> FILE_ID (PK)
> VERSION (PK)
> ATTRIBUTES
>
> Table 2
> --------------
> USERNAME (PK)
> ATTRIBUTES
>
> Table 3 (the many to many)
> --------------
> FILE_ID (?????)
> USERNAME (FK)
Make another table like
Table FILE:
FILE_ID (PK)
ATTRIBUTES ?Are attributes assigned to a file in general then here else in FILE_VERSION

Table FILE_VERSION:
FILE_ID (PK)
VERSION (PK)
(ATTIBUTES only needed if some attributes differ between versions)

Table USER_FILE:
FILE_ID (PK) (FK references table FILE)
USERNAME(PK) Now, this solution can sometimes be a pain in a**. I designed like this but three levels deep in a schema and since nearly all selects relate to the newest VERSION coders have to do a lot of subselect where max(version) = ... Performence is OK and so is normalization but consider moving info about older fileversions to a history table if info about old files is rarely used.

Peter Laursen Received on Sat Feb 09 2002 - 06:55:29 CST