Step 5: Design the database

Data must survive a restart, so design the tables for a database: books, members and loans. The loan table links the other two with foreign keys (REFERENCES). A loan with returned_on IS NULL is still open. Run the schema and the sample data, then the query for current loans:

Examplesql

CREATE TABLE books (
  isbn TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  year INTEGER
);
CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);
CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  isbn TEXT NOT NULL REFERENCES books(isbn),
  member_id INTEGER NOT NULL REFERENCES members(id),
  borrowed_on TEXT NOT NULL,
  returned_on TEXT
);

INSERT INTO books VALUES ('1', 'Clean Code', 'Robert Martin', 2008),
                         ('2', 'Refactoring', 'Martin Fowler', 1999),
                         ('3', 'Design Patterns', 'Gamma et al.', 1994);
INSERT INTO members (name) VALUES ('Ada'), ('Sam');
INSERT INTO loans (isbn, member_id, borrowed_on) VALUES ('1', 1, '2026-10-01'), ('2', 1, '2026-10-03'), ('3', 2, '2026-10-05');
UPDATE loans SET returned_on = '2026-10-12' WHERE isbn = '2';

SELECT b.title, m.name, l.borrowed_on
FROM loans l
JOIN books b ON b.isbn = l.isbn
JOIN members m ON m.id = l.member_id
WHERE l.returned_on IS NULL
ORDER BY l.borrowed_on;

Open in editor »

Now ask the questions the application needs. Available books are those with no open loan, and a LEFT JOIN counts loans even for books never borrowed:

Examplesql

Open in editor »

The Java rules map onto SQL: "already on loan" is an open loan exists for this ISBN, and the two-book limit is SELECT COUNT(*) FROM loans WHERE member_id = ? AND returned_on IS NULL. In Java you would run these with JDBC using prepared statements (placeholders ?) to prevent SQL injection.

Quick check

In the loans table, what marks a loan as still open?

returned_on is NULLreturned_on equals todayisbn is NULL

Finish the quick check (and any code exercise) to complete this lesson, or mark it done yourself.

Tip: use the left and right arrow keys to move between lessons.