Which Foreign Key in which table?

I can't figure out what the rules are for foreign keys in tables. For
example, M:M is easy -- the table includes the PKs of the two related tables.
In a 1:M relationship, it appears that the PK on the 1 side should be the FK
on the M side. But how about a 1:1 relationship? How do I handle that one?

1:1 relationships are rare. However, normally one of the tables (call it
"A") isn't going to have a row for every row in the other table ("B"). In
that case, the foreign key should be in table A, and will be the PK of table
B.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Arri" wrote in message
news
I can't figure out what the rules are for foreign keys in tables. For
example, M:M is easy -- the table includes the PKs of the two related
tables.
In a 1:M relationship, it appears that the PK on the 1 side should be the
FK
on the M side. But how about a 1:1 relationship? How do I handle that one?

you'll rarely have a legitimate need for a 1:1 relationship. when you do,
the primary key of the dominant table is linked to the primary key of the
subordinate table. the key value of each record in the subordinate table is
*never* generated independently, it is *always* a copy of the key value
already assigned to the matching record in the dominant table.

hth


"Arri" wrote in message
news
I can't figure out what the rules are for foreign keys in tables. For
example, M:M is easy -- the table includes the PKs of the two related
tables.
In a 1:M relationship, it appears that the PK on the 1 side should be the
FK
on the M side. But how about a 1:1 relationship? How do I handle that one?

tina wrote:

you'll rarely have a legitimate need for a 1:1 relationship. when you do,
the primary key of the dominant table is linked to the primary key of the
subordinate table.

I may be a bit too picky here, but in general the link in the
subordinate Table should be treated as a foreign key, not as the primary
key, as its main function is to identify a record in the dominant Table.
Since there is at most one subordinate record for each record in the
dominant Table in a 1:1 relationship, you could let the same field serve
as a primary key in the subordinate Table, but that would be only
incidental to its main purpose of identifying records in the other Table
(which is what foreign keys do). I think you'd want any additional
Tables to link to the dominant Table via the dominant Table's primary
key, since in the subordinate Table any given key value might not exist.
The subordinate Table might have fewer records than the dominant Table
does. The dominant Table is the only one that's guaranteed to contain a
complete list of key values.

the key value of each record in the subordinate table is
*never* generated independently, it is *always* a copy of the key value
already assigned to the matching record in the dominant table.

hth

.... and therefore, when you design the subordinate Table, that field
should not contain an Autonumber field. I usually use a Number (long
integer, same format as Autonumber) and copy the values from the other
Table, for example by using an Update Query. There are other easy ways
to do it. Typing them on the keyboard is NOT one of the easy ways.

Having copied the key values, you can call this field the primary key of
the Table, which will set an index on the field and not allow duplicate
values. But even though the field would be called a primary key in the
subordinate Table, I would still avoid linking other Tables to this
field, preferring to use the dominant Table instead for that purpose.

-- Vincent Johns
Please feel free to quote anything I say here.

"Arri" wrote in message
news
I can't figure out what the rules are for foreign keys in tables. For
example, M:M is easy -- the table includes the PKs of the two related

tables.

In a 1:M relationship, it appears that the PK on the 1 side should be the

FK

on the M side. But how about a 1:1 relationship? How do I handle that one?

Since there is at most one subordinate record for each record in the
dominant Table in a 1:1 relationship, you could let the same field serve
as a primary key in the subordinate Table

of course. and frankly, i don't see any reason to have any other field as
primary key in the subordinate table, when the dominant table's key value
already serves to uniquely identify the record. quite simply, the key field
in the subordinate table serves as both the primary key for that table AND
the foreign key from the dominant table.

I think you'd want any additional
Tables to link to the dominant Table via the dominant Table's primary
key, since in the subordinate Table any given key value might not exist.

well, that's a pretty sweeping statement. if another table in the database
has a valid relationship that is specific to the subset of data housed in
the subordinate table, then i see no problem with linking the two. in fact,
in that circumstance, i would advise *against* linking to the dominant
table.

hth


"Vincent Johns" wrote in message
k.net...
tina wrote:

you'll rarely have a legitimate need for a 1:1 relationship. when you
do,
the primary key of the dominant table is linked to the primary key of
the
subordinate table.

I may be a bit too picky here, but in general the link in the
subordinate Table should be treated as a foreign key, not as the primary
key, as its main function is to identify a record in the dominant Table.
Since there is at most one subordinate record for each record in the
dominant Table in a 1:1 relationship, you could let the same field serve
as a primary key in the subordinate Table, but that would be only
incidental to its main purpose of identifying records in the other Table
(which is what foreign keys do). I think you'd want any additional
Tables to link to the dominant Table via the dominant Table's primary
key, since in the subordinate Table any given key value might not exist.
The subordinate Table might have fewer records than the dominant Table
does. The dominant Table is the only one that's guaranteed to contain a
complete list of key values.

