Bookstore Database Schema
Tags: database, db, schema, dbschema
Published: 2022-11-23 16:42:36

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

![Bookstore Schema](https://dbmodeller.net/blog/assets/bookstore-full.png)

`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` &mdash; Title of the book
- `ISBN_13` &mdash; 13 digit ISBN number of the book
- `publication_date` &mdash; Date of publication
- `page_count` &mdash; Number of pages in the book
- `edition_number` &mdash; 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](https://dbmodeller.net/blog/assets/bookstore-books.png)

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](https://dbmodeller.net/blog/assets/bookstore-genres-publishers-languages.png)

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](https://dbmodeller.net/blog/assets/bookstore-authors.png)

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](https://dbmodeller.net/blog/assets/bookstore-orders.png)

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

![Customers](https://dbmodeller.net/blog/assets/bookstore-customers.png)

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](https://dbmodeller.net/blog/schema/2022/10/09/movie-catalog-database-schema.html) schema tutorial.

We came to the end of our post. You can open this sample movie catalog schema in [dbmodeller](https://dbmodeller.net/) and use it in your own projects. You can click **View Samples** button on the welcome page in [dbmodeller.net](https://dbmodeller.net/) to checkout other samples.

[Open Schema in DbModeller](https://dbmodeller.net/app/?open_sample=13)

Thank you for reading 👋👋