library
Library Management
Which loans are overdue?
Track branches, members, book copies and loans, and find what's overdue.
Branches, patrons, books, the copies each branch holds and every checkout share one table. A patron's checkouts sit in their own partition, so one query returns everything they have on loan, and an index sorts those checkouts by due date to find overdue ones. A separate card item and a conditional write keep library card numbers unique.
The model
PatronCard
Uniqueness tracker for library card numbers - enables conditional writes
- pk
- CARD#<cardNumber>
- sk
- CARD#<cardNumber>
Attributes: PK (S), SK (S), cardNumber (S), patronId (S), issuedAt (S)
Branch
A physical library branch location with address and contact information
- pk
- BRANCH#<branchId>
- sk
- BRANCH#<branchId>
Attributes: PK (S), SK (S), branchId (S), name (S), address (S), city (S), phone (S), openingHours (M), createdAt (S)
Patron
A library member who can borrow books from any branch
- pk
- BRANCH#<branchId>
- sk
- PATRON#<patronId>
Attributes: PK (S), SK (S), GSI1PK (S), patronId (S), cardNumber (S), firstName (S), lastName (S), email (S), membershipTier (S), maxCheckouts (N), createdAt (S)
Checkout
Record of a borrowed book with due date tracking
- pk
- PATRON#<patronId>
- sk
- CHECKOUT#<checkoutId>
Attributes: PK (S), SK (S), GSI1PK (S), GSI1SK (S), checkoutId (S), patronId (S), copyId (S), isbn (S), title (S), checkoutDate (S), dueDate (S), returnedDate (S), renewalCount (N)
Book
A book in the catalogue identified by ISBN
- pk
- BOOK#<isbn>
- sk
- BOOK#<isbn>
Attributes: PK (S), SK (S), isbn (S), title (S), author (S), publisher (S), publishYear (N), genre (S), description (S), pageCount (N)
BookCopy
A physical copy of a book at a specific branch
- pk
- BRANCH#<branchId>
- sk
- COPY#<copyId>
Attributes: PK (S), SK (S), GSI2PK (S), GSI2SK (S), copyId (S), isbn (S), branchId (S), condition (S), status (S), acquiredDate (S)
Access patterns
- QueryGet all patrons for a branch
List all library members registered at a specific branch
- QueryGet patron's active checkouts
List all books currently checked out by a patron
- Query · GSI1Find overdue items
Query GSI1 with date condition to find all overdue checkouts
- Query · GSI2Check book availability across all branches
Find which branches have available copies of a book
- GetItemValidate card uniqueness
Check if a library card number is already in use
- QueryGet branch inventory
List all book copies at a specific branch
- QueryGet branch details
Fetch one branch's address, phone and opening hours.
- QueryGet book details by ISBN
Fetch a book's catalogue entry by its ISBN.
Design notes
PatronCard as separate itemtrade-off
Enables library card number uniqueness check via conditional write (attribute_not_exists) without requiring a GSI scan. Adds one extra item per patron but eliminates the need for a GSI just for uniqueness.
Checkouts under Patron PKwhy
Storing checkouts with PK=PATRON#<patronId> enables "get patron with all checkouts" in a single query using begins_with on the sort key. The alternative (checkouts under Branch) would require a GSI for this common access pattern.
BookCopy separate from Bookwhy
Physical copies are separate items from the catalogue entry. This enables tracking multiple copies per branch with individual status (available/checked_out) and condition, while the Book entity maintains catalogue metadata.
Denormalised title in Checkouttrade-off
The book title is duplicated in the Checkout item to avoid an extra GetItem call when displaying checkout lists. Trade-off: if the title changes (rare), checkouts will show the old title.
Alternative: Sparse index for overduealternative
Instead of querying GSI1 with date conditions, you could use a sparse index where only overdue items have the GSI key populated. This reduces GSI storage but requires an update when items become overdue.
Taught in the course
- The key is the address - GetItem needs the whole primary key: partition and sort.
- The sort key is a query language - Conditions on the sort key narrow a Query before it reads, for free.
- Model a one-to-many with one partition - A parent and its children share a partition key, so one Query fetches them together.
List all library members registered at a specific branch
Choose an access pattern above, or build your own request. See what comes back and what it costs.
Write transactions, streams, tags and TTL are among the operations this browser build leaves out. dynoxide's native build has them.