Library Management System Database Design: ER Diagram, Tables and SQL

Maria Garcia runs a college library from one spreadsheet: a row per book, the authors typed into a cell, and a column that says how many copies the library owns. Twice this term the sheet said a copy was in when it was out. This post turns a half-hour conversation with her into a database: nine tables, every one of them created and loaded on SQL Server and on PostgreSQL, the five questions an examiner asks answered with real output, the spreadsheet's two mistakes shown failing and then repaired, and an ER diagram drawn from the script. Everything quoted below is what the engines printed.

The conversation

The questions are the design. Every one below is there to separate two things the spreadsheet had merged, or to find a rule the database can enforce.

Maria: I run the library of a college. Everything is in one spreadsheet: a row per book, the authors typed into one cell, and a column that says how many copies we have.

Designer: When you say "a book", do you mean the title or the thing on the shelf? If a student returns a torn copy, can you tell which of the three it was?

Maria: No, and that is my problem. Every copy gets an accession number stamped inside the cover when it arrives, ACC-1001 and so on, and each one wears out differently.

Designer: So the title and the copy are two different things. The title has a name, a year and a category; each copy has its own accession number, its condition and the date it arrived. Can a title have more than one author?

Maria: Often. Tables and Keys Explained has two, and Wei Zhang appears on three of our titles.

Designer: Then authors are a list of their own, and a small table pairs titles with authors in the order the names appear on the cover. Who borrows?

Maria: Students and staff. Every member has a card with a number, an email and a phone. A student can hold 3 books at a time, a member of staff 5. A loan is 14 days for both, and the fine is 2 for every day late.

Designer: Then each member type carries its limit, its loan days and its fine, and a member points at their type. When you lend a book, what do you write down?

Maria: Which copy, to whom, the day it went out and the day it is due. When it comes back, the return date. And please stop us lending a copy that is already out; today that happens because the count in the sheet is wrong.

Designer: That count is the trouble. If you subtract one for every loan, the sheet no longer says how many copies you own. Counting copy rows and open loans gives both numbers, and a rule in the database can refuse a second open loan on the same copy. What happens when every copy of a title is out?

Maria: Students ask us to keep the next one for them. Whichever copy comes back first goes to whoever asked first. Some cancel, and once they collect the book it is done.

Designer: A reservation is a member, a title, the time they asked and a status: waiting, fulfilled or cancelled. The earliest waiting one is next in line. And the weekly questions, overdue books, fines and popular titles, are all counted from loans; none needs a column of its own.

From sentences to tables

Nine tables came out of the thirteen exchanges. Two of them are not in any textbook list, and they are the ones that make the design honest. member_types holds the rules Maria said out loud, 3 or 5 books, 14 days, 2 per day, as rows rather than as numbers buried inside queries. copies separates the title from the thing on the shelf, which is the distinction the spreadsheet lost.

The tables that carry the rules

The full script creates all nine in section 2. The two below carry the rules Maria asked for. PostgreSQL first:

CREATE TABLE copies (
    copy_id         integer GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_copies PRIMARY KEY,
    book_id         integer     NOT NULL CONSTRAINT fk_copies_book REFERENCES books (book_id),
    accession_no    varchar(20) NOT NULL CONSTRAINT uq_copies_accession_no UNIQUE,
    copy_condition  varchar(10) NOT NULL CONSTRAINT ck_copies_copy_condition
                                CHECK (copy_condition IN ('new', 'good', 'fair', 'poor')),
    added_on        date        NOT NULL
);

CREATE TABLE loans (
    loan_id      integer GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_loans PRIMARY KEY,
    copy_id      integer NOT NULL CONSTRAINT fk_loans_copy REFERENCES copies (copy_id),
    member_id    integer NOT NULL CONSTRAINT fk_loans_member REFERENCES members (member_id),
    issued_on    date    NOT NULL,
    due_on       date    NOT NULL,
    returned_on  date    NULL,
    CONSTRAINT ck_loans_due_after_issue CHECK (due_on > issued_on),
    CONSTRAINT ck_loans_returned_after_issue CHECK (returned_on IS NULL OR returned_on >= issued_on)
);
-- A copy is with one member at a time: at most one open loan per copy.
CREATE UNIQUE INDEX uq_loans_open_copy ON loans (copy_id) WHERE returned_on IS NULL;

The last line is the rule from the conversation. A unique index on copy_id alone would allow one loan per copy ever; the WHERE makes it one open loan per copy, and returned loans stay as history. SQL Server spells the same table with INT IDENTITY(1,1), DATE and the dbo. prefix, and the index line is the same words:

