Designing tables layout for storing books, editions, colors

Let's say for example that I have to organize this information: books, books editions and colors of every page.

Let's assume that a book can have only one edition, and since the application is written in PHP, every table will have its set of functions to read and write on them, and I'd like to keep it simple if possible. I can't decide if it's best to do:

[Books]
bookid
author

[Editions]
editionid
bookid
title
publisher
isbn

[PagesColors]
editionid
page
color


or trying to simplify in:

[Books]
bookid
bookidreference
author
title
publisher
isbn

[PagesColors]
bookid
page
color


and records like this:

1, 0, "Jane Austen", null, null, null
2, 1, null, "Emma", "Books Enterprise", 12231231213
3, 1, null, "Emma in Italian", "Jane Austen Italian Editions", 45345354334


"bookidreference" would be used to link books and editions in the same table.

For more thorough critique, provide SHOW CREATE TABLE for each table.
– Rick James
2 days ago

SHOW CREATE TABLE

Hi Rick James. I can't provide the SHOW CREATE TABLE because there aren't any tables right now, I'm just trying to understand what it's best to do before actually creating them.
– silentheaven
20 hours ago

But you will have to write them eventually. Another exercise that is important, even at this early stage, is to try to write the SELECT statements. Doing this will sometimes point out that the TABLEs were not properly designed.
– Rick James
15 hours ago

SELECT

TABLEs

3 Answers
3

I think simplified version is better as it is very concise and easy to understand.... But try to avoid null entries in database..

I don't know what you want to use in "editions.bookreference" but the first one seems more consistent.
Can the title of a book change with a new edition and does the ISDN change as well? All values, which can't change between editions should stay in your "books". You should also add a column of the release date. It may not be required now but it probably will (if your task is not a school project).

Besides that, it looks good. Maybe move authors to a table as well and use a relation table between books and authors, but the concept is solid.

Thanks DataVader. editions.bookreference would be used to "link" books and editions in the same table. In the example the second and third row (which are two editions of the same book) have that field with value "1" so they would be linked to the first row that is the book itself.
– silentheaven
Jun 29 at 10:17

You could use a reference pointing to the same table to avoid creating a table but I would only do that if I don't have any other choice or I when know for sure the avoided table would only contain one column. A column referencing to it's own table is very uncommon, so other developers may be confused what you are doing. It may save you a lot of work doing this, but only in very rare cases where you have strange constrains and/or triggers
– DataVader
Jun 29 at 11:15

Yes, it would save me a lot of coding. I'm not concerned about other developers because I'm the only one working on this project. I'm a little worried about the future (disk space occupied), because it could be: [Books] 100 entries [Editions] 1000 entries or [Books] 1100 entries with a lot of null values.
– silentheaven
Jun 29 at 11:45

[Books] 100 entries [Editions] 1000 entries

[Books] 1100 entries

I have come to this idea:

[Books]
bookid
bookid_parent
author
release_date

[BooksDetails]
bookid
title
publisher
isbn
...other details without index.

[PagesColors]
bookid
page
color


In this case I won't have as many NULL as the second solution. The records would be like:

[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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.