Part 1 is here https://dev.to/muhammadnizamani/create-fastapi-app-like-pro-part-1-12pi

In this part, we will focus on designing the database. In part 3, I will demonstrate how to create the database and use an ORM with PostgreSQL via pgAdmin.

Note: Please spend ample time on the design and planning phase to ensure a smooth journey ahead.

Now we are going to create a backend for a car rental system. In this backend, users can rent cars and check the availability of cars for rent. This simple backend project is designed to help beginners understand how to securely design a database. We will use ORM (Object-Relational Mapping) with SQLAlchemy to achieve this.

We will design the database first and create an ER diagram to help the others to understand the database structure. I have created the ER diagram using PostgreSQL and PGAdmin. here it is

In this Entity-Relationship Diagram (ERD), there are three main tables: users, cars, and rentals.
Users Table:

The users table stores information about the users. It has the following columns:

• user_id: A unique identifier for each user (Primary Key).
• name: The name of the user.
• email: The user's email address, which must be unique.
• phone_number: The user's phone number.

Cars Table:

The cars table holds information about the cars available for rent. It includes:

• car_id: A unique identifier for each car (Primary Key).
• make: The make of the car (e.g., Toyota, Ford).
• model: The model of the car (e.g., Camry, Focus).
• year: The manufacturing year of the car.
• registration_number: A unique registration number for each car.
• available: A boolean indicating whether the car is available for rent (defaults to true).

Rentals Table:

The rentals table records information about the rental transactions. It consists of:

• rental_id: A unique identifier for each rental (Primary Key).
• user_id: A reference to the user who rented the car (Foreign Key).
• car_id: A reference to the car that was rented (Foreign Key).
• rental_start_date: The date when the rental period begins.
• rental_end_date: The date when the rental period ends (if applicable).

Relationships:

This ER diagram illustrates two key relationships:

** Users to Rentals*: A one-to-many relationship, where one user can have multiple rentals.
**Cars to Rentals*: A one-to-many relationship, where one car can be rented multiple times.

Design Rationale:

To design this schema, I began by considering the core functionality of the application: providing cars for rent to users. This led to the creation of two primary tables: users and cars.

Next, I considered the relationships:

• Since a user can rent multiple cars over time, the users table has a one-to-many relationship with the rentals table.
• Similarly, a car can be rented by multiple users at different times, establishing a one-to-many relationship between the cars table and the rentals table.

To link the users and cars tables, I created the rentals table, which acts as a bridge table. This table includes foreign keys referencing the users and cars tables, thus capturing the rental transactions and their details.

rest will be in part 3

this is my github
https://github.com/MuhammadNizamani
this is my squad on daily.dev
https://dly.to/DDuCCix3b4p
check code example on this repo and please give a start to my repo
https://github.com/MuhammadNizamani/Fastapidevto