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 |
#1
|
|||
|
|||
Primary and Foreign Keys
It's the end of the day and I'm second-guessing myself and need some
clarification regarding Primary and Secondary Keys and which tables they should be in. In a one-to-many relationship, the primary key is on the "one" side and the foreign key is on the "many" side, right? I.e. an Individual can have many phone numbers. tblIndividual IndID (PK) FirstName LastName tblPhone PhoneID (PK) PhIndID (FK) phonenumber phonetype What about in a one-to-one relationship? Does it really matter which the foreign key goes in? Is there an acceptable/preferred practice? I have a membership table where one of the fields is the manner in which the individual joined. Since there's a limited number of ways to join, but they can and do change over the years, I created a lookup table for that bit of information. Currently (early stages of development) I have the foreign key in the membership table. Since the membership table is what I would consider the "main" table, should the primary key go into the "child" table? tblMembership MembID (PK) JoinDate JoinOfficiant MJoinMannerID (FK) lkpJoinManner JoinMannerID (PK) JoinMannerCode JoinManner Thanks for your help! Kathy R. (whose brain is currently mush - time for a break!) |
#3
|
|||
|
|||
Primary and Foreign Keys
Thanks Steve for the reply, but you didn't answer my questions about the
one-to-one relationships What about in a one-to-one relationship? Does it really matter which the foreign key goes in? Is there an acceptable/preferred practice? And I would add, why is one way better than another (assuming I could do it either way)? Kathy R. Steve wrote: You get a gold star today! 100%. Your statements are correct and your examples are correct. One way to think about PK/FK is by way of a TblIndividual. Social Security Number could be the PK in TblIndividual because supposedly SSN is unique for each person. Then in all tables of data pertaining to information about the individual, SSN could be the FK to relate a record in that table to a specific individual in TblIndividual. Steve "Kathy R." wrote in message ... It's the end of the day and I'm second-guessing myself and need some clarification regarding Primary and Secondary Keys and which tables they should be in. In a one-to-many relationship, the primary key is on the "one" side and the foreign key is on the "many" side, right? I.e. an Individual can have many phone numbers. tblIndividual IndID (PK) FirstName LastName tblPhone PhoneID (PK) PhIndID (FK) phonenumber phonetype What about in a one-to-one relationship? Does it really matter which the foreign key goes in? Is there an acceptable/preferred practice? I have a membership table where one of the fields is the manner in which the individual joined. Since there's a limited number of ways to join, but they can and do change over the years, I created a lookup table for that bit of information. Currently (early stages of development) I have the foreign key in the membership table. Since the membership table is what I would consider the "main" table, should the primary key go into the "child" table? tblMembership MembID (PK) JoinDate JoinOfficiant MJoinMannerID (FK) lkpJoinManner JoinMannerID (PK) JoinMannerCode JoinManner Thanks for your help! Kathy R. (whose brain is currently mush - time for a break!) |
#4
|
|||
|
|||
Primary and Foreign Keys
On Thu, 18 Feb 2010 16:27:58 -0500, "Kathy R." wrote:
What about in a one-to-one relationship? Does it really matter which the foreign key goes in? Is there an acceptable/preferred practice? Yes, it matters. There is still a directionality: one table is the "parent" and the other is the "child", even if you are enforcing a One Child Policy. One to one relationships are quite rare; the most usual case for them is "Subclassing", in which you have a number of fields which only apply to a subset of the records. For instance, you might have a membership table in which some members are Officers, and there are a lot of fields which are relevant only for Officers; you could have a one to one relationship from Members (on the MemberID primary key) as the parent to Officers (joining on the MemberID, which serves as both the primary AND foreign key in this case). I have a membership table where one of the fields is the manner in which the individual joined. Since there's a limited number of ways to join, but they can and do change over the years, I created a lookup table for that bit of information. Currently (early stages of development) I have the foreign key in the membership table. Since the membership table is what I would consider the "main" table, should the primary key go into the "child" table? tblMembership MembID (PK) JoinDate JoinOfficiant MJoinMannerID (FK) lkpJoinManner JoinMannerID (PK) JoinMannerCode JoinManner Exactly correct. This is a perfectly normal one to many "lookup table" scenario. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Primary and Foreign Keys
See John's response. If you still have questions, post back and I will help.
BTW, I provide help with Access, Excel and Word applications for a modest fee. One thing I have done frequently is help with the design of the tables at the initial stage of the project. Along with design I provide documentation of the tables. I have a proprietary way of doing it. If you need help designing the tables in your database or just want someone to review what you have, I can do that for you. If you want my help, contact me. Steve "Kathy R." wrote in message ... Thanks Steve for the reply, but you didn't answer my questions about the one-to-one relationships What about in a one-to-one relationship? Does it really matter which the foreign key goes in? Is there an acceptable/preferred practice? And I would add, why is one way better than another (assuming I could do it either way)? Kathy R. Steve wrote: You get a gold star today! 100%. Your statements are correct and your examples are correct. One way to think about PK/FK is by way of a TblIndividual. Social Security Number could be the PK in TblIndividual because supposedly SSN is unique for each person. Then in all tables of data pertaining to information about the individual, SSN could be the FK to relate a record in that table to a specific individual in TblIndividual. Steve "Kathy R." wrote in message ... It's the end of the day and I'm second-guessing myself and need some clarification regarding Primary and Secondary Keys and which tables they should be in. In a one-to-many relationship, the primary key is on the "one" side and the foreign key is on the "many" side, right? I.e. an Individual can have many phone numbers. tblIndividual IndID (PK) FirstName LastName tblPhone PhoneID (PK) PhIndID (FK) phonenumber phonetype What about in a one-to-one relationship? Does it really matter which the foreign key goes in? Is there an acceptable/preferred practice? I have a membership table where one of the fields is the manner in which the individual joined. Since there's a limited number of ways to join, but they can and do change over the years, I created a lookup table for that bit of information. Currently (early stages of development) I have the foreign key in the membership table. Since the membership table is what I would consider the "main" table, should the primary key go into the "child" table? tblMembership MembID (PK) JoinDate JoinOfficiant MJoinMannerID (FK) lkpJoinManner JoinMannerID (PK) JoinMannerCode JoinManner Thanks for your help! Kathy R. (whose brain is currently mush - time for a break!) |
#6
|
|||
|
|||
Primary and Foreign Keys
"Kathy R." wrote in message
... Thanks Steve for the reply, but you didn't answer my questions about the one-to-one relationships What about in a one-to-one relationship? Does it really matter which the foreign key goes in? Is there an acceptable/preferred practice? And I would add, why is one way better than another (assuming I could do it either way)? Kathy R. 1-1 is just a special case of a 1-N relationship where N is greater than 0 and is mandatory. In most SQL DBMSs it is usually impossible to enforce such constraints because SQL foreign keys are always optional on one side. So for example you can require an Item to have a parent Order but requiring an Order to contain at least one Item is much harder. The compromise workaround is generally to either allow optionality or to enforce the rule through procedural code, for example using a DEFERRABLE constraint.This means the constraint is temporarily disabled and violated during updates. Mandatory 1-N relationships are quite common business requirements and are common in conceptual data models so it is actually a serious deficiency that the SQL model of data can't easily implement them. -- David Portas |
#7
|
|||
|
|||
Primary and Foreign Keys
"Steve" schreef in bericht ... See John's response. If you still have questions, post back and I will help. BTW, I provide help with Access, Excel and Word applications for a modest fee. One thing I have done frequently is help with the design of the tables at the initial stage of the project. Along with design I provide documentation of the tables. I have a proprietary way of doing it. If you need help designing the tables in your database or just want someone to review what you have, I can do that for you. If you want my help, contact me. -- Get lost $teve. Go away... far away.... Again... Get lost $teve. Go away... far away.... No-one wants you here... no-one needs you here... This newsgroup is meant for FREE help.. No-one wants you here... no-one needs you here... OP look at http://home.tiscali.nl/arracom/whoissteve.html (Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!) Arno R |
#8
|
|||
|
|||
Primary and Foreign Keys
They have a rhetorical question in my locale: "Are you completely numb?"
What part do you not understand about the concept of a volunteer newsgroup? I have spoken out againt pre-emptive attacks when you have not yet advertised in a thread, but I am among those keeping an eye on you, and will call you out when warranted. This is such a situation. You are out of line again. Your inability to understand the concept of a volunteer newsgroup speaks poorly of your capacity to grasp the complexities and subtleties of Access. Besides, did you really expect to ingratiate yourself with the patronizing remark about the gold star, especially when you did not get around to (or could not) answer the question? Steve wrote: See John's response. If you still have questions, post back and I will help. BTW, I provide help with Access, Excel and Word applications for a modest fee. One thing I have done frequently is help with the design of the tables at the initial stage of the project. Along with design I provide documentation of the tables. I have a proprietary way of doing it. If you need help designing the tables in your database or just want someone to review what you have, I can do that for you. If you want my help, contact me. Steve Thanks Steve for the reply, but you didn't answer my questions about the one-to-one relationships [quoted text clipped - 61 lines] Kathy R. (whose brain is currently mush - time for a break!) -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
#9
|
|||
|
|||
Primary and Foreign Keys
Isn't your JoinManner table just for lookup purposes, and therefore has a
one-to-many relationship with the Membership table? I'm no expert, but for lookup tables, I haven't bothered with autonumber keys, but just use a natural key, some sort of reasonable code, e.g. "CA" for "California". I've just recently been trying the one-to-one approach for additional fields not applicable to all records in the main table. The directionality is established in the Edit Relationships window (Database Tools, Relationships, right click on the relationship line). The main table is on the left and the related table or sub-table is on the right. In my case, a delete from the sub-table was causing an unexpected delete of the main record (see recent thread in Database Design named "Unexpected Delete"). I eventually gave up, at least for now, and put the additional fields in the main table. Hope this helps, OldBlindPew "Kathy R." wrote: It's the end of the day and I'm second-guessing myself and need some clarification regarding Primary and Secondary Keys and which tables they should be in. In a one-to-many relationship, the primary key is on the "one" side and the foreign key is on the "many" side, right? I.e. an Individual can have many phone numbers. tblIndividual IndID (PK) FirstName LastName tblPhone PhoneID (PK) PhIndID (FK) phonenumber phonetype What about in a one-to-one relationship? Does it really matter which the foreign key goes in? Is there an acceptable/preferred practice? I have a membership table where one of the fields is the manner in which the individual joined. Since there's a limited number of ways to join, but they can and do change over the years, I created a lookup table for that bit of information. Currently (early stages of development) I have the foreign key in the membership table. Since the membership table is what I would consider the "main" table, should the primary key go into the "child" table? tblMembership MembID (PK) JoinDate JoinOfficiant MJoinMannerID (FK) lkpJoinManner JoinMannerID (PK) JoinMannerCode JoinManner Thanks for your help! Kathy R. (whose brain is currently mush - time for a break!) . |
#10
|
|||
|
|||
Primary and Foreign Keys
Thank you all for your replies. As I was driving home last night, that
I was thinking in only one direction - a member has only one type of join manner. And then I had a "duh!!" moment, turned it around and realized that the join manner has many members. I appreciate the information on the 1-to-1 relationships. It seems like the more I learn the more I realize I've got a long, long way to go. And Steve, thanks for the offer, but, should I ever decide to pay for a professional, it will most certainly be one that understands the concept of FREE help on these forums. Kathy R. John W. Vinson wrote: On Thu, 18 Feb 2010 16:27:58 -0500, "Kathy R." wrote: What about in a one-to-one relationship? Does it really matter which the foreign key goes in? Is there an acceptable/preferred practice? Yes, it matters. There is still a directionality: one table is the "parent" and the other is the "child", even if you are enforcing a One Child Policy. One to one relationships are quite rare; the most usual case for them is "Subclassing", in which you have a number of fields which only apply to a subset of the records. For instance, you might have a membership table in which some members are Officers, and there are a lot of fields which are relevant only for Officers; you could have a one to one relationship from Members (on the MemberID primary key) as the parent to Officers (joining on the MemberID, which serves as both the primary AND foreign key in this case). I have a membership table where one of the fields is the manner in which the individual joined. Since there's a limited number of ways to join, but they can and do change over the years, I created a lookup table for that bit of information. Currently (early stages of development) I have the foreign key in the membership table. Since the membership table is what I would consider the "main" table, should the primary key go into the "child" table? tblMembership MembID (PK) JoinDate JoinOfficiant MJoinMannerID (FK) lkpJoinManner JoinMannerID (PK) JoinMannerCode JoinManner Exactly correct. This is a perfectly normal one to many "lookup table" scenario. |
|
Thread Tools | |
Display Modes | |
|
|