A database model becomes easier to judge when you ask what the application must refuse. In a small lending library, a loan must refer to a real physical copy. Two copies of the same title must remain distinguishable. A returned loan should keep its history instead of disappearing.
These requirements are more useful than starting with a long list of fashionable database features. Use PostgreSQL for the SQL examples below in a disposable local database. Other systems differ in their syntax and constraint behavior, so check their own documentation before translating the statements.
Separate the title from the object on the shelf
A book title and a physical copy have different lifetimes. A title's spelling can be corrected, while a copy might be lost or replaced. Storing a separate title row lets two copies share bibliographic information without pretending they are the same lendable object.
CREATE TABLE books (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL CHECK (length(trim(title)) > 0)
);
CREATE TABLE copies (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
book_id bigint NOT NULL REFERENCES books(id),
shelf_code text NOT NULL UNIQUE
);
CREATE TABLE loans (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
copy_id bigint NOT NULL REFERENCES copies(id),
borrowed_on date NOT NULL,
returned_on date,
CHECK (returned_on IS NULL OR returned_on >= borrowed_on)
);This teaching schema deliberately leaves borrowers out. A real library would need a borrower model and decisions about privacy and retention. Do not add real names or contact details just to make a learning database feel realistic.
A primary key identifies a row. A foreign key links a row to one that exists elsewhere. PostgreSQL's foreign key tutorial shows how the database rejects missing references. The title check prevents a blank title, while the unique shelf code prevents two physical copies from sharing this project's identifying label.
Ask which layer owns each rule
Use application validation to explain mistakes clearly, and database constraints to protect stored facts. The database can receive writes from an import or an administrative tool as well as the main application. A friendly form is insufficient protection if another write path can create an impossible loan.
Read the PostgreSQL constraints documentation before relying on a check. In particular, a CHECK expression alone does not imply NOT NULL. The schema names required fields explicitly because an unknown value and a value that violates a comparison are different cases.
The sample does not yet prevent two open loans for the same copy. That rule spans multiple rows. In PostgreSQL, a carefully designed partial unique index can enforce at most one loan per copy where returned_on is null; application-level checks alone can race. Adding this rule is a separate step worth testing, rather than claiming that the basic schema already solves borrowing concurrency.
Read back the relationship
A useful first query answers a real screen requirement: list copies currently on loan with their book titles. Build the join in the same order you would explain the relationship.
SELECT c.shelf_code, b.title, l.borrowed_on
FROM loans AS l
JOIN copies AS c ON c.id = l.copy_id
JOIN books AS b ON b.id = c.book_id
WHERE l.returned_on IS NULL
ORDER BY l.borrowed_on, l.id;The second sort field makes equal borrowing dates predictable. This is a small detail, but it helps when a UI or test compares ordered results. Resist adding an index to every column immediately. Later, use actual queries and query plans to decide which lookups need help.
Exercise the rules with made-up records
- Create one book titled River Maps and two copies with shelf codes RM-A and RM-B.
- Insert an open loan for RM-A using its generated copy id. The query should return one row.
- Try inserting a copy that references a nonexistent book id. Expect a foreign key error.
- Try a returned_on date earlier than borrowed_on. Expect a check violation.
- Set a valid returned_on date on the open loan. The query should now return no rows.
Keep these experiments out of production. For a multi-step operation, use a transaction so its database changes can succeed or roll back together; PostgreSQL's transaction tutorial explains the boundary. A transaction does not automatically solve every concurrent business rule, and it cannot roll back an email already sent outside the database.
To practise inspecting a read model before writing a UI, represent one query result as JSON in the JSON lab. Select a field with a JSON Pointer and compare the value with your SQL result. That tool formats and inspects JSON; it does not run SQL or validate your database design.