Hello there again.

 

I have 2 tables which look like this: (shortened)

 

Person

 

id, name,...

 

Book

 

id, title,...

 

 

I need to be able to allow each book to have an Author(s), Editor(s) and/or Translator(s). For each book there could be just 1 author and no editors or translators or there could be multiple authors and multiple editors etc, or a book might not actually have an author and just some editors. Or any combination really. There will always be at least one 'contributor'.

 

At the minute I have this:

 

Authors

 

person_id, book_id

 

Editors

 

person_id, book_id

 

Translators

 

person_id, book_id

 

 

I think that this design is ok (please tell me if it isn't), but I'm not really sure how to go about dealing with the empty cases when it comes to reading the values from the database. Will this design work? Is it relatively simple dealing with the fact that at least two contributors could be empty?

 

Thanks all

The design is OK but imho can be improved

scrap those last three tables. they are all the same but with a different name. instead create a third table called contributor_type, with the structure

id, name

 

Then in that table you can have

1 Author

2 Editor

3 Translator

 

Then to link a book to a person, you need a table with the following structure (example call it person_book)

person_id, book_id, contributor_type_id

 

This gives you much more flexibility since to add a new contributor type you only need to create a new record in the contributor_type table not create an entirely new table

It also means that you only need to query one table to find out which people belong to a book

Simply using

SELECT * FROM person_book WHERE book_id = 5

will return all the people associated with the book

The design is OK but imho can be improved

scrap those last three tables. they are all the same but with a different name. instead create a third table called contributor_type, with the structure

id, name

 

Then in that table you can have

1 Author

2 Editor

3 Translator

 

Then to link a book to a person, you need a table with the following structure (example call it person_book)

person_id, book_id, contributor_type_id

 

This gives you much more flexibility since to add a new contributor type you only need to create a new record in the contributor_type table not create an entirely new table

It also means that you only need to query one table to find out which people belong to a book

Simply using

SELECT * FROM person_book WHERE book_id = 5

will return all the people associated with the book

 

Perfect! Thanks.

 

I think I need to brush up a bit on database design.

Perfect! Thanks.

 

I think I need to brush up a bit on database design.

 

I thought I would add the name of the technique as Jay has already provided the solution and it is simply Normalisation, may help to steer you in the right direction to brush up perhaps this article may help as a starting point: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html