Many-to-many relationship with attributes 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.
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)
);