Database Design – Many-to-Many Relationship with Attributes

database-designsqlite

I'm working on a project where I need a book database. The problem is as follows:

A book can be related to multiple people (authors, editors, traslators, etc.). Then a person may contribute to different books in different roles.

Book1 PersonA (author), PersonB(editor)
Book2 PersonC (author), PersonB(translator), PersonD(editor)
Book3 PersonE (author), PersonA(editor)

I have a BOOKS table (bookID, bookTitle), a PEOPLE table (personID, personName) and a ROLES table (roleID, roleName). Then I need a way to relate these tables and then query them. I thought of a junction table that has the following row structure:

bookId personID roleID

Is this the optimal way of handling this problem (when it comes to insertion, update and deletion of entries on all these tables)? If not, what are the alternatives?

Note: I'm using SQLite3.

PS: I hope my use of the terminology is correct.

Best Answer

I would use The Party Model, so that you can associate a book with an Individual or Organization, not just an Individual. For example, you might want Random House in the role of Publisher. Or Mötley Crüe as the author of the book, not just Tommy Lee.
Otherwise, looks good. You might want to consider associating the book and the PartyRole, instead of Party and Role separately, as you might have extra info pertaining to a Party in a particular Role. Perhaps author pseudonyms.

Postgres-y SQL, but close enough to SQLite:

create table parties (
  party_id int primary key,
  party_type smallint not null references party_types(party_type_id), --1=individual,2=org
  name text not null
);

create table party_roles (
  party_role_id int primary key,
  party_id int not null references parties(party_id),
  role_id smallint not null references roles(role_id),
  ...
  extra information about a party pertaining to a role
  ...
);

create table book_roles (
  book_id int references books(book_id),
  party_role_id int references party_roles(party_role_id),

  primary key (book_id, party_role_id)
);

Related Solutions

Entity relationship many to many

It's not strictly necessary to have an association class to model a M:M relationship. You can just have classes with references to the two parents that would map directly to a link entity. Semantically, an association class would limit you to one relationship between two entities.

A short blurb on association classes can be found here.

Many-to-Too-Many Relationship

If there can be more than one author / editor / translator per book - like it is in real life and like your relational design suggests, then the existing answer with plain LEFT JOINs will produce incorrect results. If any book could have at most one person for each of the roles, you could radically simplify your design: no junction tables needed, just a foreign key column in the table Books.

You need to aggregate authors, translators and editors. You could do that after joining all rows (and producing mulitple resulting rows), but it should be more efficient to aggregate in the subqueries and then join to the Books table

SELECT B.BookTitle, BA.Authors, BT.Translators, BE.Editors
FROM   tblBook B
LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Authors
    FROM    tblBookAuthorJunction J
    JOIN    tblPerson P ON J.AuthorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BA ON BA.BookID = B.ID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Translators
    FROM    tblBookTranslatorJunction J
    JOIN    tblPerson P ON J.TranslatorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BT ON BT.BookID = B.ID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Editors
    FROM    tblBookEditorJunction J
    JOIN    tblPerson P ON J.EditorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BE ON BE.BookID = B.ID
WHERE  B.ID = 123

In most RDBMS it will be faster if you add WHERE BookID = 123 to the subqueries, too.

Best Answer

Related Solutions

Entity relationship many to many

Many-to-Too-Many Relationship

Related Question