Shuttle Bus Reservation System – Database Design (SQL)

1. Users Table

Stores user info and blacklist status.

CREATE TABLE Users (
    Id UUID PRIMARY KEY,
    Email VARCHAR(255) NOT NULL UNIQUE,
    FullName VARCHAR(255),
    ViolationCount INT DEFAULT 0,
    IsBlacklisted BOOLEAN DEFAULT FALSE,
    BlacklistedUntil TIMESTAMP NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Users (
    Id UUID PRIMARY KEY,
    Email VARCHAR(255) NOT NULL UNIQUE,
    FullName VARCHAR(255),
    ViolationCount INT DEFAULT 0,
    IsBlacklisted BOOLEAN DEFAULT FALSE,
    BlacklistedUntil TIMESTAMP NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

2. Routes Table

Stores route metadata (only 2 routes initially but scalable).

sqlCopyEditCREATE TABLE Routes (
    Id UUID PRIMARY KEY,
    Origin VARCHAR(100) NOT NULL,
    Destination VARCHAR(100) NOT NULL,
    DepartureTime TIME NOT NULL,
    IsActive BOOLEAN DEFAULT TRUE
);

Example Data:

Origin	Destination	DepartureTime
Petone	Porirua	07:30
Petone	Porirua	15:00
Porirua	Petone	07:40
Porirua	Petone	15:10

---

3. Reservations Table

Stores each shuttle booking with status tracking.

sqlCopyEditCREATE TABLE Reservations (
    Id UUID PRIMARY KEY,
    UserId UUID REFERENCES Users(Id) ON DELETE CASCADE,
    RouteId UUID REFERENCES Routes(Id),
    TravelDate DATE NOT NULL,
    Status VARCHAR(20) CHECK (Status IN ('Booked', 'Cancelled', 'CheckedIn', 'Expired')),
    QrCode VARCHAR(255), -- QR code string or path to image
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP
);

---

4. Violations Table

Tracks each instance of a user not showing up.

sqlCopyEditCREATE TABLE Violations (
    Id UUID PRIMARY KEY,
    UserId UUID REFERENCES Users(Id) ON DELETE CASCADE,
    ReservationId UUID REFERENCES Reservations(Id),
    Reason VARCHAR(100), -- e.g., 'No-show', 'Late cancel'
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

5. CheckInLogs Table

Tracks QR scans at self-service kiosks.

sqlCopyEditCREATE TABLE CheckInLogs (
    Id UUID PRIMARY KEY,
    ReservationId UUID REFERENCES Reservations(Id),
    UserId UUID REFERENCES Users(Id),
    ScannedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Location VARCHAR(100)
);

---

6. Scheduled Tasks (Handled via Backend Jobs)

Backend or cron jobs update statuses:

• Mark unscanned reservations as Expired
• Update ViolationCount in Users
• Move users to IsBlacklisted = TRUE if violation count ≥ 3
• Reset blacklist after BlacklistedUntil

---

Notes

• UUID: Use uuid_generate_v4() in PostgreSQL or NEWID() in SQL Server.
• Add indexes on UserId, TravelDate, and Status for performance.
• Consider RouteId + TravelDate uniqueness to prevent overbooking (if seat limits added).
• QR code string can be base64 or stored as reference to a static file.