Library Database

A title is not a lendable object

A catalogue may describe one title while the library owns several physical copies. A member borrows a particular accessioned copy, not the abstract title. Mixing these concepts makes “available” ambiguous and can allow the same copy to be issued twice.

Represent valid states explicitly

Member limits differ for students and faculty and are better represented by a policy table in an extension. “Lost” and “Repair” are copy states independent of title. A real design must define how a lost copy closes or suspends its loan.

Book 1—N copy 1—N loan

Book metadata is stored once. Each copy stores only inventory-specific facts. Loan stores the temporal relationship, so returning a book updates the loan instead of deleting history. This supports borrowing statistics and dispute investigation.

The partial index UNIQUE(copy_id) WHERE returned_on IS NULL is more precise than a general unique copy constraint: one copy may have many historical loans but only one open loan. Partial indexes are SQLite/PostgreSQL-style features; other databases may use a generated column, filtered index or transaction with locking.

Protect invariants under concurrent requests

Issue

1. Confirm member is active and within their current policy limit.
2. Confirm copy is available.
3. Insert an open loan inside a transaction.
4. Let uniqueness reject a competing open loan; trigger marks the copy On Loan.
5. Commit only if every step succeeds.

Return

1. Update the matching open loan with a return date.
2. The return trigger marks its copy Available.
3. Calculate charges from the policy effective for that loan.
4. Commit; if no open row changed, investigate rather than creating a false return.

Choose joins that match the question

Availability begins with book joined to copy because owned copies must exist. Overdue reporting begins with open loan and joins member, copy and book for readable identifiers. Popularity uses LEFT JOIN loan so titles with zero loans are retained.

days overdue = reference date − due date, only when returned_on IS NULL

An illustrative fine of ₹2 per day appears in the demonstration but is not claimed as institutional policy. Production code should store fine rules with effective dates, cap rules and waiver audit. Date calculations and Boolean expressions differ among SQL products, so portability must be planned.

Executable SQLite lifecycle and reports

Loading source…

Run with sqlite3 :memory: < library-database.sql. It creates the normalized schema, partial unique index, two synchronization triggers, deterministic records, availability/overdue reports and an atomic return.

Follow loan 101 back to availability

Test the lifecycle, not only SELECT output

Indexes follow circulation access paths

The unique accession index supports scanning/issue lookup. The partial index both enforces and finds open loans by copy. idx_loan_member_open supports a member’s current/history list. An overdue workload may benefit from an index involving returned_on and due_on; verify selectivity and plans before adding it.

Transactions do not replace durability planning. Use tested backups, point-in-time recovery where required, role-based access, change logs and periodic inventory reconciliation. Barcode input must still be validated; fast incorrect input remains incorrect.

Reason about identity and time

Extensions

1. Add reservations with an ordered queue and expiration time.
2. Model fine policies by member type and effective date.
3. Add acquisitions, suppliers and copy withdrawal history.
4. Create monthly circulation and most-borrowed-subject reports.
5. Implement safe issue/return procedures in PostgreSQL or MySQL and compare syntax.

Model the physical object and its history separately

A trustworthy library system distinguishes catalogue from inventory, represents open/closed time explicitly and enforces the one-open-loan rule at the database boundary. Transactions coordinate lifecycle changes; carefully chosen joins turn that reliable history into useful reports.