Library Management System

A database system for managing libraries, books, and patrons with borrowing relationships.

Requirements

Build a library management system that tracks books, patrons (library members), and their borrowing history. The system should support:

1. Books and Authors: Books are written by authors. An author can write multiple books, and each book has one primary author.

2. Patrons and Borrowing: Patrons can borrow multiple books, and books can be borrowed by multiple patrons over time (but only one patron at a time in the current period).

3. Categories: Books belong to categories (Fiction, Non-Fiction, Science, History, etc.). A book can belong to multiple categories, and each category contains multiple books.

4. Data Management: When an author is deleted, their books should remain in the system (the author field should be set to null). When a patron is deleted, their borrowing records should be automatically removed.

Implementation

@generates

You should implement the following models with appropriate relationships:

• Author model with id, name, and birth_year
• Book model with id, title, publication_year, and author relationship
• Patron model with id, name, and email
• Category model with id and name
• BorrowRecord model to track when a patron borrows a book (with borrow_date and return_date)
• BookCategory link model for the many-to-many relationship between books and categories

All models should support bidirectional navigation (e.g., from author to books and from book to author).

API

from sqlmodel import SQLModel, Field, Relationship
from datetime import date
from typing import Optional

class Author(SQLModel, table=True):
    """Author model with one-to-many relationship to books."""
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    birth_year: int
    books: list["Book"] = ...  # Relationship to be implemented

class Book(SQLModel, table=True):
    """Book model with relationships to author and categories."""
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str
    publication_year: int
    author_id: Optional[int] = ...  # Foreign key to be implemented
    author: Optional[Author] = ...  # Relationship to be implemented
    categories: list["Category"] = ...  # Many-to-many relationship to be implemented
    borrow_records: list["BorrowRecord"] = ...  # Relationship to be implemented

class Patron(SQLModel, table=True):
    """Patron (library member) model."""
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    email: str
    borrow_records: list["BorrowRecord"] = ...  # Relationship to be implemented

class BorrowRecord(SQLModel, table=True):
    """Record of a patron borrowing a book."""
    id: Optional[int] = Field(default=None, primary_key=True)
    borrow_date: date
    return_date: Optional[date] = None
    patron_id: int = ...  # Foreign key to be implemented
    book_id: int = ...  # Foreign key to be implemented
    patron: Patron = ...  # Relationship to be implemented
    book: Book = ...  # Relationship to be implemented

class Category(SQLModel, table=True):
    """Category model for organizing books."""
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    books: list[Book] = ...  # Many-to-many relationship to be implemented

class BookCategory(SQLModel, table=True):
    """Link table for many-to-many relationship between books and categories."""
    book_id: int = ...  # Foreign key to be implemented
    category_id: int = ...  # Foreign key to be implemented

Test Cases

Author-Book Relationship

• Creating an author and adding books through the relationship creates the association correctly. The author's books list contains the added books, and each book's author field references the author. @test

• Deleting an author sets the author_id to null for all their books (books remain in database). @test

Patron-BorrowRecord Relationship

• Creating a patron and borrow records through the relationship creates the association correctly. The patron's borrow_records list contains the records. @test

• Deleting a patron automatically deletes all their borrow records from the database. @test

Book-Category Many-to-Many

• A book can be associated with multiple categories, and a category can have multiple books. Navigation works in both directions. @test

• The BookCategory link table correctly stores the associations between books and categories. @test

Dependencies { .dependencies }

SQLModel { .dependency }

Provides database ORM functionality with relationship support.

@satisfied-by