Library Management System

Illustration by MOMO Studio on Unsplash

Overview

A database-backed library management system built with Python, SQLAlchemy, MySQL, and a Django web frontend. The system handles catalog management for 10k+ records, automated check-in/checkout workflows, and circulation tracking. I was lead developer on the project.

Problem

The core modeling challenge was the book-author relationship. A book can have zero, one, or many authors, and an author can appear across many books. Storing authors directly on the book record — as a comma-separated field or repeated columns — violates third normal form and creates update anomalies: changing an author’s name means touching every book row they appear on. Publisher data had the same problem — publisher name, city, and contact info repeated across every book from that publisher.

Schema Design

The schema was normalized to 3NF. The key tables were Books, Authors, and Publishers, with a BookAuthors junction table resolving the many-to-many relationship between books and authors. Each book held a foreign key to Publishers, eliminating the repeated publisher data. Circulation was tracked through separate tables for checkout history, linking members to books with timestamps.

This structure meant that updating a publisher’s city was a single-row operation that reflected everywhere instantly, and querying “all books by author X” was a straightforward join through the junction table rather than a string search.

Solution

The Django web frontend gave librarians a GUI for check-in/checkout without direct database access. SQLAlchemy handled the ORM layer, mapping the normalized schema to Python objects. The check-in/checkout workflow automated availability tracking — when a book was checked out, its status updated and a circulation record was created in one transaction.

Normalization reduced redundancy in the publisher and author data substantially compared to the denormalized starting point (estimated ~65% reduction in duplicate publisher entries based on the catalog composition). UML diagrams documented the schema and relationships for team alignment and future development.