[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