[AccessD] Access and SQL Server

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.

<< 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.

<<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:

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.

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 03:47 PM
To: Jim Dettman; Access Developers discussion and problem solving
Subject: Re: [AccessD] Access and SQL Server

When does a "many to many linking table" cease to be a "simply a many to
many linking 
table"    How about when you are linking chemicals and formulas for a
product formula and 
have to store a quantity as well?

What about if you subsequently need to store info such as the start date of
the link 
relationship.

Do you then add a PK?

-- 
Stuart

On 2 Mar 2011 at 8:04, Jim Dettman wrote:

> table.  As this is simply a many to many linking table, no other
> fields would ever be added to it.
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com