Automobile Repair Shop Data Model

Running an automobile/car repair shop is a really complex business. You’ll need to make appointments while some customers will drive in and you don’t want to have them wait for hours. Also, you’ll need to organize employees, track repairs, materials, charge customers, etc. You’ll definitely need an IT solution and, of course, a data model in the background. Today we’ll talk about one such model.

The Idea

I’ve already mentioned that this business model is really complex. Therefore, I won’t try to cover everything. I’ve intentionally omitted tracking materials and spare parts and also simplified some parts of the model. The reason for that is pretty simple. If I’ve included really everything, the model would simply be too large for an article of the reasonable size. So, let’s start.

Data Model

The model consists of 5 subject areas:

• Repair shops & employees
• Customers & contacts
• Vehicles
• Services & offers and
• Visits

We’ll describe each of these 5 subject areas in the order they were listed.

Section 1: Repair shops & employees

The first subject area, we’ll begin with is the Repair shops & employees subject area. It’s pretty obvious that we need to know what we have at disposal before we can make offers to customers.

The city dictionary is used to store all distinct cities where we have repair shops or our customers come from. Each city is uniquely defined by the pair postal_code – city_name. We could decide to have only one entry per each city, even if that city has multiple postal codes. In that case, we would use only the “main” postal code for that city. Still, we have an option to have multiple entries for the same city and different postal codes – in case we want that.

The repair_shop table is the place where we’ll store a list of all our repair shops. We can expect that we’ll operate more than one at some point. Each shop is uniquely defined by its shop_name and the id of the city it belongs to (city_id). We’ll also store the shop’s address and additional details in the textual format if any.

The position dictionary is used to store unique position_names that could be assigned to our employees. While most position shall be related to our core business, we’ll also have some that are not part of the core business (technical roles/positions) but are also essential (administration, sales, etc.).

A list of all our employees is stored in the employee table. For each employee, we’ll store his:

• first_name & last_name – The first and the last name of the employee.
• employment_start_date & employment_end_date – Employee’s start and end date in the company. The end date shall contain NULL value until we can define it.
• position_id – A reference to the current position in the company.
• city_id – A reference to the city where the employee currently lives.
• is_active – A flag denoting if the employee is currently active or not.

The last table in this subject area is the schedule table. In this table, we’ll store exact schedules for all our employees on a daily level. We’ll also have the option to store multiple intervals for the same employee during the same day. In order to achieve this, we’ll use the following attributes:

• repair_shop_id – A reference to the related repair shop.
• employee_id – A reference to the related employee.
• position_id – A reference to the related position, the employee would have during the defined time period. In most cases, this would be his current position, but we have the flexibility to assign some other position here.
• schedule_date – A date this entry is related to.
• time_from & time_to – This pair defines the time period this entry is related to.
• plan – A flag denoting if this was planned entry. Entry shall not be planned only if we inserted it ad-hoc.
• actual – This flag denotes if this entry was realized. Notice that in most cases, both flags, plan and actual, would be set to True. This would point out that we planned and actually realized that plan.
• insert_ts – A timestamp denoting the moment when this record was inserted in the table.

The combination repair_shop_id - employee_id - schedule_date - time_from forms the UNIQUE/alternate key of this table. Before inserting a new record, we should also check that new interval time_from – time_to doesn’t overlap with any existing interval for that same employee and date.

Section 2: Customers & contacts

Now we’re ready to move to the customer-related part of the model.

We’ll store all customers, we worked with before or we had contact with, in the customer table. For each customer, we’ll store:

• first_name & last_name – The first and the last name of the customer, in case our customer is a private individual.
• company_name – A company name, in a case out customer is a company and not a private individual.
• address – The customer’s address.
• mobile – The customer’s mobile phone number.
• email – The customer’s email
• details – All additional customer details, if any, in the textual format.
• insert_ts – A timestamp denoting the moment when this record was inserted in the table.

Most of the attributes in this table are nullable because we probably won’t have some of them and some (first_name & last_name vs. company_name) exclude others.

We’ll need to track all contacts we made with each customer. In order to do that, we’ll use two tables. First one, the contact_type table, is a simple dictionary containing only the UNIQUE type_name value.

Real contact data is stored in the contact table. We’ll store references to the type of that contact (contact_type_id), a customer we had contact with (customer_id), an employee who made that contact (schedule_id), and also store contact details and the time when this record was inserted in the table (insert_ts).

Section 3: Vehicles

After knowing our resources and customers, we need to store vehicles we’ll work with. Besides tracking data and creating internal reports, in most countries we’ll also need to create reports for regulatory agencies, insurance companies, police.

First, we’ll define models of our vehicles. We’ll use 3 tables to achieve that. In the make dictionary, we’ll list unique make_names for all car/vehicle manufactures/makes. Besides that, we’ll need to know vehicle types, so we’ll use one more dictionary with only one unique value attribute – type_name. The 3^rd dictionary used is the model dictionary. This one shall contain the list of all models that came through our doors. For each model, we’ll define the unique combination model_name – make_id – vechicle_type_id.

We’ll finish describing this subject area with the vehicle table. This is the only table in this subject area containing “real” data. We’ll use this table to store the following details:

• vin – A vehicle identification number, uniquely defining this vehicle.
• license_plate – A current license plate number.
• customer_id – A reference to the customer this vehicle belongs to. In case vehicle changes the owner, we’ll insert it as a new record, but we’ll know this is the same vehicle based on the serial number.
• model_id – A reference to the model dictionary.
• manufactured_year & manufactured_month – Denote the year and the month when this vehicle was produced.
• details – All additional details in the textual format.
• insert_ts – A timestamp denoting the moment when this record was inserted in the table.

Section 4: Services & offers

We’re ready to make the next big step. We need to define what we offer to our (potential) customers. These could be single tasks or a set of tasks – services.

The list of all services is stored in the service_catalog dictionary. Each service consists of a few tasks and is uniquely defined by its service_name. Besides the name, we’ll also store a description, if we have any, the percentage of service_discount and the is_active flag. The service discount shall be used for all tasks included in this service.

Next, we’ll define tasks. Tasks are part of our services. They are the basic action that could be done stand-alone. Each task is defined by these values in the task_catalog table:

• task_name & service_catalog_id – A name we’ll use to describe this task and the service it belongs to. This attribute pair forms the unique key of the table.
• description – The additional textual description, if any, used to describe this task.
• ref_interval – A flag denoting if we’ll measure interval for this task.
• ref_interval_min & ref_interval_max – The minimal and the maximal boundary of the reference range.
• describe – A flag denoting if we should add a textual comment for this task.
• task_price – A current price, without service discounts, for this task.
• is_active – A flag denoting if the task is currently active (in our offer) or not.

After the contact with customers, we’ll make offers to them. The offer could be a complete service, with all its tasks or a set of tasks. All offers are stored in the offer table. For each offer, we’ll store:

• customer_id – An id of the related customer.
• contact_id – An id of the related contact, if there was any. This could be important information to determine how many offers came as a result of previous contacts.
• offer_description – An additional textual description of this offer.
• service_catalog_id – An id of the service we have offered to the customer. This id could be NULL in case we haven’t offered him a complete service, but one or more tasks that are not a part of the service.
• service_discount – The service discount at the moment offer was created. This value shall contain NULL in case offer was not related to the service.
• offer_price – A final price of that offer. It could be calculated as the sum of all tasks minus service discount.
• insert_ts – A timestamp denoting the moment when this record was inserted in the table.

The last table in this subject area is the offer_task table. For each offer, no matter if we offered a complete service or not, we’ll store the set of all tasks. We need to store the following details:

• offer_id – An id of the related offer.
• task_catalog_id – An id of the related task. Together with the offer_id, it forms the unique/alternate key of this table
• task_price – A current price of that task at the moment this record was inserted.
• insert_ts - A timestamp denoting the moment when this record was inserted in the table.

Section 5: Visits

The last subject area in our model is used to store actual customer visits to our repair shop. Although it looks complex, we have only 2 new tables here, visit and visit_task.

When the customer agrees to our offer or just comes into our shop, we’ll treat that as a visit. For each such event, we’ll store the following details:

• repair_shop_id – A reference to the related repair shop.
• customer_id – A reference to the customer this visit is related to.
• vehicle_id – A reference to the vehicle this visit is related to.
• visit_start_date – A visit start date (planned).
• visit_start_time – A visit start time (planned).
• visit_end_date – A visit start date (actual). This value shall be set when the visit actually ends.
• visit_end_time – A visit start time (actual). This value shall be set when the visit actually ends.
• license_plate – A license plate number at the moment visit happened. Notice, that license plates change during the time.
• offer_id – An id of the related offer, if any.
• service_catalog_id – An id of the related service, if any.
• service_discount – A percentage amount of discount at the moment this record was added and in case we offer a complete service.
• visit_price – A total price a customer should pay for that visit.
• invoice_created – A timestamp when the invoice was generated.
• invoice_due – A timestamp when the invoice became due.
• invoice_charged – A timestamp when the invoice was charged.
• insert_ts – A timestamp denoting the moment when this record was inserted in the table.

The last table in our model is the visit_task table. This is the place to store all tasks that were actually part of that visit. For each record here, we’ll store the following values:

• visit_id – A reference to that visit.
• task_catalog_id – A reference to the related task
• value_measured – A value that was measured during this task, if the task required measurement.
• task_description – A description related to that task if the task required a description.
• pass – A flag denoting if this task was in the expected interval or not.
• task_price – An actual price of that task at the moment inserted in this table.
• insert_ts – A timestamp denoting the moment when this record was inserted in the table.

While this model is pretty simplified it contains all the necessary elements you’ll need to build a complete model around it. Parts which require improvements are definitely material used and payment processing. Would you add something more to this model?