In this post we will design a schema to be used in a bookstore software or an online store selling books.

Bookstore Schema

books is the first table we are going to create and it will hold information about the books themselves.

Primary fields of the books table that holds direct information about a book are:

  • title — Title of the book
  • ISBN_13 — 13 digit ISBN number of the book
  • publication_date — Date of publication
  • page_count — Number of pages in the book
  • edition_number — Edition number

If we want hold the older 10 digit ISBN code for a book we could add an ISBN_10 varchar(10) field.

page_count field holds the number of pages in the book and we have a field to hold edition number. However, if we want to hold page counts in each edition separately, we might want to create a related editions table and keep edition specific information in that table.

Books Table

Next, we have genres, publishers and languages tables. These tables are connected to the books table using a many-to-one relation. If we want a book to have more than one genre, publisher or language, then we would need to create join tables and have many-to-many relations.

Genres, publishers, languages

We are going to create an authors table to represent book authors. Since a book can have multiple authors and an author can have multiple books, the relation between books and authors will be many-to-many. We have an author_books join-table for the needed relation.

Authors

We have an optional Orders and Customers part that could be useful for an online book shop.

General order information is kept in the orders table, and individual books in an order are kept in the order_items table.

Order items have the following fields:

  • line_price a field for the books price in this order. We could have a current_price field in the books for any listing or report, but since that price can change in time, we must have a constant price associated with the order.
  • amount field holds the count of a specific book in an order.

Orders

We have the following generic tables related to customers, which are not specifically related to bookstores.

Customers

Those are the basic tables that we would need to design a bookstore schema. If we were developing an online store we could add reviews, ratings and similar tables. We have used a similar structure in our movie catalogs schema tutorial.

We came to the end of our post. You can open this sample movie catalog schema in dbmodeller and use it in your own projects. You can click View Samples button on the welcome page in dbmodeller to checkout other samples.

Open Schema in DbModeller

Thank you for reading 👋👋