The application uses SQLite as its persistent data store (library.db). The schema is defined in sql/schema.sql.
erDiagram
users ||--o{ issue_records : "borrows"
books ||--o{ issue_records : "is_borrowed_in"
Stores information about everyone who can log in (Admins and Students).
| Field | Type | Description | constraints |
|---|---|---|---|
user_id |
INTEGER | Primary Identifier | PRIMARY KEY AUTOINCREMENT |
username |
TEXT | Login name | UNIQUE, NOT NULL |
password_hash |
TEXT | Hashed password via BCrypt logic | NOT NULL |
role |
TEXT | User role | CHECK(role IN ('ADMIN', 'STUDENT')), NOT NULL |
created_at |
TIMESTAMP | Registration time | DEFAULT CURRENT_TIMESTAMP |
Stores the catalog of books in the library.
| Field | Type | Description | constraints |
|---|---|---|---|
book_id |
INTEGER | Primary Identifier | PRIMARY KEY AUTOINCREMENT |
isbn |
TEXT | Global barcode number | UNIQUE, NOT NULL |
title |
TEXT | Book Title | NOT NULL |
author |
TEXT | Book Author | NOT NULL |
publisher |
TEXT | Book Publisher | (Optional) |
total_copies |
INTEGER | Total copies owned | NOT NULL DEFAULT 0 |
available_copies |
INTEGER | Copies currently on shelf | NOT NULL DEFAULT 0 |
Tracks the history of who borrowed what.
| Field | Type | Description | constraints |
|---|---|---|---|
issue_id |
INTEGER | Primary Identifier | PRIMARY KEY AUTOINCREMENT |
user_id |
INTEGER | User who borrowed the book | NOT NULL, FK to users(user_id) |
book_id |
INTEGER | Book that was borrowed | NOT NULL, FK to books(book_id) |
issue_date |
TIMESTAMP | Checkout date | DEFAULT CURRENT_TIMESTAMP |
due_date |
TIMESTAMP | When it should be returned | (Optional) |
return_date |
TIMESTAMP | When it was actually returned | NULL if currently borrowed |
status |
TEXT | Borrow status | CHECK(status IN ('ISSUED', 'RETURNED')), NOT NULL |