If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
Which Foreign Key in which table?
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 |
#13
|
|||
|
|||
Which Foreign Key in which table?
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. |
#14
|
|||
|
|||
Which Foreign Key in which table?
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 |
#15
|
|||
|
|||
Which Foreign Key in which table?
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. |
#16
|
|||
|
|||
Which Foreign Key in which table?
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 |
#17
|
|||
|
|||
Which Foreign Key in which table?
"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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |