Library Database Project in MySQL

The project

A library database is a favourite DBMS project because it shows a clear many-to-many relationship: members borrow books, and each book can be borrowed by many members over time. The heart of the design is a borrowings link table.

Designing the tables

• members — id, name, phone.
• books — id, title, author.
• borrowings — id, member_id (FK), book_id (FK), issue_date, due_date, return_date.

Members and books connect only through borrowings, which records each loan.

Creating the schema

CREATE TABLE members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(15)
);
CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50)
);
CREATE TABLE borrowings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    member_id INT, book_id INT,
    issue_date DATE, due_date DATE, return_date DATE,
    FOREIGN KEY (member_id) REFERENCES members(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

The borrowing link table

The borrowings table is the many-to-many link. Each row is one loan: which member took which book, when it was issued, when it is due, and when it was returned. A NULL return_date means the book is still out.

Sample data

INSERT INTO members (name, phone) VALUES ('Aman', '9990001'), ('Diya', '9990002');
INSERT INTO books (title, author) VALUES ('SQL Basics', 'R. Roy'), ('PHP Guide', 'S. Iyer');
INSERT INTO borrowings (member_id, book_id, issue_date, due_date, return_date) VALUES
    (1, 1, '2026-09-01', '2026-09-15', NULL),
    (2, 2, '2026-08-20', '2026-09-03', '2026-09-02');

Useful queries

-- currently issued books (not returned)
SELECT m.name, b.title, br.due_date
FROM borrowings br
JOIN members m ON br.member_id = m.id
JOIN books b ON br.book_id = b.id
WHERE br.return_date IS NULL;

-- overdue books
SELECT m.name, b.title, br.due_date
FROM borrowings br
JOIN members m ON br.member_id = m.id
JOIN books b ON br.book_id = b.id
WHERE br.return_date IS NULL AND br.due_date < CURDATE();

-- how many books each member has borrowed
SELECT m.name, COUNT(*) AS total_borrowed
FROM borrowings br JOIN members m ON br.member_id = m.id
GROUP BY m.id;

Common mistakes

• Putting member_id directly in books. That cannot model many-to-many; use a link table.
• No return_date column. You need it to tell issued from returned.
• Deleting a book that has borrowings. Handle the foreign key (RESTRICT or archive).
• Confusing due_date and return_date. Due is the deadline; return is when it came back.

Summary

• A library database has members, books and a borrowings link table.
• members ↔ books is many-to-many, resolved by borrowings.
• Each borrowing records issue, due and return dates.
• NULL return_date means the book is still issued.
• Query issued, overdue and per-member counts with JOIN and WHERE.

Next, revise many-to-many in ER diagram examples, or build the school database.