Online Bookstore Database Design

Complete solution with ER diagram, SQL schema, and implementation details.

Problem Statement

For an online bookstore, identify:

Main entities (tables)
Attributes for each entity (columns)
Primary keys
Relationships between entities
Cardinalities (one-to-many, many-to-many)

Entities to consider: Books, Authors, Customers, Orders, Categories, Reviews

1. Entities Identified

Core entities:

BOOKS
AUTHORS
CUSTOMERS
ORDERS
CATEGORIES
REVIEWS

Junction tables (for many-to-many relationships):

BOOKS_AUTHORS - connects books to authors
BOOKS_CATEGORIES - connects books to categories
ORDER_ITEMS - connects orders to books

2. Entity-Relationship Diagram

erDiagram CUSTOMERS ||--o{ ORDERS : "places" CUSTOMERS ||--o{ REVIEWS : "writes" BOOKS ||--o{ REVIEWS : "has" BOOKS ||--o{ ORDER_ITEMS : "appears in" BOOKS }o--o{ AUTHORS : "written by" BOOKS }o--o{ CATEGORIES : "belongs to" ORDERS ||--|{ ORDER_ITEMS : "contains" CUSTOMERS { int customer_id PK varchar email UK varchar password_hash varchar first_name varchar last_name varchar phone text shipping_address datetime created_at } BOOKS { int book_id PK varchar isbn UK varchar title text description decimal price int stock_quantity varchar publisher date publication_date } AUTHORS { int author_id PK varchar first_name varchar last_name text biography } CATEGORIES { int category_id PK varchar category_name UK text description } ORDERS { int order_id PK int customer_id FK datetime order_date decimal total_amount varchar status } REVIEWS { int review_id PK int book_id FK int customer_id FK int rating text review_text datetime review_date } BOOKS_AUTHORS { int book_id FK int author_id FK int author_order } BOOKS_CATEGORIES { int book_id FK int category_id FK } ORDER_ITEMS { int order_item_id PK int order_id FK int book_id FK int quantity decimal price_at_purchase }

3. Entity Specifications

CUSTOMERS

Primary Key: customer_id
Unique: email
Purpose: Store customer account information

BOOKS

Primary Key: book_id
Unique: isbn
Purpose: Product catalog
Note: ISBN is unique but not used as PK because books can be reissued with new ISBNs

AUTHORS

Primary Key: author_id
Purpose: Store author information separately from books

ORDERS

Primary Key: order_id
Foreign Key: customer_id references CUSTOMERS
Purpose: Track customer orders

REVIEWS

Primary Key: review_id
Foreign Keys: book_id, customer_id
Unique: (book_id, customer_id) - one review per customer per book
Purpose: Customer book reviews

BOOKS_AUTHORS (Junction Table)

Composite Primary Key: (book_id, author_id)
Additional field: author_order (for books with multiple authors)
Purpose: Many-to-many relationship between books and authors

BOOKS_CATEGORIES (Junction Table)

Composite Primary Key: (book_id, category_id)
Purpose: Many-to-many relationship between books and categories

ORDER_ITEMS (Junction Table)

Primary Key: order_item_id
Foreign Keys: order_id, book_id
Additional field: price_at_purchase (preserves historical price)
Purpose: Many-to-many relationship between orders and books, with quantity and price

4. Relationships

Relationship	Type	Implementation
Books - Authors	Many-to-Many	BOOKS_AUTHORS junction table
Books - Categories	Many-to-Many	BOOKS_CATEGORIES junction table
Customers - Orders	One-to-Many	ORDERS.customer_id FK
Orders - Books	Many-to-Many	ORDER_ITEMS junction table
Customers - Reviews	One-to-Many	REVIEWS.customer_id FK
Books - Reviews	One-to-Many	REVIEWS.book_id FK

5. SQL Schema

-- CUSTOMERS
CREATE TABLE CUSTOMERS (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    shipping_address TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- AUTHORS
CREATE TABLE AUTHORS (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    biography TEXT,
    INDEX idx_name (last_name, first_name)
) ENGINE=InnoDB;

-- CATEGORIES
CREATE TABLE CATEGORIES (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
) ENGINE=InnoDB;

-- BOOKS
CREATE TABLE BOOKS (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    publisher VARCHAR(100),
    publication_date DATE,
    CHECK (price >= 0),
    CHECK (stock_quantity >= 0),
    INDEX idx_title (title),
    INDEX idx_isbn (isbn)
) ENGINE=InnoDB;

-- BOOKS_AUTHORS
CREATE TABLE BOOKS_AUTHORS (
    book_id INT NOT NULL,
    author_id INT NOT NULL,
    author_order INT DEFAULT 1,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES BOOKS(book_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (author_id) REFERENCES AUTHORS(author_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

-- BOOKS_CATEGORIES
CREATE TABLE BOOKS_CATEGORIES (
    book_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (book_id, category_id),
    FOREIGN KEY (book_id) REFERENCES BOOKS(book_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (category_id) REFERENCES CATEGORIES(category_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ORDERS
CREATE TABLE ORDERS (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'Pending',
    CHECK (total_amount >= 0),
    CHECK (status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled')),
    FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_customer (customer_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- ORDER_ITEMS
CREATE TABLE ORDER_ITEMS (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price_at_purchase DECIMAL(10,2) NOT NULL,
    CHECK (quantity > 0),
    CHECK (price_at_purchase >= 0),
    FOREIGN KEY (order_id) REFERENCES ORDERS(order_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (book_id) REFERENCES BOOKS(book_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- REVIEWS
CREATE TABLE REVIEWS (
    review_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    customer_id INT NOT NULL,
    rating INT NOT NULL,
    review_text TEXT,
    review_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    CHECK (rating BETWEEN 1 AND 5),
    UNIQUE KEY (book_id, customer_id),
    FOREIGN KEY (book_id) REFERENCES BOOKS(book_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(customer_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

6. Design Decisions

Primary Keys

All tables use surrogate keys (auto-increment integers) rather than natural keys. This provides:

Stability - IDs don't change even if data changes
Performance - Integer joins are faster than string joins
Simplicity - Consistent key type across all tables

Historical Price Preservation

ORDER_ITEMS.price_at_purchase stores the book price at time of purchase. This is necessary because book prices may change over time, but historical orders must reflect the actual price paid.

Referential Integrity

Relationship	ON DELETE	Reason
ORDERS → CUSTOMERS	RESTRICT	Preserve order history, prevent accidental customer deletion
ORDER_ITEMS → ORDERS	CASCADE	Order items have no meaning without parent order
ORDER_ITEMS → BOOKS	RESTRICT	Cannot delete books that appear in orders

Back to Lecture 2 Next Exercise