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: David Portas on 19 Feb 2010 02:34 "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. > 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
From: Stop$teve on 19 Feb 2010 07:46 "Steve" <notmyemail(a)address.com> schreef in bericht news:%23olnuaPsKHA.3800(a)TK2MSFTNGP06.phx.gbl... > 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
From: BruceM via AccessMonster.com on 19 Feb 2010 11:19 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 >santus(a)penn.com > >> 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/Forums.aspx/access-tablesdbdesign/201002/1
From: oldblindpew on 19 Feb 2010 12:03 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!) > . >
From: Kathy R. on 19 Feb 2010 21:13
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. |