Working with multiple tables: Bookstore dataset

Bookstore

In the previous section, we worked with only one dataset consisting of just one table – Superstore. But what if there are multiple tables? How to deal with them?

To get a better brief understanding of data modeling, we highly recommend reading this article from IBM.

In this section, we are going to work with the online bookstore dataset. Here is the diagram of a data model for your reference:

Here's a breakdown of each table and the relationships between them:

1. Authors Table
  • Purpose: Contains information about book authors.
  • Columns: author_id (unique identifier for each author), name, birth_year, nationality.
  • Primary Key: author_id.
  • Relationships: Each author can write multiple books. This table is related to the Books table through the author_id.
  • Location: dataacademykz.bookstore.authors
2. Books Table
  • Purpose: Holds details about the books available in the bookstore.
  • Columns: book_id (unique identifier for each book), title, author_id (links to Authors), genre, price, publish_date.
  • Primary Key: book_id.
  • Foreign Key: author_id (references Authors.author_id).
  • Relationships: Each book is written by one author. This table is related to the OrderDetails table through the book_id.
  • Location: dataacademykz.bookstore.books
3. Customers Table
  • Purpose: Stores information about customers.
  • Columns: customer_id (unique identifier for each customer), name, join_date, country, date_of_birth, gender.
  • Primary Key: customer_id.
  • Relationships: Customers can place multiple orders. This table is related to the Orders table through the customer_id.
  • Location: dataacademykz.bookstore.customers
4. Orders Table
  • Purpose: Records details of orders placed by customers.
  • Columns: order_id (unique identifier for each order), customer_id (links to Customers), order_date, total_price.
  • Primary Key: order_id.
  • Foreign Key: customer_id (references Customers.customer_id).
  • Relationships: Each order is linked to one customer but can include multiple books. This table is related to the OrderDetails table through the order_id.
  • Location: dataacademykz.bookstore.orders
5. OrderDetails Table
  • Purpose: Provides detailed information about each order, such as which books were ordered and in what quantity.
  • Columns: order_id (links to Orders), book_id (links to Books), quantity, price_per_unit.
  • Foreign Keys: order_id (references Orders.order_id), book_id (references Books.book_id).
  • Relationships: This table bridges the Orders and Books tables, indicating which books are included in each order.
  • Location: dataacademykz.bookstore.order_details