Skip to content

Latest commit

 

History

History
51 lines (41 loc) · 2.08 KB

database-map.md

File metadata and controls

51 lines (41 loc) · 2.08 KB

Database Map

Overview

The application uses SQLite as its persistent data store (library.db). The schema is defined in sql/schema.sql.

Entity Relationships

erDiagram
    users ||--o{ issue_records : "borrows"
    books ||--o{ issue_records : "is_borrowed_in"
Loading

Tables

1. users

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

2. books

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

3. issue_records

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