Jim Dettman
jimdettman at verizon.net
Thu Mar 3 09:02:40 CST 2011
Stuart, <<No, it's many to many. Paint Chemical ... Satin White Titanium Dioxide Satin White Water ... Satin Yellow Titaniun Dioxide Satin Yellow Water >> I certainly would not model it that way. I have always done that as a one to many BOM structure: tblBOM BOMID - Autonumber - PK AssemblyID - Long - FK to tblItems - CK1-A LineNumber - Long - CK1-b ComponetID - Long - FK to tblItems QPA - Decimal EffectiveDate - DT IneffectiveDate - DT and build up a where used index on the fly based on effective/ineffective dates. <<I'm talking about the created date of the relationbship, not the objects. For example: Within the Royal Papua Yacht Club we have a number "sub Clubs" which members can join . (Scuba, Deep Sea Fishing, Sailing, Canoe Racing) . I may decide to join the Deep Sea Fishing Club at some point in the future. That join date needs to stored at the link, not in my master record. Members MembPK MembNo MembName membJoinDate ' Joined RPYC CLubs ClubPK ClubName MemberClubs ClubFK MemberFK JoinDate 'Joined the sub-Club>> I understand your point and you are correct. You could add the date (along with a bunch of other attributes) to the linking table and at that point it would no longer be a "simple" linking table. However that does not change my original point: the need to add an auto number PK simply because "I always do it that way" is not required. The pairing of ClubFK and MemberFK work fine as a primary key. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Wednesday, March 02, 2011 05:41 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access and SQL Server Comments inline. -- Stuart On 2 Mar 2011 at 17:12, Jim Dettman wrote: > Stuart, > > <<When does a "many to many linking table" cease to be a "simply a > many to many linking table" >> > > It never does as your modeling a join and not some "thing" like a > customer, book, or author. > You're not modelling a join - a join is just a programming construct. You are modelling a real world relationship which can have attributes. > << How about when you are linking chemicals and formulas for a product > formula and have to store a quantity as well?>> > > That's a BOM (Bill of Materials) type structure and a one to many. No, it's many to many. Paint Chemical ... Satin White Titanium Dioxide Satin White Water ... Satin Yellow Titaniun Dioxide Satin Yellow Water ... > <<What about if you subsequently need to store info such as the start > date of the link relationship.>> > > You never would, but if you did it would look like this: > I wish I had a buck for every time I've seen "..never would" subseuqnetly happened. > tblBooksAndAuthors > AuthorID - Long - FK to tblAuthors - PK-A > BookID - Long - FK to tblBooks - PK-B > CreatedDT - D/T > > But that doesn't make any sense as a many to many record is added at > the > same time as adding a record on one of the sides represented by the > table. > > For example, when entering a book, a user would be forced to select > a > "written by" and the linking record to the author would be added at > that point. You can't have a book without an author. > > So you would include a CreatedDT on tblBooks rather then on the > linking > table. One automatically implies the other. > I'm talking about the created date of the relationbship, not the objects. For example: Within the Royal Papua Yacht Club we have a number "sub Clubs" which members can join . (Scuba, Deep Sea Fishing, Sailing, Canoe Racing) . I may decide to join the Deep Sea Fishing Club at some point in the future. That join date needs to stored at the link, not in my master record. Members MembPK MembNo MembName membJoinDate ' Joined RPYC CLubs ClubPK ClubName MemberClubs ClubFK MemberFK JoinDate 'Joined the sub-Club -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com