schema.sql

/*
Stadium Management System Schema
------------------------------

This schema defines the core tables for a stadium management system, focusing on:
- User management and loyalty tracking
- Event scheduling and categorization
- Ticket sales and seat allocation
- Concession sales tracking
- Gate entry monitoring

Design Considerations:
- All monetary values use DECIMAL(10,2) for precise currency handling
- Timestamps track all temporal data
- CHECK constraints ensure data integrity
- UNIQUE constraints prevent duplicate records
- Foreign keys maintain referential integrity
*/

-- Clean up existing tables if they exist
DROP TABLE IF EXISTS gate_entries;
DROP TABLE IF EXISTS concession_transactions;
DROP TABLE IF EXISTS tickets;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS users;

-- Users table: Stores customer information and loyalty program data
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20),
    loyalty_points INTEGER DEFAULT 0 CHECK (loyalty_points >= 0)
);

-- Events table: Manages all stadium events (games, concerts, etc.)
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    event_date DATE NOT NULL CHECK (event_date >= CURRENT_DATE),
    event_type VARCHAR(100) NOT NULL
);

-- Tickets table: Tracks ticket sales and seat assignments
CREATE TABLE tickets (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users (id),
    event_id INTEGER REFERENCES events (id),
    seat_number VARCHAR(10) NOT NULL,  -- Format: S01R01 (Section 01, Row 01)
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    purchased_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (event_id, seat_number)  -- Prevents double-booking of seats
);

-- Concession transactions: Records food and beverage purchases
CREATE TABLE concession_transactions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users (id),
    event_id INTEGER REFERENCES events (id),
    item_name TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_each DECIMAL(10, 2) NOT NULL CHECK (price_each >= 0),
    transaction_time TIMESTAMP DEFAULT NOW()
);

-- Gate entries: Tracks stadium entry points and timing
CREATE TABLE gate_entries (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users (id),
    event_id INTEGER REFERENCES events (id),
    gate_number INTEGER NOT NULL CHECK (gate_number BETWEEN 1 AND 100),
    entry_time TIMESTAMP DEFAULT NOW(),
    UNIQUE (user_id, event_id, entry_time)  -- Prevents duplicate gate scans
);

/*
Table Relationships:
------------------
1. Users to Tickets: One-to-Many
   - A user can purchase multiple tickets
   - Each ticket belongs to one user

2. Users to Concessions: One-to-Many
   - A user can make multiple concession purchases
   - Each purchase is linked to one user

3. Users to Gate Entries: One-to-Many
   - A user can enter through gates multiple times
   - Each entry is linked to one user

4. Events to Tickets: One-to-Many
   - An event can have multiple tickets
   - Each ticket is for one specific event

5. Events to Concessions: One-to-Many
   - An event can have multiple concession sales
   - Each sale is linked to one event

6. Events to Gate Entries: One-to-Many
   - An event can have multiple gate entries
   - Each entry is for one specific event

Data Integrity Rules:
-------------------
1. Prices cannot be negative
2. Loyalty points cannot be negative
3. Event dates must be in the future
4. Gate numbers must be between 1 and 100
5. Concession quantities must be positive
6. Seats cannot be double-booked
7. Users cannot have duplicate gate entries
*/