Wednesday, May 14, 2014

Many-to-many Relationship

Today, at work, our team were designing a screen for collecting some data. I don't think I explained very well how would we implement a many-to-many relationship between 2 entities. I believe I don't understand a subject well enough until I can help other understand how it works. The motivation for this post is helping me solidify my knowledge and help other people.

Example: We have a database that holds information about books. A book can have one or many authors, and an author can write one or many books.
Let's take a look at the design.



Notice that the middle table is the bridge table. The bridge table has 2 foreign keys that reference to the Books and Authors table. Basically, we establish an one-to-many relationship between the Books and the bridge table, and an one-to-many relationship between the Authors and the bridge table. Below is the SQL code for create these 3 tables.

CREATE TABLE dbo.Books (
   BookId INT NOT NULL PRIMARY KEY,
   BookTitle VARCHAR(50) NOT NULL
);
GO

CREATE TABLE dbo.Authors (
   AuthorId  INT NOT NULL PRIMARY KEY,
   LastName  VARCHAR(30) NOT NULL,
   FirstName VARCHAR(30) NOT NULL
);
GO

--bridge table. 
CREATE TABLE dbo.BooksAuthors (
   BookId INT NOT NULL FOREIGN KEY REFERENCES dbo.Books(BookId),
   AuthorId INT NOT NULL FOREIGN KEY REFERENCES dbo.Authors(AuthorId),
   CONSTRAINT BooksAuthors_PK PRIMARY KEY(BookId, AuthorId)
);
GO

--generate some sample data
INSERT INTO dbo.Books (BookId, BookTitle)
VALUES (1, 'Be Happy'),
       (2, 'Smiles'),
       (3, 'NoSQL 101');
GO

INSERT INTO dbo.Authors (AuthorId, LastName, FirstName)
VALUES (1, 'Preston', 'Ton' ),
       (2, 'Phuong' , 'Tran'),
       (3, 'Jocelyn', 'Lui' )
GO

-- Let assume:
-- book with title "Be Happy" was written by Preston Ton and Phuong Tran.
-- book with title "Smiles" was written by Phuong Tran and Jocelyn Lui.
-- book with title "NoSQL 101" was written by Jocelyn Lui.
-- Here is the sample data for the bridge table:
INSERT INTO dbo.BooksAuthors (BookId, AuthorId)
VALUES (1, 1),
       (1, 2),
       (2, 2),
       (2, 3),
       (3, 3);
GO

--A query to show the all books with all authors. 
SELECT b.BookId  , b.BookTitle
      ,a.AuthorId, a.LastName + ', ' + a.FirstName AS AuthorName 
FROM dbo.BooksAuthors AS ba
JOIN dbo.Authors AS a ON ba.AuthorId = a.AuthorId
JOIN dbo.Books   AS b ON ba.BookId   = b.BookId;












One final note: for the bridge table, we can add some other columns if needed.

No comments:

Post a Comment