Normalization of Many to Many relation in sql

Question

I have a product table that contains two column

ProductID   Desc
  1         Fan
  2         Table
  3         Bulb

I have another table that contains supplier information

SupplierID    Desc
   1          ABC
   2          XYZ
   3          HJK

Now One supplier can supply multiple products and one product can be supplied by multiple suppliers To achieve this I have created another table tbl_Supplier_Product

 SupplierID    ProductID 
     1            1
     1            2
     2            1
     2            2
     2            3

Is it a good way to link this table to supplier and product table through primary composite key. In this table the primary key would be a composite key (SupplierID and ProductID) or should I add an extra column row ID for each record and then use that as a primary key and add a unique constraint to columns SupplierID and ProductID

 SupplierID    ProductID    Row ID
         1            1       1
         1            2       2
         2            1       3
         2            2       4
         2            3       5
UNIQUE CONSTRAINT(SupplierID, ProductID)

What would the relationship of this table be to supplier table? I am a bit confused here because I added this table to resolve many to many relationship and redundant data but it still seems like this table has many to many relationship with both the tables??

score 4 · Accepted Answer · edited May 23 '17 at 11:51

4

You don't need the extra column: a composite key is all you need

I would create a unique index that is the reverse of the PK too: this is useful for many queries and also provides an FK index for ProductID

After comment:

CREATE TABLE SupplierProduct (
    SupplierID int NOT NULL,
    ProductID int NOT NULL,

    PRIMARY KEY (SupplierID, ProductID)
);
GO
CREATE UNIQUE NONCLUSTERED INDEX IXU_ReversePK ON SupplierProduct (ProductID, SupplierID);
GO

For more

SQL: Do you need an auto-incremental primary key for Many-Many tables? for more (see comments)
Difference between 2 indexes with columns defined in reverse order

And also use this generally to ensure that all your FKs have indexes

SELECT  fk.name AS [Missing FK Index]
FROM    sys.foreign_keys fk
WHERE   EXISTS
        (
        SELECT  *
        FROM    sys.foreign_key_columns fkc
        WHERE   fkc.constraint_object_id = fk.object_id
                AND NOT EXISTS
                (
                SELECT  *
                FROM    sys.index_columns ic
                WHERE   ic.object_id = fkc.parent_object_id
                        AND ic.column_id = fkc.parent_column_id
                        AND ic.index_column_id = fkc.constraint_column_id
                )
        );
GO

In an ERD (random one from a PowerPoint I have):

enter image description here

edited May 23 '17 at 11:51

Community

1
1

answered Apr 25 '13 at 08:53

gbn

422,506
82
585
676

1

can you please elaborate on " would create a unique index that is the REVERSE of the PK too: this is useful for many queries and also provides an FK index for ProductID" – Abhi.Net Apr 25 '13 at 08:55
Thanks for the explanation but what would the relation of this table be to supplier and product table if shown in an ER diagram. from what I think it will be many to many. – Abhi.Net Apr 25 '13 at 09:01
One last thing, from this page http://stackoverflow.com/questions/13499096/composite-primary-key-sql-relationship if you have a look at the answer it says "If you have a composite primary key, then all foreign keys that reference it MUST use all columns of the composite primary key" Does tha mean I'll have to add productID to Supplier table and SupplierId to product table. – Abhi.Net Apr 25 '13 at 09:14
Not in your case. @marc_s is referring to a simple child table that refers to a single parent, and the single parent has a composite key. It is not a many-many case like your table. – gbn Apr 25 '13 at 09:20

North Gork · Answer 2 · 2021-08-19T11:43:58.837

You can use composite keys but these days it is more conventional to use surrogate keys. A surrogate is a primary key that is usually a unique GUID that has no meaning to anyone except for the purpose of uniquely identifying a table row. A surrogate is only used internally by the software so normally users never see it and do not need to see it.

You should also use the naming convention for primary keys where id is the primary key (this should be a surrogate key) and all foreign keys are table.id. The table part tells you it is a foreign key to that table. So:

The product-supplier table is what some call a "link table" or a "join table". It is their to NORMALIZE the many to many relationship. It turns a many to many relationship into a one to many and we all live happily ever after. So many products can have many suppliers and many suppliers can have many products is normalized (resolved) by the product-supplier join table where a product has one to many product-supplier rows and a supplier has one to many product-supplier rows.

Note that the join table could be called supplier-product or product-supplier, it does not matter which way you name it as long as it is named after the parent tables with a hyphen in between, this is also a naming convention.

The point here is that you always resolve a many to many relationship by normalizing it into two one-to-many relations as shown here.

Normalization of Many to Many relation in sql

2 Answers2

Linked