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

Question

I need to introduce a many-to-many relationship between two tables, which both have an integer for primary key, in a SQL Server database. How is this best done in T-SQL?

Consider the following two example table definitions for which there should be a many-to-many relationship:

CREATE TABLE [dbo].[Authors] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_Versions] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Books] (
    [Id]      INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Answer 1

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;