CREATE UNIQUE INDEX uq_loans_open_copy ON dbo.loans (copy_id) WHERE returned_on IS NULL;

Section 3 ends by lending ACC-1005, which David Chen already has, to Priya Nair. Both engines refuse, and the error names the rule, which is why every constraint in the script has a name you chose:

ERROR:  duplicate key value violates unique constraint "uq_loans_open_copy"
DETAIL:  Key (copy_id)=(5) already exists.

Msg 2601: Cannot insert duplicate key row in object 'dbo.loans' with unique index 'uq_loans_open_copy'. The duplicate key value is (5).

The same trick gives the reservation rule: a unique index on (book_id, member_id) where status = 'waiting', so a member waits for a title once but can reserve it again next year. Every foreign key column also gets a plain index of its own, eight of them, because neither engine indexes the referencing side for you. Everything else differs only in spelling: NVARCHAR against varchar, DECIMAL(6,2) against numeric(6,2), DATETIME2(0) against timestamp(0), and GO between batches on one side against nothing on the other.

The ER diagram

Five questions the examiner asks

Section 4 of the script answers the questions Maria's principal asks every week. The outputs below are PostgreSQL's; SQL Server returned the same rows in the same order for all five. Which copies are on loan right now, and with whom:

 accession_no |           title           |    member     | card_no | issued_on  |   due_on
--------------+---------------------------+---------------+---------+------------+------------
 ACC-1005     | The Patient Compiler      | David Chen    | S-2401  | 2026-09-21 | 2026-10-05
 ACC-1012     | A Map Drawn in Salt       | Aisha Khan    | S-2402  | 2026-09-23 | 2026-10-07
 ACC-1004     | The Patient Compiler      | Mateo Alvarez | T-0103  | 2026-09-24 | 2026-10-08
 ACC-1008     | Seven Small Circuits      | Tomas Silva   | S-2403  | 2026-09-28 | 2026-10-12
 ACC-1001     | Tables and Keys Explained | Kwame Mensah  | T-0101  | 2026-10-06 | 2026-10-20
 ACC-1010     | Monsoon Over Tin Roofs    | Yuki Tanaka   | S-2404  | 2026-10-09 | 2026-10-23
(6 rows)

Which loans are overdue on 16 October 2026, and what is owed. The fine is days late times the member type's rate, computed when asked and never stored:

SELECT m.full_name AS member, m.member_type, b.title, c.accession_no, l.due_on,
       DATE '2026-10-16' - l.due_on                    AS days_late,
       (DATE '2026-10-16' - l.due_on) * t.fine_per_day AS fine
FROM loans l
JOIN copies       c ON c.copy_id     = l.copy_id
JOIN books        b ON b.book_id     = c.book_id
JOIN members      m ON m.member_id   = l.member_id
JOIN member_types t ON t.member_type = m.member_type
WHERE l.returned_on IS NULL

    member     | member_type |        title         | accession_no |   due_on   | days_late | fine
---------------+-------------+----------------------+--------------+------------+-----------+-------
 David Chen    | student     | The Patient Compiler | ACC-1005     | 2026-10-05 |        11 | 22.00
 Aisha Khan    | student     | A Map Drawn in Salt  | ACC-1012     | 2026-10-07 |         9 | 18.00
 Mateo Alvarez | staff       | The Patient Compiler | ACC-1004     | 2026-10-08 |         8 | 16.00
 Tomas Silva   | student     | Seven Small Circuits | ACC-1008     | 2026-10-12 |         4 |  8.00
(4 rows)

Subtracting two dates gives whole days on PostgreSQL; the SQL Server script uses DATEDIFF(day, l.due_on, @as_of) with the date declared once at the top. The five most borrowed titles, counted from loans:

           title           | times_borrowed
---------------------------+----------------
 Tables and Keys Explained |              4
 Monsoon Over Tin Roofs    |              3
 The Patient Compiler      |              3
 A Map Drawn in Salt       |              2
 Seven Small Circuits      |              2
(5 rows)

How many copies of each title are on the shelf, counted from copies minus open loans:

            title             | copies_owned | on_loan | available
------------------------------+--------------+---------+-----------
 A Map Drawn in Salt          |            1 |       1 |         0
 A Week of Queues and Stacks  |            2 |       0 |         2
 Bridges That Learned to Bend |            1 |       0 |         1
 Counting the Stars by Hand   |            1 |       0 |         1
 Letters From a Hill Station  |            1 |       0 |         1
 Monsoon Over Tin Roofs       |            2 |       1 |         1
 Seven Small Circuits         |            1 |       1 |         0
 Tables and Keys Explained    |            3 |       1 |         2
 The Patient Compiler         |            2 |       2 |         0
 Why the River Bends          |            1 |       0 |         1