the key value of each record in the subordinate table is
*never* generated independently, it is *always* a copy of the key value
already assigned to the matching record in the dominant table.

hth

... and therefore, when you design the subordinate Table, that field
should not contain an Autonumber field. I usually use a Number (long
integer, same format as Autonumber) and copy the values from the other
Table, for example by using an Update Query. There are other easy ways
to do it. Typing them on the keyboard is NOT one of the easy ways.

Having copied the key values, you can call this field the primary key of
the Table, which will set an index on the field and not allow duplicate
values. But even though the field would be called a primary key in the
subordinate Table, I would still avoid linking other Tables to this
field, preferring to use the dominant Table instead for that purpose.

-- Vincent Johns
Please feel free to quote anything I say here.

"Arri" wrote in message
news
I can't figure out what the rules are for foreign keys in tables. For
example, M:M is easy -- the table includes the PKs of the two related

tables.

In a 1:M relationship, it appears that the PK on the 1 side should be
the

FK

on the M side. But how about a 1:1 relationship? How do I handle that
one?

tina wrote:

[...]
I think you'd want any additional
Tables to link to the dominant Table via the dominant Table's primary
key, since in the subordinate Table any given key value might not exist.


well, that's a pretty sweeping statement. if another table in the database
has a valid relationship that is specific to the subset of data housed in
the subordinate table, then i see no problem with linking the two. in fact,
in that circumstance, i would advise *against* linking to the dominant
table.

hth

Since you put it that way, I'd have to agree in a case like this. I was
concerned that using the same field as both a foreign key and a primary
key could be confusing, but that's probably just a result of the naming
convention I usually use -- naming a key according to the Table where
it's the primary key so that it's easy to track while reading SQL, for
example. I've never run into the situation you describe, and it
certainly does seem wasteful to create an extra primary key in the
subordinate Table, as I kind of suggested, when the foreign key that's
already there will do the job. If this came up often, I'd probably want
to set up a new naming convention for keys like this.

-- Vincent Johns
Please feel free to quote anything I say here.

Vincent Johns wrote in news:Y0Dbf.741$Id6.35
@newsread1.news.pas.earthlink.net:

I've never run into the situation you describe,



People (
*PersonID
FName
LName
Address...)

Employees (
*PersonID FK references People
DepartmentCode
StartYear
IncrementDate...)

SeniorManagers (
*PersonID FK references Employees
NumberOfForeignHomes
WifesBirthday
ExecToiletPassNumber...)


Anyone for a suggestion for a sub-sub-sub-typing solution?


All the best


Tim F

SeniorManagers (
*PersonID FK references Employees
NumberOfForeignHomes
WifesBirthday
ExecToiletPassNumber...)

LOL

i agree. it definitely makes more sense to modify a naming convention to be
compatible with a properly designed table, than to modify a table to be
compatible with a naming convention.


"Vincent Johns" wrote in message
.net...
tina wrote:

[...]
I think you'd want any additional
Tables to link to the dominant Table via the dominant Table's primary
key, since in the subordinate Table any given key value might not exist.


well, that's a pretty sweeping statement. if another table in the
database
has a valid relationship that is specific to the subset of data housed
in
the subordinate table, then i see no problem with linking the two. in
fact,
in that circumstance, i would advise *against* linking to the dominant
table.

hth

Since you put it that way, I'd have to agree in a case like this. I was
concerned that using the same field as both a foreign key and a primary
key could be confusing, but that's probably just a result of the naming
convention I usually use -- naming a key according to the Table where
it's the primary key so that it's easy to track while reading SQL, for
example. I've never run into the situation you describe, and it
certainly does seem wasteful to create an extra primary key in the
subordinate Table, as I kind of suggested, when the foreign key that's
already there will do the job. If this came up often, I'd probably want
to set up a new naming convention for keys like this.

-- Vincent Johns
Please feel free to quote anything I say here.

tina wrote:

SeniorManagers (
*PersonID FK references Employees
NumberOfForeignHomes
WifesBirthday
ExecToiletPassNumber...)

LOL

I love it... I imagine you have ideas for other fields as well, which
wouldn't be suitable for describing in any detail in a family-oriented
publication. :-)

-- Vincent Johns
Please feel free to quote anything I say here.