Primary and Foreign Keys
in [Tables]
|
Prev: Tab Order for Multiple Entry Form. Vertical not horizontal tabbin
Next: marvendas@gmail.com Kit completo deSolenóides ( solenoid ) + chicote Para Câmbio automatico hidramatico Audi A3 Vw Golf gti turbo 66707
From: Kathy R. on 18 Feb 2010 16:27 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!)
From: Steve on 18 Feb 2010 17:10 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 santus(a)penn.com "Kathy R." <wild_rose(a)graffiti.net> wrote in message news:%gifn.730$Ye4.176(a)newsfe11.iad... > 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!)
From: Kathy R. on 18 Feb 2010 17:21 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 > santus(a)penn.com > > > > "Kathy R." <wild_rose(a)graffiti.net> wrote in message > news:%gifn.730$Ye4.176(a)newsfe11.iad... >> 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!) > >
From: John W. Vinson on 18 Feb 2010 18:00 On Thu, 18 Feb 2010 16:27:58 -0500, "Kathy R." <wild_rose(a)graffiti.net> 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]
From: Steve on 18 Feb 2010 19:01
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 santus(a)penn.com "Kathy R." <geneseoumc(a)frontiernet.net> wrote in message news:C3jfn.914$qJ.500(a)newsfe10.iad... > 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 >> santus(a)penn.com >> >> >> >> "Kathy R." <wild_rose(a)graffiti.net> wrote in message >> news:%gifn.730$Ye4.176(a)newsfe11.iad... >>> 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!) >> |