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
First, here is the entire ER diagram and a quick overview of the system.
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) CARS
|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_type||VARCHAR||SUV, MPV, sedan, coupe, convertible, etc…|
|car_description||VARCHAR||A short description of the vehicle.|
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
|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.
|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
|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
|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
|sale_date||DATE TIME||Sale date and time.|
|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.
|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.
EXTRA BITS & LINKS
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
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
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!