Car Dealership System ERD (With Database Structure)

Last Updated: November 10, 2023

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

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

CAR DEALERSHIP SYSTEM ERD

Car Dealership System ERD (click to enlarge)

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.

 

 

TABLE STRUCTURE

Now that we are done with the overview, let us get into more details on the entities or tables.

 

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.

 

EXTRAS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!