How to design a database

Databases are core to our applications; they store, manage, and retrieve data. Therefore, efficient database design is crucial, We will go through step by step to illustrate the thought process involved in building a database using a simple example.

These are the 7 steps we will cover:

Define the Application Scope: Document the detailed requirements and objectives of the application.
Identify Entities: Extract key nouns from the application scope, representing the core entities of the database.
Design Tables: Create database tables corresponding to the identified entities.
Define Attributes: Specify the attributes (columns) for each table, including data types and constraints.
Establish Relationships: Define the relationships between tables (e.g., one-to-one, one-to-many, many-to-many) using primary and foreign keys.
Review and Refine Schema: Evaluate the database schema for accuracy, completeness, and efficiency, making necessary adjustments.
Normalize Database: Apply normalization techniques (e.g., 1NF, 2NF, 3NF) to reduce data redundancy and improve data integrity, if required.

Step 1 : Define the Application Scope

For this example, we will model a parking lot management system. Outlining the core functionality our application must have.

PARKING LOT

Users must be able to reserve available parking spaces for their vehicles, and the system should support monthly subscription options for recurring parking needs. Furthermore, the system will provide parking duration tracking, logging the duration for which a vehicle occupies a specific parking space. Finally, the system will provide real-time space occupancy monitoring, enabling users to know which vehicle is parked in each space.

Step 2 : Identify Entities

In this phase, we identify the key entities that will form the basis of our database design. For that we need to list down nouns present in our application description, _nouns are simply objects or things we mentioned in our application scope.

Users must be able to reserve available parking spaces for their vehicles, and the system should support monthly subscription options for recurring parking needs. Furthermore, the system will provide parking duration tracking, logging the duration for which a vehicle occupies a specific parking space. Finally, the system will provide real-time space occupancy monitoring, enabling users to know which vehicle is parked in each space.

Our entities will be

users
parking space
vehicles
monthly subscription

To store booking information need another we need another entity

reservation

Step 3 : Design Tables

Based on the entities identified in the previous step, we will now design the corresponding database tables. The entities User , Parking Space, Vehicle , reservation and Monthly Subscription will each be represented by a dedicated table.

STEP 4 : Define Attributes

In this step, we define the attributes (columns) for each table, based on the application scope defined in Step 1. This process involves a combination of requirements analysis and where necessary, informed assumptions. It is an iterative process, subject to refinement as the design progresses.

Users must be able to reserve available parking spaces for their vehicles, and the system should support monthly subscription options for recurring parking needs.
Furthermore, the system will provide parking duration tracking*, logging the duration for which a vehicle occupies a specific parking space. Finally, the system will provide real-time space occupancy monitoring, enabling users to know which vehicle is parked in each space.*

Given the application scope, which includes user reservations, monthly subscriptions, vehicle tracking, and parking space management, we define the following attributes for our database tables.

This is an initial attribute list, and further refinements can be done.

User Table:

user_id (Primary Key, Integer)
email (String, Unique)
name (String)
phone_number (String)

Parking Space Table:

parking_space_id (Primary Key, Integer)
parking_space_number (Integer)
floor_number (Integer)
price_per_hour (Decimal)
is_occupied (Boolean)

Vehicle Table

vehicle_id (Primary Key, Integer)
owner_id (Foreign Key referencing User table, Integer)
license_plate (String, Unique)
subscription_id (Primary Key, Integer)

Subscription Table

user_id (Foreign Key referencing User table, Integer)
start_date (Date)
end_date (Date)
subscription_type (String)

Reservation Table

reservation_id (Primary Key, Integer)
user_id (Foreign Key referencing User table, Integer)
parking_space_id (Foreign Key referencing Parking Space table, Integer)
vehicle_id (Foreign Key referencing Vehicle Table, Integer)
reservation_start_time (DateTime)
reservation_end_time (DateTime)
actual_start_time (DateTime)
actual_end_time (DateTime)
reservation_status (String) // e.g., "Booked", "Active", "Completed", "Cancelled"

STEP 5 : Establish Relationships

We establish relationships by considering our application's description.

User - Vehicle (One-to-Many): One user can own multiple vehicles.
User - Subscription (One-to-Many): One user can have multiple subscriptions.
User - Reservation (One-to-Many): One user can make multiple reservations.
Parking Space - Reservation (One-to-Many): One parking space can be reserved multiple times
Vehicle - Reservation (One-to-Many): One vehicle can be used for multiple reservations.

STEP 6 : Review and Refine Schema

During this step, we evaluate and refine our database schema to ensure it accurately reflects the application's requirements and optimizes for efficiency. In our parking lot management system, we've identified a need to differentiate parking spaces and pricing based on vehicle type.

We need to add a parking_type attribute to indicate the type of parking space (e.g. Compact, Standard, Large, Motorcycle).
We should also add a vehicle_type attribute. This attribute will indicate what type of vehicle can park in the specific parking spot.

STEP 7 : Normalize Database

The final step in our database design process is normalization. Normalization involves applying a series of rules to organize data in a database, reducing redundancy and improving data integrity. This typically involves breaking down large tables into smaller, more manageable ones and defining relationships between them. Techniques like 1NF, 2NF, and 3NF are used to achieve this.

Whether or not normalization is strictly required depends on the complexity and scale of your application. For our parking lot management system, further normalization might be needed to address potential redundancies or improve data consistency, especially as the system scales. However, I won't be performing a full normalization process in this article. If your application requires it, you can apply normalization techniques to the tables we have designed.

The concepts presented in this article were inspired by the How to Design a Database by Database Star, I highly recommend checking out their channel, along with Piyush Garg and Hitesh Choudary

How to design a Database