Database Design Process: From Requirements to MySQL Tables

Good schemas are not drawn in one go. Designing a database is a short, repeatable process, and the earlier you catch a mistake, the cheaper it is. The relationship patterns themselves are in Relationships. This lesson is about the process that leads to them.

The seven steps

Describe what the system must do, in plain sentences.
Find the tables: the nouns that matter.
List the columns of each, and pick the right data types (Choosing Data Types).
Choose a primary key for every table.
Connect the tables: the verbs become relationships (one-to-many, many-to-many).
Add constraints: NOT NULL, UNIQUE, CHECK, foreign keys (Constraints).
Test with sample data and the real questions the application will ask.

An example: a small library

The sentences:

> Members borrow books. A book can have several authors, and an author can write several books. The library owns several physical copies of a book. A loan records which copy was borrowed by which member, when it is due and when it was returned.

Nouns: member, book, author, copy, loan. Verbs: borrows, writes, owns.

Three decisions come straight from the sentences:

A book (the title) and a copy (one physical item on a shelf) are different things. If you store only “books”, you cannot say which of three copies is lent out.
Book and author are many-to-many, so they need a junction table.
A loan is an event that connects a member and a copy, and it has its own data (dates).

query.sql

SQL

CREATE TABLE members (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL,
    email VARCHAR(120) NOT NULL,
    joined_on DATE NOT NULL,
    UNIQUE KEY uq_members_email (email)
);

CREATE TABLE authors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL
);

CREATE TABLE books (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    isbn CHAR(13) NOT NULL,
    published_year SMALLINT UNSIGNED NULL,
    UNIQUE KEY uq_books_isbn (isbn)
);

CREATE TABLE book_authors (
    book_id INT UNSIGNED NOT NULL,
    author_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (book_id, author_id),
    CONSTRAINT fk_book_authors_book FOREIGN KEY (book_id) REFERENCES books (id),
    CONSTRAINT fk_book_authors_author FOREIGN KEY (author_id) REFERENCES authors (id)
);

CREATE TABLE copies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    book_id INT UNSIGNED NOT NULL,
    shelf VARCHAR(10) NOT NULL,
    CONSTRAINT fk_copies_book FOREIGN KEY (book_id) REFERENCES books (id)
);

