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 do it the way you describe. Commented Aug 31, 2014 at 11:03

1 Answer 1

  1. 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.

  2. 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)
  • I have a separate Publishers table for that role (publisher is typically an organization while the creators and contributors of the book are typically people). But good to know I'm on the right track. Thanks :)
    – some user
    Commented Aug 30, 2014 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.