Unanswered: Need Help Understanding Concept: Many-To-Many Relationships
I don't know if this may go better in a general area of the forum, if so mods are free to move this. However I'm learning Access and how to program VBA, I'm only a week or so in and I'm trying to understand the three relationship types. I understand One-To-Many, and One-To-One, however how is Many-To-Many not just multiple One-To-One relationships?
- It has several customers. Each customer is registered into a table that holds several pieces of information (Name, address, etc.): Tbl_CustomerData. There is only one row for each customer.
- This shop sells several items. Information about each item is also stored into a table (Brand, size, description, etc.): Tbl_ItemData. Here again, there is only one row for each kind of item.
- One customer can buy several different kinds of items. You can define this with a One (Customer i.e. one row in the table Tbl_CustomerData) to Many (items i.e. several rows in the table Tbl_ItemData).
This means that there is one column in the table Tbl_ItemData that contains a foreign key to the table Tbl_CustomerData.
However, one kind of item can be bought by several different customers, while we saw previously that you can store one (and only one) foreign key to Tbl_CustomerData in each row (= each item) of the table Tbl_ItemData.
The solution consists in creating a third table (often called a Junction table) that will contain a column holding a foreing key to the table Tbl_CustomerData and a second column that will contain a foreign key to the table Tbl_ItemData. Each pair of keys (= each row) in this table represents an item bought by a customer.
Customer C1 buys Items I1, I2, I3. and Customer C2 buys Items I2, I4. In the junction table, we have: