For an instructor lead, in-depth look at learning SQL click below.
If you are building or managing a parking service, you need a robust system to manage parking data. A well-structured database not only allows you to store and retrieve information but also enables you to analyze trends and make informed business decisions. SQL (Structured Query Language) is the standard language for database management and manipulation, and this post will guide you on how to use SQL to design a parking management system.
1. Database Structure
Before diving into the SQL code, it’s vital to plan your database structure. For a basic parking management system, we need four tables: ‘ParkingLot’, ‘Spot’, ‘Vehicle’, and ‘ParkingRecord’.
1.1. ParkingLot Table
|
1 2 3 4 5 6 |
CREATE TABLE ParkingLot ( lot_id INT PRIMARY KEY, location VARCHAR(50) ); |
The ‘ParkingLot’ table contains two columns: ‘lot_id’, which is the primary key, and ‘location’, which stores the location of the parking lot.
1.2. Spot Table
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Spot ( spot_id INT PRIMARY KEY, lot_id INT, spot_size INT, availability VARCHAR(20), FOREIGN KEY (lot_id) REFERENCES ParkingLot(lot_id) ); |
The ‘Spot’ table is linked to the ‘ParkingLot’ table through the foreign key ‘lot_id’. It includes the ‘spot_id’ as its primary key, ‘lot_id’ to identify which parking lot the spot belongs to, ‘spot_size’ to classify the size of the spot, and an ‘availability’ column to indicate if a spot is open or occupied.
1.3. Vehicle Table
|
1 2 3 4 5 6 7 |
CREATE TABLE Vehicle ( vehicle_id INT PRIMARY KEY, type VARCHAR(20), registration_no VARCHAR(50) ); |
The ‘Vehicle’ table stores information about the vehicles parked or to be parked. It has a primary key ‘vehicle_id’, a ‘type’ column for the car type, and ‘registration_no’ for the car’s registration number.
1.4. ParkingRecord Table
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE ParkingRecord ( record_id INT PRIMARY KEY, vehicle_id INT, spot_id INT, time_in DATETIME, time_out DATETIME, charge FLOAT, FOREIGN KEY (vehicle_id) REFERENCES Vehicle(vehicle_id), FOREIGN KEY (spot_id) REFERENCES Spot(spot_id) ); |
The ‘ParkingRecord’ table holds the parking instances. The primary key is the ‘record_id’. It also includes the ‘vehicle_id’ and ‘spot_id’ as foreign keys, ‘time_in’ and ‘time_out’ for the parking duration, and ‘charge’ for the parking cost.
2. Manipulating Data With SQL
With the database structure laid out, let’s look at how we can use SQL to manipulate our data.
2.1. Inserting Data
To insert data into our tables, we use the INSERT INTO statement. Here’s how you can do it:
|
1 2 3 4 |
INSERT INTO ParkingLot (lot_id, location) VALUES (1, 'Main Street'); |
This command inserts a parking lot with ‘lot_id’ of 1 and ‘location’ as ‘Main Street’.
2.2. Data retrieval (SELECT Statement)
To retrieve data from our tables, we use the SELECT statement. Let’s use the following command to retrieve all details from ‘ParkingLot’:
|
1 2 3 |
SELECT * FROM ParkingLot; |
Summary
This post provided you with the basic SQL knowledge required to build a parking management system. While it only covered the database setup and few basic commands, it’s worth noting that SQL is a powerful language that can help you execute sophisticated analyses and outputs once you get more comfortable with advanced functions and commands.
Whether you’re aspiring to become a data analytics professional or a business owner looking to manage the parking service, learning SQL is an excellent start!