Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 37 ยท Projects and Practice

Project: Library Loans with Business Rules and Reports

Model a library with members, books, copies and loans. Enforce rules in code and with partial unique indexes, handle conflicts with transactions and write overdue reports.

The blog project was mostly about reading and writing related data. This one is about rules. A library lends physical copies of books to members, and the interesting questions are all constraints: a copy cannot be on loan twice, a member may hold at most three books, an overdue loan costs money. You will enforce the important rules twice, in Python for friendly messages and in the database as the final guard, then finish with reports built from aggregates and a window function.

The model

erDiagram
    BOOK ||--o{ COPY : "has physical"
    COPY ||--o{ LOAN : "lent in"
    MEMBER ||--o{ LOAN : "borrows"
    BOOK { int id PK
           string title
           string author }
    COPY { int id PK
           string barcode
           int book_id FK }
    MEMBER { int id PK
             string name }
    LOAN { int id PK
           int copy_id FK
           int member_id FK
           date borrowed_on
           date due_on
           date returned_on }

A book is an abstract title, a copy is a physical item with a barcode, and a loan links a copy to a member for a period.

Why separate Book from Copy? Because the library may own three copies of the same title, and you want to count availability per title but track loans per copy.

The central rule, "a copy has at most one open loan", is a partial unique index: unique on copy_id only for rows where returned_on IS NULL. SQLite and PostgreSQL both support partial indexes, so the database refuses a double loan even if two requests race each other and slip past the Python check.

The complete program

Python Library loans: models, rules and reports

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Walk through the output. Both copies of Dune go out (to Ana and Ben), so Ana's second request for Dune is refused with "no copy available". She still gets Emma and Ulysses, which brings her to three open loans; a fourth request would hit the MAX_LOANS rule. The attempt to create a duplicate open loan directly is refused by the database. Python rules give nice messages; the partial index is the guarantee.

What to notice

  • Constraints in the model. Index(..., unique=True, sqlite_where=..., postgresql_where=...) documents the rule where Alembic can create it. A migration would then contain op.create_index("uq_open_loan_per_copy", "loan", ["copy_id"], unique=True, sqlite_where=sa.text("returned_on IS NULL")).
  • Check, then rely on the constraint. borrow() checks first for good error messages, and flushes inside the caller's transaction so an IntegrityError rolls everything back cleanly.
  • A subquery for availability. Copy.id.not_in(select(...)) is one SQL statement, not a Python loop. Be careful: NOT IN returns nothing if the subquery contains NULL; copy_id here is NOT NULL, so it is safe.
  • Reports in SQL. Counting, grouping and ranking happen in the database. func.rank().over(...) is a window function, and works the same on PostgreSQL.
  • Dates are passed in, not read from the clock inside the functions. That makes the code deterministic and easy to test.

Extension ideas

  1. Add a reserve() feature: a Reservation table with a queue per book, and a rule that returning a copy hands it to the first reservation.
  2. Make the fine a stored column so a later change in the rate does not rewrite history.
  3. Add a "most borrowed authors" report with a join through copies and loans.
  4. Convert the rules into a service class with a unit of work (Repository pattern).
  5. Write the Alembic revision that adds the partial index to an existing database.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why enforce 'one open loan per copy' with a partial unique index as well as a Python check?

    A race between check and insert is possible in application code. A constraint is atomic.
  2. 2. What does the partial index uq_open_loan_per_copy make unique?

    Only open loans (no return date) are indexed, so a copy can have many closed loans but one open loan.
  3. 3. Why separate Book and Copy?

    Modeling the physical item separately lets you track each loan and count availability.
  4. 4. Why are dates passed into borrow() and give_back() instead of calling date.today() inside?

    Injecting the date removes hidden dependence on the clock.

Summary

  • Model physical items and abstract titles separately when counts matter.
  • Enforce key rules twice: Python for messages, database constraints (such as partial unique indexes) for guarantees.
  • Flush inside the caller's transaction so constraint errors roll back cleanly.
  • Do counting, grouping and ranking in SQL with aggregates and window functions.
  • Inject dates and other changing inputs to keep business code testable.

Next: Practice: SQLAlchemy Exercises with Solutions