Bookstore Project - Part 1: Table Setup

Let's think this through for a moment

By now you've learned CREATE TABLE, constraints, and INSERT, so let's put them together in an actual project. This project is a small Bookstore Database made up of four tables: Authors, Books, Customers, and Orders. In Part 1, we'll design the database structure and correctly link the tables together using constraints like PRIMARY KEY, FOREIGN KEY, NOT NULL, and UNIQUE. Designing data with real relationships in mind, the way a real-world app would, is one of the best ways to build muscle memory in SQL. In Part 2 and Part 3, we'll write queries against these tables for search, reporting, and more, so this Part 1 structure is the foundation everything else builds on.

Let's build it for real

First, create the Authors table (AuthorID PRIMARY KEY, AuthorName NOT NULL, Country) with CREATE TABLE. Next, create the Books table (BookID PRIMARY KEY, Title NOT NULL, AuthorID FOREIGN KEY REFERENCES Authors, Price, Stock). Then move on to the Customers table (CustomerID PRIMARY KEY, CustomerName NOT NULL, Email UNIQUE, City). Finally, create the Orders table (OrderID PRIMARY KEY, CustomerID FOREIGN KEY, BookID FOREIGN KEY, Quantity, OrderDate) and use INSERT INTO to load sample data: 3 authors, 5 books, 3 customers, and 4 orders.

Created all 4 tables — Authors, Books, Customers, Orders — with PRIMARY KEY/FOREIGN KEY
Added NOT NULL and UNIQUE constraints where needed
Inserted sample data into each table with INSERT INTO

Code example

sql

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(100) NOT NULL,
    Country VARCHAR(50)
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(150) NOT NULL,
    AuthorID INT,
    Price DECIMAL(10,2),
    Stock INT DEFAULT 0,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    City VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    BookID INT,
    Quantity INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

INSERT INTO Authors VALUES (1, 'Aung Aung', 'Myanmar'), (2, 'J.K Rowling', 'UK'), (3, 'Haruki Murakami', 'Japan');

INSERT INTO Books (BookID, Title, AuthorID, Price, Stock) VALUES
(101, 'Myanmar Sarpay', 1, 5000, 20),
(102, 'Harry Potter', 2, 15000, 10),
(103, 'Norwegian Wood', 3, 12000, 8),
(104, 'Kafka on the Shore', 3, 13000, 5),
(105, 'Fantastic Beasts', 2, 14000, 0);

INSERT INTO Customers VALUES
(1, 'Su Su', 'susu@mail.com', 'Yangon'),
(2, 'Ko Ko', 'koko@mail.com', 'Mandalay'),
(3, 'Hla Hla', 'hlahla@mail.com', 'Yangon');

INSERT INTO Orders VALUES
(1, 1, 102, 2, '2026-01-05'),
(2, 2, 101, 1, '2026-01-10'),
(3, 3, 103, 3, '2026-01-12'),
(4, 1, 104, 1, '2026-02-01');

You should see

All 4 tables will be correctly linked by their relationships, and the sample data will insert without errors.

5-minute try-it

Create an extra Publishers table (PublisherID PRIMARY KEY, PublisherName NOT NULL) and use ALTER TABLE to add a PublisherID column to the Books table as a FOREIGN KEY — give yourself 5 minutes to try it.

A quick word of caution

When linking a foreign key, create the parent table and insert its data first, then move on to the child table — doing it in order helps you avoid errors.