Access Many to Many relationship
in [Tables]
|
Prev: Relationship Help
Next: Budget Table
From: MJ on 29 Dec 2009 17:05 Thanks all for you input. Point taken and noted on the issues with primary key autonumber for join tables along with composite keys. However, my initial question was since Allen categorically stated not to use lookup tables how can the user input data listed in another table eg manually? That can't be correct due to data input error. Any suggestions on making the form work better? MJ "Armen Stein" wrote: > On Tue, 29 Dec 2009 15:32:24 -0500, "Douglas J. Steele" > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > > >While I'm a proponent of natural keys, you could use an Autonumber PK as > >long as you also had a unique index defined on the composite of the two > >foreign keys. > > I'm not. In our shop we use surrogate Autonumber/Identity keys almost > exclusively. As Doug says, we enforce uniqueness using indexes. All > of our relationships are simple one-field joins, no matter how far > down a hierarchy, which can be very handy in a database of hundreds of > tables. In the case of this many-to-many table, it's hard to know > whether you'll need a child table in the future. If you just use a > surrogate, you won't need to modify the structure in the future - just > add the child table. > > Another reason for surrogates is that auto-generating middle tier > objects is simpler with consistent integer surrogate key fields. > Access front-ends don't benefit, but we have some projects that have > (or may soon have) a web application front-end too. > > At the end of the day, it's a style preference. Either way will work, > each with pros and cons. > > Armen Stein > Microsoft Access MVP > www.JStreetTech.com > > . >
From: Steve on 29 Dec 2009 18:43 For data entry, create a form/subform. Base the main form on TblProject and base the subform on TblProjectEmployee. Set the LinkMaster and LinkChild properties to ProjectID. You will be able to enter projects in the main form, go to previously entered projects in the main form and enter employees on the project in the subform. Steve santus(a)penn.com "MJ" <MJ(a)discussions.microsoft.com> wrote in message news:AA4DFB82-5ACA-48CC-A094-21BFCCC4AB1A(a)microsoft.com... > Thanks all for you input. Point taken and noted on the issues with primary > key autonumber for join tables along with composite keys. However, my > initial > question was since Allen categorically stated not to use lookup tables how > can the user input data listed in another table eg manually? That can't be > correct due to data input error. > Any suggestions on making the form work better? > MJ > > "Armen Stein" wrote: > >> On Tue, 29 Dec 2009 15:32:24 -0500, "Douglas J. Steele" >> <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: >> >> >While I'm a proponent of natural keys, you could use an Autonumber PK as >> >long as you also had a unique index defined on the composite of the two >> >foreign keys. >> >> I'm not. In our shop we use surrogate Autonumber/Identity keys almost >> exclusively. As Doug says, we enforce uniqueness using indexes. All >> of our relationships are simple one-field joins, no matter how far >> down a hierarchy, which can be very handy in a database of hundreds of >> tables. In the case of this many-to-many table, it's hard to know >> whether you'll need a child table in the future. If you just use a >> surrogate, you won't need to modify the structure in the future - just >> add the child table. >> >> Another reason for surrogates is that auto-generating middle tier >> objects is simpler with consistent integer surrogate key fields. >> Access front-ends don't benefit, but we have some projects that have >> (or may soon have) a web application front-end too. >> >> At the end of the day, it's a style preference. Either way will work, >> each with pros and cons. >> >> Armen Stein >> Microsoft Access MVP >> www.JStreetTech.com >> >> . >>
From: David W. Fenton on 29 Dec 2009 19:32 "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote in news:OE01IYMiKHA.1648(a)TK2MSFTNGP05.phx.gbl: > "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message > news:Xns9CEFA7CB653ECf99a49ed1d0c49c5bbb2(a)74.209.136.100... >> =?Utf-8?B?TUo=?= <MJ(a)discussions.microsoft.com> wrote in >> news:3468ED0A-D353-49C2-959B-D4B5B7A7CB4E(a)microsoft.com: >> >>> tblEmployee_Project >>> Employee_ProjectID - Autonumber >>> fkEmployeeID - Number >>> fkProjectID - Number >> >> I disagree with Allen's recommendation to add an Autonumber field >> here. The proper PK is the composite of the two foreign keys. The >> only scenario in which an additional Autonumber would be useful >> is if this join table is involved in a relationship with a child >> table, e.g., if you recorded in a separate table the dates in >> which someone was assigned to a project. Absent a relationship to >> another table, there is no utility at all to the Autonumber field >> in your join table. > > While I'm a proponent of natural keys, you could use an Autonumber > PK as long as you also had a unique index defined on the composite > of the two foreign keys. I don't see the value of a surrogate PK in a join table unless the join table is itself a parent table in a parent/child relationship. That's not at all an unheard-of scenario, but most join tables really only have the two foreign keys. > Unfortunately, given the direction Microsoft is insisting we move > to congruence with SharePoint, it's almost going to be mandatory > to have Autonumber PKs on each table... I think they're going to have to add composite indexes sooner or later. It's too important for data integrity. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: John W. Vinson on 29 Dec 2009 19:36 On Tue, 29 Dec 2009 14:05:01 -0800, MJ <MJ(a)discussions.microsoft.com> wrote: >However, my initial >question was since Allen categorically stated not to use lookup tables how >can the user input data listed in another table eg manually? Neither Allen nor any of us have ever said "not to use lookup tables". What we have said is "don't use the Lookup Field datatype in Table design". Lookup tables are absolutely vital. Every database I've ever developed contains lookup tables. The objection is not to "lookup tables" - it's to Microsoft's misguided decision to include Combo Boxes ("lookup fields") in Tables. Doing so is the source of great confusion and bad design, and it is *not* necessary in order to (properly!!!) use Lookups (combo boxes, listboxes) on Forms. -- John W. Vinson [MVP]
From: David W. Fenton on 29 Dec 2009 19:41
Armen Stein <ArmenStein(a)removethisgmail.com> wrote in news:mtqkj55tticthgcbcmj9adl56jcm33jmnl(a)4ax.com: > In the case of this many-to-many table, it's hard to know > whether you'll need a child table in the future. If you just use > a surrogate, you won't need to modify the structure in the future > - just add the child table. I would say child tables of join tables are pretty rare (though not at all unheard-of). I would also say that adding a surrogate key that is not the PK is not that difficult. It might seem that this would be insufficient, but RI can be enforced on any field with a unique index -- it need not be the PK (I was actually surprised to see that this was the case, as I specifically set up a test to be sure that it was doable, and as I was putting the tables together, thought for sure that RI had to have a PK on the parent side). So I'm not convinced by the argument of adding it on the front end just in case. It will require maintaining another index and an Autonumber seed, and I don't think the overhead of this (little as it may be) is worth it just to avoid the remote possibility of adding a child table later on (which I think is a pretty remote possibility for the vast majority of join tables), particularly given how easy it is to add the non-PK surrogate key. I also think I'd always choose the non-PK surrogate key with composite 2-column PK over the alternative (making the surrogate key the PK and keeping the unique index on the 2-column key), because then the surrogate key is really functioning as a literal surrogate for the actual PK, and has no purpose other than to link the join table to its child table(s). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |