Database Design for Parking

Posted on: March 12, 2025 Posted by: rahulgite Comments: 0

Design a database system for a multi-floor car parking building where:

Users can check the number of available parking slots on each floor in real time.
When a vehicle enters, the system should:
- Assign the first available slot.
- Record the vehicle’s registration number, floor, and slot location.
- Maintain an entry log.
When a vehicle exits, the system should:
- Free the corresponding slot.
- Update the available slot count.
- Log the exit time.
Ensure data consistency during concurrent vehicle entries and exits.
Optimize for fast read and write operations to handle high traffic.

The system should support both a normalized design for maintaining data integrity and a denormalized design for faster access and scalability.

1. Normalized Database Design

In a normalized design, we follow 3NF (Third Normal Form) to minimize data redundancy and ensure data integrity.

Schema (Normalized Design)

Building: Stores building-level information.

CREATE TABLE building (
    building_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Floor: Stores information about each floor.

CREATE TABLE floor (
    floor_id SERIAL PRIMARY KEY,
    building_id INT NOT NULL,
    floor_number INT NOT NULL,
    total_slots INT NOT NULL,
    available_slots INT NOT NULL,
    FOREIGN KEY (building_id) REFERENCES building(building_id)
);

Slot: Stores individual parking slots.

CREATE TABLE slot (
    slot_id SERIAL PRIMARY KEY,
    floor_id INT NOT NULL,
    slot_number INT NOT NULL,
    is_occupied BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (floor_id) REFERENCES floor(floor_id)
);

Vehicle: Stores vehicle details when parked.

CREATE TABLE vehicle (
    vehicle_number VARCHAR(20) PRIMARY KEY,
    slot_id INT UNIQUE,
    owner_name VARCHAR(100),
    entry_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (slot_id) REFERENCES slot(slot_id)
);

Parking Logs: Stores entry and exit logs.

CREATE TABLE parking_logs (
    log_id SERIAL PRIMARY KEY,
    vehicle_number VARCHAR(20),
    floor_id INT,
    slot_id INT,
    action_type VARCHAR(10) CHECK (action_type IN ('ENTRY', 'EXIT')),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Key Queries (Normalized Design)

Find Available Slots on Each Floor

SELECT f.floor_number, COUNT(*) AS available_slots
FROM floor f
JOIN slot s ON f.floor_id = s.floor_id
WHERE s.is_occupied = FALSE
GROUP BY f.floor_number;

Park a Vehicle (Transaction for Consistency)

BEGIN;

-- Find the first available slot
WITH available_slot AS (
    SELECT s.slot_id, f.floor_id
    FROM slot s
    JOIN floor f ON s.floor_id = f.floor_id
    WHERE s.is_occupied = FALSE
    ORDER BY f.floor_number, s.slot_number
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
-- Mark slot as occupied
UPDATE slot
SET is_occupied = TRUE
WHERE slot_id = (SELECT slot_id FROM available_slot);

-- Insert vehicle record
INSERT INTO vehicle (vehicle_number, slot_id, owner_name)
VALUES ('ABC123', (SELECT slot_id FROM available_slot), 'John Doe');

-- Log entry
INSERT INTO parking_logs (vehicle_number, floor_id, slot_id, action_type)
VALUES ('ABC123', (SELECT floor_id FROM available_slot), (SELECT slot_id FROM available_slot), 'ENTRY');

COMMIT;

Find Vehicle Location

SELECT f.floor_number, s.slot_number
FROM vehicle v
JOIN slot s ON v.slot_id = s.slot_id
JOIN floor f ON s.floor_id = f.floor_id
WHERE v.vehicle_number = 'ABC123';

Trade-offs (Normalized Design)

✅ Pros:

Data Integrity: Ensures data consistency via normalization.
Reduces Redundancy: Avoids duplicate information.
Easier Maintenance: Update and delete operations are simple.

❌ Cons:

Performance: Requires JOIN operations across tables.
Complex Queries: Requires transactions for atomic updates.
Slower Reads: Especially for real-time slot availability checks.

2. Denormalized Database Design

In a denormalized design, we combine related tables to improve read performance by reducing joins.

Schema (Denormalized Design)

Parking Status: Stores all slot and vehicle information in a single table.

CREATE TABLE parking_status (
    id SERIAL PRIMARY KEY,
    building_id INT NOT NULL,
    floor_number INT NOT NULL,
    slot_number INT NOT NULL,
    vehicle_number VARCHAR(20) UNIQUE, -- NULL if empty
    owner_name VARCHAR(100),
    is_occupied BOOLEAN NOT NULL DEFAULT FALSE,
    entry_time TIMESTAMP
);

Parking Logs: Logs vehicle entry and exit.

CREATE TABLE parking_logs (
    log_id SERIAL PRIMARY KEY,
    vehicle_number VARCHAR(20),
    floor_number INT,
    slot_number INT,
    action_type VARCHAR(10) CHECK (action_type IN ('ENTRY', 'EXIT')),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Key Queries (Denormalized Design)

Find Available Slots on Each Floor

SELECT floor_number, COUNT(*) AS available_slots
FROM parking_status
WHERE is_occupied = FALSE
GROUP BY floor_number;

Park a Vehicle (Fast Atomic Update)

WITH available_slot AS (
    SELECT id FROM parking_status
    WHERE is_occupied = FALSE
    ORDER BY floor_number, slot_number
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE parking_status
SET vehicle_number = 'ABC123', owner_name = 'John Doe', is_occupied = TRUE, entry_time = CURRENT_TIMESTAMP
WHERE id = (SELECT id FROM available_slot)
RETURNING floor_number, slot_number;

-- Log entry
INSERT INTO parking_logs (vehicle_number, floor_number, slot_number, action_type)
VALUES ('ABC123', 1, 101, 'ENTRY');

Find Vehicle Location

SELECT floor_number, slot_number
FROM parking_status
WHERE vehicle_number = 'ABC123';

Trade-offs (Denormalized Design)