In this post we will design a schema to be used in a bookstore software or an online store selling books.
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.
Next, we have
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
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
authors will be many-to-many. We have an
author_books join-table for the needed relation.
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 have the following fields:
line_pricea field for the books price in this order. We could have a
current_pricefield 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.
amountfield holds the count of a specific book in an order.
We have the following generic tables related to customers, which are not specifically related to bookstores.
Those are the basic tables that we would need to design a bookstore schema. If we were developing an online store we could add
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.
Thank you for reading 👋👋