(10 rows)

And who is next in line for each reserved title, the earliest waiting reservation per title:

        title         |   next_member   | card_no |     reserved_at     | waiting
----------------------+-----------------+---------+---------------------+---------
 A Map Drawn in Salt  | Mateo Alvarez   | T-0103  | 2026-10-05 12:00:00 |       2
 Seven Small Circuits | Liam O'Brien    | S-2406  | 2026-10-02 11:30:00 |       2
 The Patient Compiler | Fatima Al-Sayed | S-2405  | 2026-09-25 10:15:00 |       3
(3 rows)

Kwame Mensah reserved The Patient Compiler a day before Fatima Al-Sayed and then cancelled, so the status filter passes over him. The three titles with waiting reservations are exactly the three with zero available copies in the previous answer, which is the kind of cross-check an examiner likes to see you make.

The spreadsheet mistake, and the repair

Section 5 recreates Maria's sheet as a table, books_flat, with the same ten titles, the authors in one cell and copies as a number, and asks it the questions. How many titles has each author written:

           authors           | titles
-----------------------------+--------
 Arjun Mehta                 |      1
 Chloe Dubois                |      1
 Elena Rossi, Wei Zhang      |      1
 Hannah Weber                |      1
 Hannah Weber, Arjun Mehta   |      1
 Ngozi Okafor                |      1
 Ngozi Okafor, Sofia Petrova |      1
 Sofia Petrova               |      1
 Wei Zhang                   |      1
 Wei Zhang, Elena Rossi      |      1
(10 rows)

Ten "authors" with one title each, and Wei Zhang split over three rows; a WHERE authors = 'Wei Zhang' finds one title of his three. The same question against the junction table:

SELECT a.full_name AS author, COUNT(*) AS titles
FROM authors a
JOIN book_authors ba ON ba.author_id = a.author_id
GROUP BY a.author_id, a.full_name
ORDER BY titles DESC, author;
    author     | titles
---------------+--------
 Wei Zhang     |      3
 Arjun Mehta   |      2
 Elena Rossi   |      2
 Hannah Weber  |      2
 Ngozi Okafor  |      2
 Sofia Petrova |      2
 Chloe Dubois  |      1
(7 rows)

The second mistake is the count. Two loans of The Patient Compiler recorded the spreadsheet way, by subtracting one each time:

UPDATE books_flat SET copies = copies - 1 WHERE title = 'The Patient Compiler';
UPDATE 1
UPDATE books_flat SET copies = copies - 1 WHERE title = 'The Patient Compiler';
UPDATE 1
        title         | copies
----------------------+--------
 The Patient Compiler |      0
(1 row)

The library owns two copies and the row now says zero. It cannot say which copies are out, with whom, or in what condition, and a third subtraction would go negative. One row per copy answers all of it:

 accession_no | copy_condition |  with_member  |   due_on
--------------+----------------+---------------+------------
 ACC-1004     | fair           | Mateo Alvarez | 2026-10-08
 ACC-1005     | good           | David Chen    | 2026-10-05
(2 rows)

What this design does not do

Two things Maria said are stored but not enforced, and a viva examiner may ask about both. The 3 or 5 limit is a row in member_types; refusing a fourth open loan for a student needs a trigger or a check in the statement that issues the loan, and the script does not build one. Fines are computed for open loans and never recorded, so a late book returned last month leaves no trace of what was paid. Both are a table and a rule away, and both are better left out of a first design than done wrong.

Frequently asked

What tables does a library management system database need?

Nine in this design: categories, authors, books, book_authors (the many-to-many pair), copies (one row per physical copy, with its accession number), member_types (the borrowing limit, loan days and fine per day as rows), members, loans and reservations. Overdue loans, fines, popular titles and available copies are counted from loans and copies, never stored.

How do I stop the library system from lending a copy that is already out?

With a filtered unique index on the loans table: CREATE UNIQUE INDEX uq_loans_open_copy ON loans (copy_id) WHERE returned_on IS NULL. SQL Server and PostgreSQL both support it with the same words. A second open loan on the same copy is refused with an error that names the index, and returned loans stay as history because their returned_on is set.

Why is storing the number of copies in the books table a mistake?

Because the number drifts. Each loan subtracts one, so the row stops saying how many copies the library owns, cannot say which copies are out or in what condition, and goes negative one day. One row per copy, plus a loan that points at the copy, gives copies owned, copies on loan and copies available by counting, and the numbers are always right.

Next: Part 2, a school management system, designed from a conversation with the office manager: sections, teachers, subjects, marks and guardians, and the mark sheet that should never be a row of columns.