Building the Database

Here are all the entities we have so far:

• Category
• Book
• Address
• Author
• Book_Author
• Customer
• Bookstore_Order
• Review
• Tax
• Promotion
• Event

Initial set of tables:

CREATE TABLE IF NOT EXISTS Category (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS Book (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    price NUMERIC NOT NULL,
    quantity_in_stock INTEGER NOT NULL,
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES Category(category_id)
);

CREATE TABLE IF NOT EXISTS Address (
    address_id SERIAL PRIMARY KEY,
    street VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    state VARCHAR(255) NOT NULL,
    zip_code VARCHAR(10) NOT NULL
);

CREATE TABLE IF NOT EXISTS Author (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    address_id INTEGER,
    FOREIGN KEY (address_id) REFERENCES Address(address_id)
);

CREATE TABLE IF NOT EXISTS Book_Author (
    book_id INTEGER,
    author_id INTEGER,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES Book(book_id),
    FOREIGN KEY (author_id) REFERENCES Author(author_id)
);

CREATE TABLE IF NOT EXISTS Customer (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    address_id INTEGER,
    FOREIGN KEY (address_id) REFERENCES Address(address_id)
);

CREATE TYPE OrderStatus AS ENUM ('PLACED', 'SHIPPED', 'DELIVERED', 'CANCELLED');

CREATE TABLE IF NOT EXISTS Bookstore_Order (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    book_id INTEGER,
    quantity_ordered INTEGER NOT NULL,
    order_status OrderStatus DEFAULT 'PLACED',
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (book_id) REFERENCES Book(book_id)
);

CREATE TABLE IF NOT EXISTS Review (
    review_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    book_id INTEGER,
    rating INTEGER NOT NULL,
    comment TEXT,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (book_id) REFERENCES Book(book_id)
);

CREATE TABLE IF NOT EXISTS Tax (
    tax_id SERIAL PRIMARY KEY,
    state VARCHAR(255) NOT NULL UNIQUE,
    tax_rate NUMERIC NOT NULL
);

CREATE TABLE IF NOT EXISTS Promotion (
    promotion_id SERIAL PRIMARY KEY,
    code VARCHAR(255) NOT NULL UNIQUE,
    discount_rate NUMERIC NOT NULL
);

And lastly we'll create an Event table that relates Author and Promotion. This could represent an event where a specific author's books are promoted. The table would have a date column to specify when the event is happening. Here is the SQL:

CREATE TABLE IF NOT EXISTS Event (
    id SERIAL PRIMARY KEY,
    author_id INTEGER,
    promotion_id INTEGER,
    event_date DATE NOT NULL,
    FOREIGN KEY (author_id) REFERENCES Author(id),
    FOREIGN KEY (promotion_id) REFERENCES Promotion(id)
);

Here, the author_id and promotion_id are foreign keys referencing the Author and Promotion tables, respectively. The event_date column stores the date of the event.

This table focus on the culture that was important to Maya.  It would let her create events with a specific author and promotion. When a customer places an order, you could check if the current date matches any event date and if the author of the ordered book is linked to a promotion. If both conditions are met, you apply the promotion to the order.

There are some limitations with this design as it assumes that events would not be multi-day but that is something for another blog post.  :)

Also, keep in mind that I've only modelled out some of the requirements and a full blown production system would have a lot more tables.   Later in the blog, I'll walk you through how to expand the database as addtional features are added.  

If you want to see anything in particular in the bookstore application, drop me note and I'll add it in.

As a final step, let's populate the tables with some seed data.  I'm a fan of the Star Wars universe if you can't tell:

-- Insert Categories
INSERT INTO Category (name) VALUES ('Sci-Fi');
INSERT INTO Category (name) VALUES ('Fiction');
INSERT INTO Category (name) VALUES ('Non-fiction');

-- Insert Addresses
INSERT INTO Address (street, city, state, zip_code) VALUES ('123 Yavin IV St', 'Alderaan', 'CA', '12345');
INSERT INTO Address (street, city, state, zip_code) VALUES ('456 Tatooine St', 'Bespin', 'NY', '67890');
INSERT INTO Address (street, city, state, zip_code) VALUES ('789 Hoth St', 'Endor', 'TX', '11122');

-- Insert Authors
INSERT INTO Author (name, address_id) VALUES ('George Lucas', 1);
INSERT INTO Author (name, address_id) VALUES ('Alan Dean Foster', 2);
INSERT INTO Author (name, address_id) VALUES ('Timothy Zahn', 3);

-- Insert Books
INSERT INTO Book (title, price, quantity_in_stock, category_id) VALUES ('Star Wars: A New Hope', 19.99, 100, 1);
INSERT INTO Book (title, price, quantity_in_stock, category_id) VALUES ('Star Wars: The Empire Strikes Back', 24.99, 75, 1);
INSERT INTO Book (title, price, quantity_in_stock, category_id) VALUES ('Star Wars: Return of the Jedi', 29.99, 50, 1);

-- Insert Book_Author relations
INSERT INTO Book_Author (book_id, author_id) VALUES (1, 1);
INSERT INTO Book_Author (book_id, author_id) VALUES (2, 2);
INSERT INTO Book_Author (book_id, author_id) VALUES (3, 3);

-- Insert Customers
INSERT INTO Customer (name, email, address_id) VALUES ('Luke Skywalker', 'luke.skywalker@email.com', 1);
INSERT INTO Customer (name, email, address_id) VALUES ('Leia Organa', 'leia.organa@email.com', 2);
INSERT INTO Customer (name, email, address_id) VALUES ('Han Solo', 'han.solo@email.com', 3);

-- Insert Orders
INSERT INTO Bookstore_Order (customer_id, book_id, quantity_ordered, order_status) VALUES (1, 1, 2, 'PLACED');
INSERT INTO Bookstore_Order (customer_id, book_id, quantity_ordered, order_status) VALUES (2, 2, 1, 'DELIVERED');
INSERT INTO Bookstore_Order (customer_id, book_id, quantity_ordered, order_status) VALUES (3, 3, 3, 'SHIPPED');

-- Insert Reviews
INSERT INTO Review (customer_id, book_id, rating, comment) VALUES (1, 1, 5, 'Great book!');
INSERT INTO Review (customer_id, book_id, rating, comment) VALUES (2, 2, 4, 'Interesting read.');
INSERT INTO Review (customer_id, book_id, rating, comment) VALUES (3, 3, 4, 'Enjoyed it.');

-- Insert Tax rates
INSERT INTO Tax (state, tax_rate) VALUES ('NY', 0.08);
INSERT INTO Tax (state, tax_rate) VALUES ('CA', 0.075);
INSERT INTO Tax (state, tax_rate) VALUES ('TX', 0.085);

-- Insert Promotions
INSERT INTO Promotion (code, discount_rate) VALUES ('PROMO10', 0.1);
INSERT INTO Promotion (code, discount_rate) VALUES ('PROMO20', 0.2);
INSERT INTO Promotion (code, discount_rate) VALUES ('PROMO30', 0.3);

-- Insert Events
INSERT INTO Event (author_id, promotion_id, event_date) VALUES (1, 1, '2023-06-01');
INSERT INTO Event (author_id, promotion_id, event_date) VALUES (2, 2, '2023-06-02');
INSERT INTO Event (author_id, promotion_id, event_date) VALUES (3, 3, '2023-06-03');