1

Let's say for example that I have to organize this type of information: books, books editions, colors of every page, reactions. Books can have one or many editions.

I can't decide if it's best to do:

  • Books [bookid, author]
  • Editions [editionid, bookid, title, publisher, isbn, release_date]
  • PagesColors [editionid, page, color]
  • Reactions [userid, editionid, reaction]

or removing editions table and using one table with bookid_parent to create them:

  • Books [bookid, bookid_parent, author, title, publisher, isbn, release_date]
  • PageColors [bookid, page, color]
  • Reactions [userid, bookid, reaction]

or:

  • Books [bookid, bookid_parent, author, release_date]
  • BooksDetails [bookid, title, publisher, isbn, ..other details without index]
  • PagesColors [bookid, page, color]
  • Reactions [userid, bookid, reaction]

I usually select the first one, but the second and the third would save me a lot of PHP coding in the real project. (I'm the only developer). The only difference between the last two are the records:

1, 0, "Jane Austen", null, null, null, 2018-01-01
2, 1, null, "Emma", "Books Enterprise", 12231231213, 2018-02-02
3, 1, null, "Emma in Italian", "Jane Austen Italian Editions", 45345354334, 2018-03-03

(lots of null) or:

[Books]
1, 0, "Jane Austen", 2018-01-01
2, 1, null, 2018-02-02
3, 1, null, 2018-03-03

[BooksDetails]
2, "Emma", "Books Enterprise", 12231231213
3, "Emma in Italian", "Jane Austen Italian Editions", 45345354334
  • The different solutions are for data with different meanings: for instance in one case the color of the page depends on the book and in other depends on the edition. You should first decide which data depend on other data (this process is called finding the functional dependencies of the data, and it is a prerequisite to normalization which is essential to the design of a good database schema. – Renzo Jun 30 '18 at 9:56
  • Thank you Renzo, the color of the page in the first case depends on the edition because the design would be "books -> (one to many relation) -> editions". In the other cases the color depends on book because "there aren't editions any more" (that's why I would save a lot of coding), in those cases an edition is a subbook linked by "bookid_parent" like "Emma" and "Emma in Italian". The meaning is the same, it's only about simplify with one table for books and editions or two. – silentheaven Jun 30 '18 at 10:16
  • Ok, the first case is better from a normalization point of view (and so to maintain the consistency of the data). If you want to simplify the program you could define views and use them in your program (for instance a view which is a join of books and editions). – Renzo Jun 30 '18 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.