Car Dealership System ERD (With Database Structure)

Welcome to a sharing of an ERD of a car dealership system. So you have taken on a project for a car dealership, or have to deal with this as a school assignment? Well, here is a sharing of my simple version – Read on!

 

 

TABLE OF CONTENTS

 

 

CAR DEALERSHIP SYSTEM ERD

To keep things simple, I have grouped this system into 4 sections.

• Cars – Captain Obvious at your service.
• Users – Staff members and customers.
• Appointments & Test Drives – Before sales.
• Sales – Sales & transactions.

 

FOR REFERENCE ONLY

I can hear the “true professionals” banging on their keyboards. “This is so stupid”, “this is inaccurate”, “I can do better”. By all means, this is only a simple example for your reference.

• A car can be sold multiple times – That can be true for a second-hand dealership. If that is not the case, change it on your own.
• A sale can have no transactions. If a deposit must be made first, change it on your own.
• If you want to add “car manufacturers”, go ahead and do so.
• If you don’t allow a test drive, go ahead and remove it.

This list can go on forever. You are making your own system now, you have to decide your own rules.

 

 

 

PART 1) CARS

COLUMN	DATA TYPE	NOTES
car_vin	VARCHAR	Primary key. For the uninitiated, every legal vehicle has a unique vehicle identification number.
car_status	VARCHAR	Up to you to decide. Maybe “in-store”, “repair”, or “sold”.
car_maker	VARCHAR	Car maker. Toyota, Honda, BMW, Peugeot, etc…
car_model	VARCHAR	Car model.
car_type	VARCHAR	SUV, MPV, sedan, coupe, convertible, etc…
car_capacity	VARCHAR	Engine capacity.
car_description	VARCHAR	A short description of the vehicle.
car_price	DECIMAL	Selling price.

To address the elephant in the room. A car dealership definitely has a stock of vehicles… Feel free to add more information as required.

 

PART 2) USERS

2A) STAFF

COLUMN	DATA TYPE	NOTES
staff_id	INTEGER	Primary key.
staff_type	VARCHAR	Up to you to decide – “M”anager, “S”ales, “A”ccountant, etc…
staff_name	VARCHAR	The staff’s full name.
staff_email	VARCHAR	The staff’s email. Set this to unique to prevent duplicates.
staff_password	VARCHAR	The login password.

This should be self-explanatory, the internal staff members.

 

 

2B) CUSTOMERS

COLUMN	DATA TYPE	NOTES
customer_id	INTEGER	Primary key.
customer_name	VARCHAR	Customer’s name.
customer_email	VARCHAR	Customer’s email.
customer_tel	VARCHAR	Customer’s telephone number.
customer_address	VARCHAR	Customer’s address, if any.
customer_license	VARCHAR	Customer’s driver license, if any.

I usually like to keep a single “user table” where possible, but we are keeping two here. That is because customers have a somewhat different dataset than the internal staff. Well, go ahead and merge them if it makes sense in your project.

 

PART 3) PRE-SALES

3A) APPOINTMENT

COLUMN	DATA TYPE	NOTES
appointment_id	INTEGER	Primary key.
appointment_date	DATE TIME	Time of appointment.
appointment_notes	VARCHAR	Appointment notes, if any.
customer_id	INTEGER	Customer making the appointment, if any.
staff_id	INTEGER	Staff serving the customer.

Take note, an appointment is not sales.

• Customers can call ahead to “book” a test drive with certain car models, or just to take a look.
• An appointment can also be a session to settle a car loan or insurance.

 

 

3B) TEST DRIVE

COLUMN	DATA TYPE	NOTES
car_vin	VARCHAR	Primary and foreign key.
test_start	DATE TIME	Primary key, timestamp. Start of the test drive.
test_end	DATE TIME	Timestamp, end of the test drive.
test_notes	VARCHAR	Notes, if any.
staff_id	INTEGER	Foreign key. Staff in charge of the test drive.
customer_id	INTEGER	Foreign key. Customer doing the test drive.
appointment_id	INTEGER	Foreign key, if any. This is a test drive during this appointment.

To keep track of who drove which vehicle.

 

PART 4) SALES

4A) SALES

COLUMN	DATA TYPE	NOTES
sale_id	INTEGER	Primary key.
sale_date	DATE TIME	Sale date and time.
sale_price	DECIMAL	Sale price.
sale_status	VARCHAR	Up to you to decide. Maybe “deposit paid”, “installment”, “fully paid”.
sale_notes	VARCHAR	Notes, if any.
car_vin	VARCHAR	Foreign key. Car that is being sold.
staff_id	INTEGER	Foreign key. Staff who sold the vehicle.
customer_id	INTEGER	Foreign key. Customer who bought the vehicle.

Which customer purchased which vehicle, and which staff sold it.

 

4B) TRANSACTIONS

COLUMN	DATA TYPE	NOTES
tx_id	INTEGER	Primary key.
tx_amount	DECIMAL	Transaction amount.
tx_type	VARCHAR	You decide – Cash, credit card, coupon, etc…
tx_notes	VARCHAR	Notes, if any.
sale_id	INTEGER	Foreign key. This transaction is related to this sale, if any.
staff_id	INTEGER	Staff who made this transaction, if any.

Finally, somewhere to store the transaction history. This one crosses into a “financial system”, and I kind of want to leave it out… You decide.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart