Access Many to Many relationship
in [Tables]
|
Prev: Relationship Help
Next: Budget Table
From: MJ on 28 Dec 2009 22:27 Hi John I setup the subform as you suggested. However, if I can't use lookups or combo boxes, how do I get the data into the field? Do I have to manually enter the data. This seems wrong as errors will occur. Many thanks for your help...progressing along. MJ "David W. Fenton" wrote: > "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in > news:#OemKF2hKHA.5608(a)TK2MSFTNGP05.phx.gbl: > > > In your 3rd table, why have you used the combination of EmployeeID > > + ProjectID as primary key? Are you trying to insist that no > > employee can ever be involved in more than one project? Since the > > primary key must be unique, that will be the effect. > > > > For a many-to-many relation, you would normally have fields like > > this in the 3rd table: > > EmployeeProjectID AutoNumber primary key > > EmployeeID Number relates to Employee > > table ProjectID Number relates to > > Project table JoinDate Date/Time date > > this employee joined this > > proj. > > LeaveDate Date/Time date this emp. left > > this proj. > > I don't understand your recommendation, Allen. A join table needs to > have the composite key on the foreign keys being joined, as the > original poster described. That is, unique composite key on > EmployeeID + ProjectID. That doesn't restrict the employee to a > single project, it just limits the join table to one record per > employee project. That makes sense, as what value would there be to > have the same project joined to the employee twice? > > Now, if an employee can join and leave a project multiple times, > then it seems to me that belongs in a different table. If, on the > other hand, the employee joins and leaves the project only once, > then those attributes are part of the employee/project record. > > The Autonumber surrogate key you've added serves no useful purpose > when the business rule is to allow only one instance of each project > per employee. but if you are linking a table of project dates to > this join record, then the surrogate key becomes very useful. In > that case, the surrogate Autonumber would be the PK, with a unique > composite key on the EmployeeID + ProjectID. > > Do you disagree? > > -- > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ > . >
From: David W. Fenton on 29 Dec 2009 11:50 =?Utf-8?B?TUo=?= <MJ(a)discussions.microsoft.com> wrote in news:E55211B6-3772-4247-904A-5D55A6A78C93(a)microsoft.com: > I setup the subform as you suggested. However, if I can't use > lookups or combo boxes, how do I get the data into the field? Do I > have to manually enter the data. This seems wrong as errors will > occur. Many thanks for your help...progressing along. No one said not to use combo boxes *on the form* -- it's only in TABLE DESIGN that you should never use lookups. -- 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 13:13 On Mon, 28 Dec 2009 19:27:01 -0800, MJ <MJ(a)discussions.microsoft.com> wrote: >Hi John >I setup the subform as you suggested. However, if I can't use lookups or >combo boxes, how do I get the data into the field? Do I have to manually >enter the data. This seems wrong as errors will occur. Sorry for being so dogmatic. I absolutely agree with David - you should use Combo Boxes on your Forms whenever it's appropriate (which will be very common). It's just the Lookup Wizard putting combo boxes into Tables, where they do NOT belong, that is objectionable. It is *not* necessary to use the Lookup Field feature in order to create a combo on a form (though I'll admit it makes it a little bit easier, though not enough to outweigh the disadvantages of the table lookup). -- John W. Vinson [MVP]
From: Douglas J. Steele on 29 Dec 2009 15:32 "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. 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... -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please)
From: Armen Stein on 29 Dec 2009 15:59
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 |