Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    24

    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?
    Last edited by nim6us; 11-14-11 at 11:15.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Imagine a shop.

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

    Example:

    Customer C1 buys Items I1, I2, I3. and Customer C2 buys Items I2, I4. In the junction table, we have:
    Code:
    FK_Cust   FK_Item
    -------------------
       C1        I1
       C1        I2
       C1        I3
       C2        I2
       C2        I4
    Note: Very often, the primary key of the junction table is a composite key that combined both foreign keys.

    See for instance:
    Understanding a SQL Junction Table Coding Notes
    Access Junction Tables
    Database Design - Many-to-many
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •