Constructing a Data Model for a Parking Lot Management System

Research shows that cars remain parked for 95% of their lifetime, suggesting that parking lot management systems should be smart, efficient, and robust. In this article, we’ll construct a data model for such a system.

Introduction

Before we begin constructing our data model, we should first understand how parking lots are structured and how they operate. Let’s take a brief look at these two key areas.

1. How are parking lots structured?

   A typical parking lot consists of one or more blocks that are further subdivided into floors. Each floor contains multiple wings that help drivers orient themselves and remember their parking spots. These are usually labeled with letters, such as “A”, “B”, “C”, and so on. A floor usually has a height limit that restricts certain vehicles from entering the parking lot. Additionally, a floor contains several uniquely numbered parking slots. Some of these slots are reserved for handicapped people; others can be reserved by regular visitors at a certain cost.

2. How do parking lots operate?

   To understand how parking lots operate, we must know more about the types of people who visit parking lots. Customers who enter parking lots belong to one of the following groups:

   • A regular customer who has purchased a biweekly, monthly, or yearly pass.
   • A prepaid customer who booked a slot remotely (on the phone or online).
   • A walk-in customer who neither has a pass nor booked a slot remotely. A slot will be assigned to such a customer based on availability.

   Regular customers are usually given cards/stickers to place somewhere visible on their dashboard or windshield so the parking lot management can easily determine that the customers are not in violation of any parking rules. Unlike occasional visitors, regular customers are never issued parking slips on a daily basis. A parking lot typically reserves an entire block or floor for its regular visitors to ensure they always have places to park. Regular customers may also reserve slots for themselves so they can park their vehicles in the same designated slots every day, but this typically costs extra.

   Those who make remote parking reservations may typically only use their designated slots for a limited time window of a couple hours, after which the slots are freed. When these visitors enter the parking lot, they must park in their reserved slots. A penalty is charged to customers who do not leave the parking lot after their time windows elapse, but customers can certainly leave before their reservations expire. Some parking lots have a fixed minimum time window (e.g., the customer may have to book a slot for three hours even if they are only going to be gone for one hour).

   Walk-in customers are given parking slips when they enter a parking lot. A parking slot is then assigned to the customer as the slip is generated, based on preferences they have specified. The reservation process here is essentially the same as the one for prepaid customers. However, a walk-in reservation depends entirely on availability. A slot may cost you more than if you were to reserve a spot ahead of time, especially if there is limited availability and high demand.

Data Model

With these requirements in mind, let’s go ahead and create our data model. This time, we’ll be working with three main sections:

• Parking lot
• Customer
• Parking reservation

Let’s take a closer look at each of these areas of our data model.

Section 1: Parking Lot

The Parking Lot section not only captures all important information about the parking lot itself but also simplifies the manner in which the smallest unit of the parking lot (a slot) can be managed by the company. Some table columns have been added for the sole purpose of making parking reservations and operations more efficient in later sections.

In accordance with the parking lot structure we discussed in the introduction, we’ve created the following tables to capture each and every detail we’ll need.

parking_lot – stores basic information about a parking lot. The columns for this table are:

• id – the primary key for this table. It assigns a unique number to each parking lot.
• number_of_blocks – tracks the number of blocks in a parking lot.
• is_slot_available – signifies whether the parking lot currently has any available slots.
• address – stores the complete address of a parking lot.
• zip – stores the zip code of a parking lot, allowing customers to more easily search for available parking lots within a certain area by simply querying their desired zip code.
• is_reentry_allowed – signifies whether a customer may exit the parking lot and re-enter with the same parking slip. Note that many parking lots typically don’t allow customers to do this. In such parking lots, you must purchase a new slip every time you re-enter on a given day.
• operating_company_name – stores the name of the company that operates the parking lot.
• is_valet_parking_available – signifies whether the parking lot offers valet parking services.

block – a parking lot is divided into one or more blocks. This table stores information about each block of a parking lot. The columns for this table are: – a parking lot is divided into one or more blocks. This table stores information about each block of a parking lot. The columns for this table are:

• id – the primary key for this table.
• parking_lot_id – the referenced column from the parking_lot table that identifies the parking lot to which the block belongs.
• block_code – stores the code associated with this block. Blocks are usually given uniquely identifying codes, such as “A”, “B”, “C”, “11”, “22”, “33”, and so on.
• number_of_floors – stores the number of floors in this block. The number “1” indicates that this is a ground-level block with no floors.
• is_block_full – signifies whether the block is currently full.

floor – in multi-level parking lots, blocks can have more than one floor. However, this table can also be referenced by ground-level blocks. The columns for this table are:

• id – the primary key for this table.
• block_id – identifies the block to which a floor belongs.
• floor_number – represents the number of a floor (where 1 = ground level).
• max_height_in_inch – in a multi-level parking lot, each floor has a height constraint. This column stores the maximum permissible height for vehicles on a floor.
• number_of_wings – a floor is further divided into wings, which help customers remember where they parked. This column stores the number of wings that exist on a floor.
• number_of_slots – stores the number of slots that exist on a floor.
• is_covered – identifies whether a floor is covered. The top floor of a multi-level parking lot or a ground-level parking lot will never be covered.
• is_accessible – indicates whether the floor is easily accessible, especially by the handicapped. If a multi-level lot has an operational elevator, each of its floors is considered to be accessible.
• is_floor_full – indicates whether a floor is fully occupied.
• is_reserved_reg_cust – indicates whether a floor is strictly reserved for regular customers.

parking_slot – this table stores all information about the parking slots of a parking lot. The columns for this table are:

• id – primary key for this table.
• floor_id – identifies the floor to which a slot belongs.
• slot_number – stores the unique identifier of the slot on a particular floor.
• wing_code – identifies the wing in which a slot is located.

Section 2: Customers

Moving on, we’ll now begin detailing all relevant information about customers. Note that parking lots are not concerned with capturing and storing personal information like names, addresses, etc., as they can access their local DMV portals at any time to obtain such information, if needed.

customer – stores all relevant details about all kinds of customers who may visit the parking lot (regular, one time, and prepaid). The columns for this table are:

• id – unique identifier for the customer.
• vehicle_number – stores the license plate number of a customer’s vehicle.
• registration_date – stores the date when the vehicle was first registered with the parking lot.
• is_regular_customer – indicates whether a customer has regular pass. If the column stores a value of true, then there must exist a valid entry in the regular_pass table. Once a pass expires and the customer has not yet renewed it, the value in this column is updated to false.
• contact_number – stores a customer’s contact number. Since some people are reluctant to share their contact numbers with parking lots, we’ve kept this column nullable.

regular_pass – stores information about regular passes that are issued to customers. The columns for this table are:

• id – primary key for this table.
• customer_id – a referenced column from the customer table.
• purchase_date – stores the date on which the pass was purchased.
• start_date – stores the date on which the pass will be considered valid, which may not necessarily be the date of purchase, as some customers purchase passes in advance.
• duration_in_days – stores the number of days for which a pass is valid. A monthly pass usually remains valid for 30 days.
• cost – stores the cost, in local currency, that a customer must pay to purchase a pass.

Section 3: Reservations

Our last section is dedicated to detailing the parking slot reservation process. When placing a reservation, a customer must typically provide certain details, such as their expected date and time of arrival, the amount of time for which they’d like to reserve the slot, and so on. We discuss the two main tables of this section below.

parking_slot_reservation – maintains reservation details. The columns for this table are:

• id – assigns a unique reference number to an individual reservation request.
• customer_id – reference to the identifier of the customer who is making this reservation.
• start_timestamp – stores the expected date and time of the customer’s arrival.
• duration_in_minutes – stores the duration for which the reservation was made.
• booking_date – stores the date on which the reservation was made.
• parking_slot_id – internal column that assigns a parking slot to a customer once their request is captured and the payment has been made.

parking_slip – stores information about the customer’ entry and exit times, as well as any relevant fees. We created this table for parking lots that allow multiple entries and exits under the same reservation. The columns for this table are:

• id – the primary key for this table.
• parking_slot_reservation_id – referenced column that identifies the associated reservation request.
• actual_entry_time – stores the customer’s arrival date and timestamp.
• actual_exit_time – stores the customer’s departure (exit) date and timestamp.
• basic_cost – stores the basic cost of the reservation.
• penalty – stores a value of 0 by default. If a customer delays their exit, a penalty fee will be applied, and the value in this column will be updated.
• total_cost – this column merely adds the values of the basic_cost and penalty columns.
• is_paid – re-entry is usually permitted only when a customer has paid their parking fee. This column denotes whether this payment has been made.

Conclusion

In this article, we presented an overview of a data model for a parking lot management system. There are many apps that help users find parking spaces by extracting, processing, and compiling data (such as availability and costs) for parking lots in a specified vicinity. This is especially useful for people visiting big cities like New York, Los Angeles, and others where finding a parking lot can be a nightmare if you don’t plan your visit carefully. Such applications rely on well-designed data models and databases APIs to retrieve this information.

In our next article, we’ll transform our current data model into a solution for a real-time parking availability system. Feel free to post your thoughts, feedback, and recommendations in the comments section below.