A useful database begins with the facts the business needs to remember and the rules those facts must obey. Drawing tables too early can freeze an assumption nobody agreed on. This reference starts with a small training-center booking system and turns its requirements into a relational design. You will identify entities, distinguish an event from a relationship, choose keys, and test the model with queries. The SQL examples use explicit integer IDs and widely supported constraints to keep the design understandable across PostgreSQL, MySQL, SQL Server, and SQLite, while calling out enforcement differences that need verification.
Ask questions that uncover the data rules
A statement such as 'Students book courses' is not enough to define a schema. Can one student book the same course more than once? Is a course a reusable description or a scheduled occurrence? Can a booking be canceled, and must its history remain? Who defines capacity, and can a session be moved after bookings exist? Ask stakeholders for examples of ordinary and exceptional cases. Record the meaning of each term so course and session do not become interchangeable later. Identify which facts are required, optional, unique, and time-dependent. A clear requirement might say a student can have at most one active booking per scheduled session. That precision drives the key and status design rather than guessing from a noun list.
Identify entities and their identities
Students, course descriptions, scheduled sessions, and bookings represent different facts. A student's name is not a safe primary key because names repeat and change. Use a stable internal student ID and a separate unique external reference when the business needs one. A session has its own ID because the same course can run on different dates. A booking may be modeled as an association with a composite key or a separate event ID depending on cancellation and rebooking history. Do not store a comma-separated list of student IDs in a session row. Each booking belongs in its own row so queries, constraints, and updates can address it independently. Write a one-sentence meaning for every proposed table.
Separate current facts from historical facts
A course's current fee can change, but a confirmed booking may need to preserve the price agreed at that time. Store a booking price snapshot when that is the business fact, rather than deriving every old invoice from the current course fee. That is not careless duplication; the two values describe different moments. The same reasoning applies to delivery addresses and product descriptions captured in orders. Record timestamps and status history only when requirements need them, with a clear timezone convention. Avoid a generic history table with unclear semantics just because history sounds useful. Ask which past questions the business must answer and which data it is allowed to retain.
Translate rules into constraints where possible
A primary key protects row identity. Foreign keys connect bookings to existing students and sessions. NOT NULL expresses required fields, UNIQUE prevents prohibited duplicate combinations, and CHECK can express a row-local rule such as nonnegative capacity. A uniqueness rule for active bookings may require a dialect-specific partial index or another schema strategy rather than an unconditional composite key if canceled rows remain. A capacity rule compares many bookings with a session limit, so a simple row CHECK is not enough. It needs a reviewed transaction or locking strategy and perhaps a maintained counter. Application validation gives friendly messages, while database constraints protect against other writers and concurrent requests. State which rules are enforced where.
Test the model using real questions
Write the queries the application will need before considering the schema finished. Can you list a student's future sessions, calculate booked places, find sessions with no bookings, and identify canceled bookings? Try small fixtures with repeated course runs, two students with the same name, a canceled booking, and an empty session. If a query requires parsing a text list or choosing an arbitrary duplicated value, reconsider the model. Define the result grain: one row per session, booking, or student. A join can multiply rows, so counts and totals need the intended grouping. Query design is an early way to validate whether table boundaries match business facts.
Refine for operations without losing meaning
Choose indexes from common lookup and join patterns after the logical model is sound. Estimate data volume, retention, and concurrency to identify likely bottlenecks. Normalization reduces update anomalies, while carefully justified snapshots preserve history. Avoid denormalizing merely because a hypothetical query might be slow. Plan schema migrations and compatibility with existing data. Use exact dialect documentation for identity generation, CHECK enforcement, filtered indexes, and date types. SQLite foreign keys must be enabled for the connection under its supported behavior, and MySQL's engine and version affect enforcement. Keep the design document, data dictionary, and migration definitions aligned. A diagram alone does not capture every business rule.
Practice task and worked solution
Model a library that lends individual physical copies of a book. The solution separates book descriptions, copies, members, and loans. Ask whether a copy can have more than one active loan, how renewals are recorded, and which historical due dates must be retained. Choose stable keys and a foreign key from loans to copies and members. State how the active-loan uniqueness rule is enforced in your chosen dialect. Create a fixture with two copies of one title and one returned loan, then write a query for currently borrowed copies. Explain why using the title as the copy's primary key would fail. Keep deletion and retention rules explicit.
Continue learning
Continue with table creation, relationships, and normalization.
The PostgreSQL constraint guide explains row constraints and the limits of cross-row checks.
Database Design from Requirements Examples
Each example shows the code, the output it produces and what each part does.
A minimal booking relationship
This fixture uses explicit IDs and generic SQL types. Run in a disposable schema with foreign-key enforcement enabled. Production booking history and capacity rules need the additional design decisions explained above.
The single session has two booking rows. COUNT uses the child key so a future empty session would count zero rather than the outer join's placeholder row.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE sessions (
session_id INTEGER PRIMARY KEY,
title VARCHAR(100) NOT NULL,
capacity INTEGER NOT NULL CHECK (capacity >= 0)
);
CREATE TABLE bookings (
student_id INTEGER NOT NULL REFERENCES students(student_id),
session_id INTEGER NOT NULL REFERENCES sessions(session_id),
PRIMARY KEY (student_id, session_id)
);
INSERT INTO students VALUES (1, 'Ada'), (2, 'Sam');
INSERT INTO sessions VALUES (10, 'SQL Basics', 20);
INSERT INTO bookings VALUES (1, 10), (2, 10);
SELECT s.title, COUNT(b.student_id) AS booked
FROM sessions s
LEFT JOIN bookings b ON b.session_id = s.session_id
GROUP BY s.session_id, s.title;title | booked
SQL Basics | 2Database Design from Requirements FAQ
Should every noun become a table?
No. A noun in the requirements becomes a table only when it represents a distinct fact with its own identity and attributes, such as customers or bookings. Other nouns are attributes of something else, like a customer's email, or values from a fixed list, like a booking status.
Is a name a good key?
Usually not, because names change and repeat: two customers can share a name, and people change their names. Use a stable surrogate id as the primary key, and add a UNIQUE constraint on a natural identifier, such as an email, when one genuinely exists.
Can one CHECK enforce capacity across rows?
No. A CHECK constraint only sees the values in the row being written, so a row-local check cannot generally enforce an aggregate rule such as "no more than 20 bookings per session". Enforce it in a transaction with locking, a trigger, or a counter column protected by a check.
Should history always be derived from current values?
No. Preserve historical snapshots when the requirement describes past agreed facts, such as the price a customer actually paid. Deriving that from the product's current price would silently rewrite history whenever the price changes.
What should I gather before designing tables?
Collect the questions the system must answer and the rules it must enforce: what is recorded, who or what each record belongs to, which values are required or unique, and what history must be kept. Concrete example records and edge cases reveal relationships far better than a list of field names.
Database Design from Requirements Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Two physical copies share one book title. What needs separate identity?
Database Design from Requirements Quiz
2 questions to check what you learned.
Why separate a course from its scheduled sessions?
- A course and a scheduled session must always share one primary key
- One course can occur at different times with separate bookings
- A course describes a person while a session describes a price
- A course can never have more than one date
What should be clarified before enforcing a unique booking pair?
- Whether student names should be used as primary keys
- Whether every booking can omit its session reference
- Whether canceled and repeated bookings need retained history
- Whether a foreign key automatically preserves all cancellation history