Which Foreign Key in which table?

[Arri]

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?

 

[Douglas J. Steele]

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.

 

[tina]

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

 

[Vincent Johns]

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.

 

[tina]

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

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.


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

one?

 

[Vincent Johns]

tina wrote:

[...]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Tim Ferguson]

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

 

[tina]

SeniorManagers (

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

LOL

 

[tina]

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.

tina wrote:

[...]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

@newsread1.news.pas.earthlink.net:






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

Yes, you can do that (unlimited number of levels) via a self-join, in
which you (for example) join the [Employees] Table to a copy of itself,
which you might call [Employees_Supervisors]. (It's a copy of a
reference to the Table; you don't actually copy any real records.)

For example, let's add a few records to your Tables. We're going to set
up 2 levels of supervisors. Incidentally, I assume you have other
references from other Tables to the records in [People], else there
would be little need to split out those fields -- they could simply be
stored in [Employees].

[People] Table Datasheet View:
PeopleID FName LName Address
----------- ----- ------ ---------------------
-1739752905 Judy Grunt 118 Drury Lane
-1724904251 Punch Peon 3352 Crazy Quilt Ave.
-506694726 Big Kahuna The Palace
1711311566 Boss Honcho The Ritz

Now we add, in [Employees], the records peculiar to an employee, such as
a reference to a supervisor:

[Employees] Table Datasheet View:
EmployeeID PeopleID StartYear Increment Employees_SupvID
Date
---------- ----------- --------- --------- ----------------
472638892 1711311566 11/7/2001 11/7/2003 761885619
761885619 -506694726 1/1/1985 1/1/1986 0
1417722657 -1724904251 11/7/2005 472638892
1930422077 -1739752905 3/3/2004 3/3/2005 472638892

Some people here on the m.p.a.t. NG dislike using lookup fields, so the
above display shows only the key values. If you avoid using lookups,
please ignore the following display. However, since I think lookup
fields are almost essential when one has to deal with key values, here's
how I would show the same Datasheet View, using lookup values:

[Employees] Table Datasheet View, with lookup fields:

EmployeeID PeopleID StartYear Increment Employees_SupvID
Date
----------- ----------- --------- --------- ----------------
472638892 Boss Honcho 11/7/2001 11/7/2003 Big Kahuna
761885619 Big Kahuna 1/1/1985 1/1/1986 0
1417722657 Punch Peon 11/7/2005 Boss Honcho
1930422077 Judy Grunt 3/3/2004 3/3/2005 Boss Honcho

Having populated our two Tables, we can now display a list of everyone's
supervisor (except the top dog), via this Query:

[Q_Supervisors] SQL:

SELECT [People]![FName] & " " & [People]![LName]
AS EmpName,
People.Address AS EmpAddr,
"Mr/Ms " & People_Supv!LName AS SupvName
FROM ((Employees INNER JOIN Employees AS Employees_Supv
ON Employees.Employees_SupvID
= Employees_Supv.EmployeeID)
INNER JOIN People AS People_Supv
ON Employees_Supv.PeopleID = People_Supv.PeopleID)
INNER JOIN People
ON Employees.PeopleID = People.PeopleID
ORDER BY People.LName, People.FName;

In Datasheet View, the results are...

[Q_Supervisors] Query Datasheet View:
EmpName EmpAddr SupvName
----------- --------------------- ------------
Judy Grunt 118 Drury Lane Mr/Ms Honcho
Boss Honcho The Ritz Mr/Ms Kahuna
Punch Peon 3352 Crazy Quilt Ave. Mr/Ms Honcho

So you have 3 levels of supervision with only 2 Tables, and you could as
easily have dozens of levels. No further changes to the Table design
(nor to the Query) would be needed to accommodate those.

Of course, if new types of fields are needed for the executive suite,
you'd need that [SeniorManagers] Table, and I assume a separate field to
link to it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Tim Ferguson]

Yes, you can do that (unlimited number of levels) via a self-join, in
which you (for example) join the [Employees] Table to a copy of itself,
which you might call [Employees_Supervisors]. (It's a copy of a
reference to the Table; you don't actually copy any real records.)

I know about self joins, but I don't think it's an example of sub-typing.

Incidentally, I assume you have other
references from other Tables to the records in [People], else there
would be little need to split out those fields

Yes: this is what is meant by subtyping. I was leaving out tables like
Customers, SalesTargets, ContactsInOtherCompanies and so on. Each of
these table would have a one-to-one relationship with the People table
similar to that described in the Employees table.

B Wishes


Tim F

 

[Vincent Johns]

@newsread3.news.pas.earthlink.net:

OK, I think I misunderstood what you were saying. Considering what I
think you are doing here, you can probably use a combination of
subtyping (for cases where you need to have additional fields for the
subtypes) and self-joins (for where you don't).

[...]

I know about self joins, but I don't think it's an example of sub-typing. > [...]
B Wishes

Tim F

That's probably true. I suspect that you're more likely to see a neat
pyramid-shaped structure like the example you gave if it's a contrived
example. In real life you might come across situations in which some
collection of fields pops up in a couple of unrelated places in your
structure. In that case, you could (if the semantics makes sense) glom
those fields into one Table, with a suitable name, and link the records
in the other Tables to it. It might not constitute sub-typing according
to your textbook, but it could still (possibly) be a good way to
organize your information.

Another thought -- to some extent, it won't hurt to leave some of the
fields in a record empty. In your example involving [Employees] and
[Senior Managers], you could combine both into one Table in which some
of the fields used for senior managers would be left empty for
employees. Whether that's a good idea in your case depends on your
circumstances, such as how many of each type of record you expect to
have and how limited your storage space is.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Tim Ferguson]

Another thought -- to some extent, it won't hurt to leave some of the
fields in a record empty.

This is true, and it is hard to apply any strict criteria about which is
better in a general situation. For me:

have a wide table if it's not too wide

have two tables if the data are very sparce (i.e. small proportion
of the records have data present

have two tables if it makes reporting easier: for example, you can
get the Senior Managers' details with their names by joining the
SeniorManagers to People without bothering to read any of the
Employee records at all. Much easier load on the server and much
easier job for the SQL coder.

As a rule, I have a fairly low threshold for creating new tables when it
looks like I can encapsulate real-life entities better.

I think we've come a bit OT for the OP...

B Wishes


Tim F

 

[Vincent Johns]

Tim Ferguson wrote:

[...]

This is true, and it is hard to apply any strict criteria about which is
better in a general situation. For me:

have a wide table if it's not too wide

have two tables if the data are very sparce (i.e. small proportion
of the records have data present

have two tables if it makes reporting easier: for example, you can
get the Senior Managers' details with their names by joining the
SeniorManagers to People without bothering to read any of the
Employee records at all. Much easier load on the server and much
easier job for the SQL coder.

As a rule, I have a fairly low threshold for creating new tables when it
looks like I can encapsulate real-life entities better.

I think we've come a bit OT for the OP...

B Wishes

Tim F

I guess I pretty much agree with everything you say, and even bad
decisions along these lines can often be rectified (at least in Access)
without a lot of work.

Looking at this thread, it appears that the OP never uttered another
word after the original question. I hope he got some value from the
responses. But I particularly enjoyed your sub-sub-typing example.
Thanks.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[tina]

this is somewhat OT too, but i just wanted to tell you, Tim, how much i've
learned from your various post on table design. self-joins, in particular,
is a concept that i struggle with visualizing (generally, if i can't see a
"map" of something in my head, i'm not getting it! <g>), but every post of
yours that i read on the subject brings it just a little bit clearer. thanks
for sharing your knowledge here in the NGs and helping all of us. tina

 

[Tim Ferguson]

self-joins, in particular,
is a concept that i struggle with visualizing (generally, if i can't
see a "map" of something in my head, i'm not getting it! <g>), but
every post of yours that i read on the subject brings it just a little
bit clearer.

<blush> Thanks for that, Tina. I think I just have a simple mind that likes
silly examples rather than abstracts.

B wishes

Tim F