Which Foreign Key in which table?

Tim Ferguson wrote:
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

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

Vincent Johns wrote in news:nsQbf.831$c_1.434
@newsread3.news.pas.earthlink.net:

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



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

Tim Ferguson wrote:

Vincent Johns wrote in news:nsQbf.831$c_1.434
@newsread3.news.pas.earthlink.net:


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

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

Vincent Johns wrote in news:VCAcf.2277
:

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

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

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" wrote in message
...
Vincent Johns wrote in news:VCAcf.2277
:

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

"tina" wrote in
:

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