Many to many conundrum

[agibsonsw]

I tend to think of a many-to-many relationship in the following terms:
'A' could interact with B many times, and B could interact with A many times. But this can fall down:
A salesperson could contact many customers, and a customer could speak to many salespeople. However, the same salesperson could speak to the same customer many times.
What is the secret to recognising a many-to-many relationship? Andy.

[HansV]

You describe a many-to-many relationship between salespersons and customers. In a relational database, this is implemented by a junction table in which each record is a unique combination of a salesperson and a customer.
Meetings between a salesperson and a customer can be stored in a "child" table of this junction table.

x41.png

(This is not from a real database, it's just a picture)

[ChrisGreaves]

What is the secret to recognising a many-to-many relationship? Andy.

Transaction Processing.
Probably has a fancy name by now.
You already knew the answer when you noted that: "the same salesperson could speak to the same customer many times".
Think of a(any) salesperson talking to a(any) customer as a transaction-to-be-recorded (or an "event"), and record that transaction as the basic unit of data.
That basic unit will have obvious data such as date, time, place, dollar-amount (zero for a chat, non-zero for a real sale or refund).
Now whatever you want to know, especially in statistical or reporting terms, can be found by querying the transaction table, or its derivatives.

[agibsonsw]

You describe a many-to-many relationship between salespersons and customers. In a relational database, this is implemented by a junction table in which each record is a unique combination of a salesperson and a customer.
Meetings between a salesperson and a customer can be stored in a "child" table of this junction table.

In a lot of examples I've seen the junction table contains two foreign keys which are set as a (joint) primary key. The example you've shown has an additional (primary)
key. What determines the need for a separate primary key? Does it follow from my initial question? The need for further links to reflect the "real-world"? Andy.

[HansV]

You can use the combination of SalesPersonID and CustomerID as primary key for the junction table. You then have to place both fields in tblMeetings too, instead of CombinationID. This will work just as well. It's a matter of taste whether you prefer to use the composite key, or create a new ID field to act as primary key.

[agibsonsw]

I appreciate that both methods would work, but isn't one method preferable?
Perhaps if no additional transactions (as suggested by Chris) were anticipated, the combined key would suffice, but if further transactions (tables) were anticipated, the
creation of a separate primary would assist future development? Andy.

[HansV]

If you have many such relationships in a database, giving each table a single AutoNumber primary key makes it easier to manage, otherwise you might end up with tables having a primary key consisting of five or six fields.

[agibsonsw]

Thank you. That makes a lot of sense. Andy.