Ms SQL Creating Many-To-Many Relation with a Junction Table

MS SQL creating many-to-many relation with a junction table

I would use the second junction table:

MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID

The primary key would be the combination of both columns. You would also have a foreign key from each column to the Movie and Category table.

The junction table would look similar to this:

create table movie_category_junction
(
  movie_id int,
  category_id int,
  CONSTRAINT movie_cat_pk PRIMARY KEY (movie_id, category_id),
  CONSTRAINT FK_movie 
      FOREIGN KEY (movie_id) REFERENCES movie (movie_id),
  CONSTRAINT FK_category 
      FOREIGN KEY (category_id) REFERENCES category (category_id)
);

See SQL Fiddle with Demo.

Using these two fields as the PRIMARY KEY will prevent duplicate movie/category combinations from being added to the table.

How to construct a Junction Table for Many-to-Many relationship without breaking Normal Form

Your structure allows duplicate data. For example, it allows data like this. (UUIDs abbreviated to prevent horizontal scrolling.)

Connection_id            Company_id               Owner_id
--
b56f5dc4...af5762ad2f86  4d34cd58...a4a529eefd65  3737dd70...a359346a13b3
0778038c...ad9525bd6099  4d34cd58...a4a529eefd65  3737dd70...a359346a13b3
8632c51e...1876f6d2ebd7  4d34cd58...a4a529eefd65  3737dd70...a359346a13b3

Each row in a relation should have a distinct meaning. This table allows millions of rows that mean the same thing.

Something along these lines is better. It's in 5NF.

CREATE TABLE CompanyOwners(
    Company_ID UUID NOT NULL references Company (Company_ID),
    Owner_ID UUID NOT NULL references Owner (Owner_ID),
    PRIMARY KEY (Company_ID, Owner_ID)
);

Standard SQL doesn't allow "-" in identifiers.

Junction table indexing in many-to-many relationship

No, you do not need to create two additional keys. Your primary key index will work for the first key (product_id) as well as the pair.

You need one additional index on order_id.

Can a junction table (join table) also be used for a one-to-many relationship?

Yes, it is still possible to store and enforce one-to-many relationship in a junction table.

In your example you are not enforcing any constraints on the UserOrders junction table, so a single order can belong to two users (assuming that's incorrect). To enforce that you could make OrderKey be the primary key of the UserOrders junction table (or have a unique constraint on that column). Technically that will just become a many-to-one relationship between UserOrders and Users, while having one-to-one relationship between Orders and UserOrders.

I can only think about one reason for designing the many-to-one relationship using junction table - if you plan to allow the many-to-many relationship in future and don't want to deal with data migration. But in the mean time you will pay the cost of storing and joining with additional table.

How do I model a many-to-many relation in SQL Server?

The traditional way is to use an additional many:many (junction) table, which links to both tables:

CREATE TABLE [dbo].[AuthorsBooks] (
    -- Optionally, we can give the table its own surrogate PK
    [Id]      INT IDENTITY(1,1) NOT NULL,
    AuthorId INT NOT NULL,
    BookId INT NOT NULL,

    -- Referential Integrity
    FOREIGN KEY(AuthorId) REFERENCES Authors(Id),
    FOREIGN KEY(BookId) REFERENCES Books(Id),

    -- PK is either the surrogate ...
    PRIMARY KEY CLUSTERED ([Id] ASC)
    -- ... Or the compound key
    -- PRIMARY KEY CLUSTERED (AuthorId, BookId)
);

One moot point is whether you want the compound key AuthorId, BookId to be the Primary Key, or whether to add your own new Surrogate - this is usually a subjective preference.

Some of the points to consider whether going for a compound primary key or a new surrogate key for the Junction table:

• Without the surrogate, external tables linking to the junction table would need to store both compound keys (i.e. would need to retain both AuthorId and BookId as foreign keys).
• So a new surrogate offers the potential benefit of a narrower primary key, which then means any tables linking to this junction table will have a single, narrower foreign key.
• However, with the compound keys, there can be an optimisation benefit that tables can join directly to the underlying Books or Authors tables without first joining to the junction table.

The following diagram hopefully makes the case of the compound key clearer (the middle table Nationality is a junction table of PersonCountry):

Edit

Usage is straightforward - if the link exists in the many:many table, then the relationship is deemed to exist. To test the existence, you 'join through' the link table e.g.

-- Find all books written by AuthorId 1234
SELECT b.* 
  FROM Books b 
  INNER JOIN AuthorsBooks ab
     ON b.Id = ab.BookId
  WHERE ab.AuthorId = 1234;

SQL join on junction table with many to many relation

I like to start from the outside and move in.
So imagine all the columns were all jammed together in just one table, you could write something like:

SELECT *
FROM products
WHERE shop_id = 3

You then just need to add the joins to make this statement possible. We know we need to add the join table next (as it's the one that joins directly onto the products table due to it having the product_id in it). So that join is what goes next:

SELECT products.*
FROM products
INNER JOIN shops_products
ON products.product_id = shops_products.product_id
WHERE shops_products.shop_id = 3

and actually you can stop right here... because shop_id exists on the join table already. But lets say you also wanted the shop's location in the set of final columns, you'd then add the shop-table join.

SELECT products.*, shops.shop_location
FROM products
INNER JOIN shops_products
ON products.product_id = shops_products.product_id
INNER JOIN shops
ON shops_products.shop_id = shops.shop_id
WHERE shops_products.shop_id = 3

Sql many to many relation with the same table

You should use a junction table where each row contains details of a "relationship" between two users.

|UserID1|UserID2|
|      1|      2|
|      2|      3|

---

Related Topics