CREATE TABLE loans (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    copy_id INT UNSIGNED NOT NULL,
    member_id INT UNSIGNED NOT NULL,
    loaned_on DATE NOT NULL,
    due_on DATE NOT NULL,
    returned_on DATE NULL,
    CONSTRAINT fk_loans_copy FOREIGN KEY (copy_id) REFERENCES copies (id),
    CONSTRAINT fk_loans_member FOREIGN KEY (member_id) REFERENCES members (id),
    CONSTRAINT chk_loans_dates CHECK (due_on >= loaned_on)
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Step 7: test the design with sample data

A schema is only proven when it answers real questions. Insert a few rows:

query.sql

SQL

INSERT INTO members (name, email, joined_on) VALUES ('Maya', 'maya@example.com', '2025-01-10'), ('Rohan', 'rohan@example.com', '2025-02-01');
INSERT INTO authors (name) VALUES ('A. Sharma'), ('B. Rao'), ('C. Iyer');
INSERT INTO books (title, isbn, published_year) VALUES ('Learning SQL', '9780000000011', 2022), ('Practical MySQL', '9780000000028', 2021);
INSERT INTO book_authors VALUES (1, 1), (2, 1), (2, 2), (2, 3);
INSERT INTO copies (book_id, shelf) VALUES (1, 'A1'), (1, 'A1'), (2, 'B3');
INSERT INTO loans (copy_id, member_id, loaned_on, due_on, returned_on) VALUES
    (1, 1, '2026-02-01', '2026-02-15', '2026-02-10'),
    (3, 1, '2026-02-20', '2026-03-06', NULL),
    (2, 2, '2026-02-25', '2026-03-11', NULL);

Output

OUTPUT

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Question 1: which books have which authors?

query.sql

SQL

SELECT b.title, GROUP_CONCAT(a.name ORDER BY a.name SEPARATOR ', ') AS authors
FROM books b
JOIN book_authors ba ON ba.book_id = b.id
JOIN authors a ON a.id = ba.author_id
GROUP BY b.id, b.title
ORDER BY b.title;

Output

OUTPUT

+-----------------+----------------------------+
| title           | authors                    |
+-----------------+----------------------------+
| Learning SQL    | A. Sharma                  |
| Practical MySQL | A. Sharma, B. Rao, C. Iyer |
+-----------------+----------------------------+
2 rows in set

Question 2: which loans are open (not returned), and which are overdue on 8 March 2026?

query.sql

SQL

SELECT m.name, b.title, l.due_on, l.due_on < '2026-03-08' AS overdue
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN copies c ON c.id = l.copy_id
JOIN books b ON b.id = c.book_id
WHERE l.returned_on IS NULL
ORDER BY l.due_on;

Output

OUTPUT

+-------+-----------------+------------+---------+
| name  | title           | due_on     | overdue |
+-------+-----------------+------------+---------+
| Maya  | Practical MySQL | 2026-03-06 |       1 |
| Rohan | Learning SQL    | 2026-03-11 |       0 |
+-------+-----------------+------------+---------+
2 rows in set

Both questions are answered with plain joins. That is a good sign that the structure fits the problem.

Testing the rules too

Sample data should also try to break the rules:

query.sql

SQL

INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (1, 1, '2026-03-10', '2026-03-01');

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_loans_dates' is violated.

The CHECK constraint refuses a due date before the loan date. (Error 3819 is the check-constraint error.) Now the gap in the design: nothing stops the same copy from having two open loans:

query.sql

SQL

1
2

INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (3, 2, '2026-02-28', '2026-03-14');
SELECT copy_id, COUNT(*) AS open_loans FROM loans WHERE returned_on IS NULL GROUP BY copy_id HAVING COUNT(*) > 1;

Output

OUTPUT

+---------+------------+
| copy_id | open_loans |
+---------+------------+
|       3 |          2 |
+---------+------------+
1 row in set

Copy 3 is now lent to two members at the same time. Finding this with a test query is exactly the point of step 7. MySQL has no “partial unique index”, but a generated column gives you the same effect: it holds the copy id while the loan is open, and NULL when it is returned. A UNIQUE index ignores NULLs, so only open loans are compared.

query.sql

SQL

DELETE FROM loans WHERE copy_id = 3 AND member_id = 2 AND loaned_on = '2026-02-28';
ALTER TABLE loans ADD COLUMN open_copy_id INT UNSIGNED GENERATED ALWAYS AS (IF(returned_on IS NULL, copy_id, NULL)) STORED, ADD UNIQUE KEY uq_one_open_loan_per_copy (open_copy_id);

INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (3, 2, '2026-02-28', '2026-03-14');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry '3' for key 'loans.uq_one_open_loan_per_copy'

Error 1062 is the duplicate-key error: the second open loan of copy 3 is refused by the database, however buggy the application. Returning the book sets returned_on, which sets the generated column to NULL, and the copy can be lent again (Generated Columns).

query.sql

SQL

1
2
3

UPDATE loans SET returned_on = '2026-03-02' WHERE id = 2;
INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (3, 2, '2026-03-02', '2026-03-16');
SELECT copy_id, member_id, returned_on FROM loans WHERE copy_id = 3 ORDER BY id;

Output

OUTPUT

+---------+-----------+-------------+
| copy_id | member_id | returned_on |
+---------+-----------+-------------+
|       3 |         1 | 2026-03-02  |
|       3 |         2 | NULL        |
+---------+-----------+-------------+
2 rows in set

Primary keys: surrogate or natural

Every table above uses a surrogate key: an AUTO_INCREMENT integer with no meaning. A natural key (an ISBN, an email, a country code) comes from the real world. Prefer surrogate keys as the primary key, and put a UNIQUE constraint on the natural identifier (uq_books_isbn, uq_members_email). Real-world identifiers change, repeat in surprising ways or turn out not to be unique, and the primary key is copied into every foreign key and every secondary index (Primary Key).

Common mistakes

Starting with screens or reports instead of the facts the system must remember.
Merging two concepts into one table (book and copy, product and price history).
Skipping step 7. A design that has never been queried hides its flaws.
Designing for a maybe: tables and columns for features nobody asked for.

lightbulb

Keep the sentences

Save the plain-language description with the schema. When someone later asks “why are copies separate from books?”, the answer is written next to the SQL.