Step 3: Relationships Explained (Parking Lot)

1. Multi-Level Physical Hierarchy

To represent the physical parking architecture, we enforce cascading one-to-many relationships:

• A parking_lot hosts multiple parking_floors (one-to-many: lots.lot_id ➡️ floors.lot_id).
• Each parking_floor contains many individual parking_spots (one-to-many: floors.floor_id ➡️ spots.floor_id).

This structure guarantees that we can aggregate active occupancy statistics at the specific floor level as well as across the entire garage structure instantly.

2. Spot Allocation & Session Lifecycles

When a driver pulls up to an entry terminal and triggers ticket generation:

1. The system queries available spaces matching their vehicle size class.
2. A single parking_spots record status is flagged as 'OCCUPIED'.
3. A new tickets record is initialized, storing the assigned spot_id and the entry_time timestamp.

-- Atomic spot state transition upon entry
UPDATE parking_spots
SET status = 'OCCUPIED'
WHERE spot_id = ? AND status = 'AVAILABLE';

3. Dynamic Billing Invoicing

Upon vehicle exit, the database calculates total elapsed minutes:

• elapsed_hours = CEIL(EXTRACT(EPOCH FROM (exit_time - entry_time)) / 3600)
• The calculated value is matched against the specific parking_rates slab configured for the target lot_id and matching spot_type.
• The final sum is stored in tickets.amount_charged and must be matched by a corresponding entry in payments with a unique transaction reference before the exit boom-gate is commanded to open.

This content is reserved for Premium Members.