Data Model Pack

Subject area: PART AND LABOR

Car Service Data Model · release 3.4 · ER Diagram · SQL DDL scripts

Part entities

Car Service Inventory Data Model
Car Service Inventory Data Model Click to zoom

Entity: MANUFACTURER

List of vehicle parts manufacturers.

Attributes Description
ID PRIMARY KEY
Name Manufacturer name. NOT NULL UNIQUE. Can't have two Manufacturers with the same Name.
Description Description of the Manufacturer.

Entity: PART_CATEGORY

Hierarchical list of vehicle parts categories.

Attributes Description
ID PRIMARY KEY
Parent_Category_ID Lookup for the vehicle Part Category that is the parent of the current Part Category. It helps to construct a hierarchy of vehicle Parts Categories.
Name Part Category name. NOT NULL UNIQUE. Can't have two Part Categories with the same Name.
Description Description of the Part Category.

Example

PART_CATEGORY

ID Name Parent_Category ID Name
1 Fuel Delivery and Air Induction NULL NULL
2 Air Cleaner 1 Fuel Delivery and Air Induction
3 Air Filter Element 2 Air Cleaner
4 Air Cleaner Fresh Air Duct 2 Air Cleaner

The example above implements the following hierarchy of vehicle Parts Categories:

Fuel Delivery and Air Induction
|___Air Cleaner
    |___Air Filter Element
    |____Air Cleaner Fresh Air Duct

and the following navigation paths:

Fuel Delivery and Air Induction > Air Cleaner > Air Filter Element

Fuel Delivery and Air Induction > Air Cleaner > Air Cleaner Fresh Air Duct

Entity: PART

List of Vehicle parts recorded at a Shop.

Attributes Description
ID PRIMARY KEY
Shop_ID Lookup for the Shop at which the vehicle part was recorded. NOT NULL
Part_Category_ID Lookup for the Part Category to which the Part is assigned. NOT NULL
Manufacturer_ID Lookup for the Manufacturer of the Part. NOT NULL
Unit_Of_Measure_ID Lookup for the Unit of Measure of the Part. NOT NULL
Code Shop internal code for the part. UNIQUE.
Name Part name. NOT NULL.
Description Vehicle Part description.
In_Stock Flag attribute to indicate if the Part is in stock. NOT NULL
Is_Active Flag attribute to indicate if the Part is still available at the Shop or it was canceled permanently from the Shop offer. NOT NULL
Comments Internal notes used for clarifications on record content.

Entity: INVENTORY

This table records information about the vehicle Parts stock available at each Shop inventory Organization.

Attributes Description
ID PRIMARY KEY
Shop_ID Lookup for the Shop at which the vehicle Part stock was recorded. NOT NULL
Organization_ID Lookup for the Inventory Organization at which the vehicle Part stock was recorded. A Shop can have many Organizations. An Organization can be an Inventory Organization (for example a Deposit) or a Labor Organization (for example a Technical Team). Only an Inventory Organization can be assigned to an Inventory record. See Entity: ORGANIZATION for more information. NOT NULL
Part_ID Lookup for the Part for which the Part stock was recorded in the Inventory. NOT NULL
On_Hand_Quantity Part stock quantity available in the Inventory. NOT NULL.
Info_Lot Information about the Part stock lot (for example details about Part stock lot supplier etc.)
Comments Internal notes used for clarifications on record content.

Labor entities

Car Service Labor Data Model
Car Service Labor Data Model Click to zoom

Entity: LABOR_CATEGORY

Hierarchical list of labor categories.

Attributes Description
ID PRIMARY KEY
Parent_Category_ID Lookup for the Labor Category that is the parent of the current Labor Category. It helps to construct a hierarchy of vehicle Labor Categories.
Name Labor Category name. NOT NULL UNIQUE. Can't have two Labor Categories with the same Name.
Description Description of the Labor Category.

Example

LABOR_CATEGORY

ID Name Parent_Category ID Name
1 Brake Control NULL NULL
2 Brake Inspection 1 Brake Control
3 Brake Disk Front Replacement 1 Brake Control
4 Brake Disk Rear Replacement 1 Brake Control

The example above implements the following hierarchy of vehicle Labor Categories:

Brake Control
|___Brake Inspection
|___Brake Disk Front Replacement
|___Brake Disk Rear Replacement

and the following navigation paths:

Brake Control > Brake Inspection

Brake Control > Brake Disk Front Replacement

Brake Control > Brake Disk Rear Replacement

Entity: LABOR_CORRECTION

List of Labor corrections that can be applied to a car.

Attributes Description
ID PRIMARY KEY
Name Labor Correction name. NOT NULL UNIQUE. Can't have two Labor Corrections with the same Name.
Description Description of the Labor Correction.

Example

LABOR_CORRECTION

Name
Replace
Diagnose / Test
Wear and Tear
etc.

Entity: LABOR

List of all Labor items available at a Shop.

Attributes Description
ID PRIMARY KEY
Labor_Category_ID Lookup for the Labor Category to which the Labor item is assigned. NOT NULL
Labor_Correction_ID Lookup for the Labor Correction to which the Labor item is assigned. NOT NULL
Labor_Organization_ID Lookup for the Labor Organization where the Labor item is available. NOT NULL
Code Shop internal code for the Labor.
Name Labor name. NOT NULL.
Is_Active Flag attribute to indicate if the Labor item is still available at the Shop or it was canceled permanently from the Shop offer. NOT NULL
Description Labor description.
Comments Internal notes used for clarifications on record content.

Entity: UNIT_OF_MEASURE

List of all Units of Measure for vehicle parts.

Attributes Description
ID PRIMARY KEY
Name Unit Of Measure name. NOT NULL UNIQUE
Description Unit Of Measure description.
Symbol Unit Of Measure symbol (short name). Example: 'unit', 'l' (for liter), 'kg' (for kilogram) etc. NOT NULL UNIQUE

Entity: MEASUREMENT_TYPE

List of all measurements that can be recorded during a labor activity.

Attributes Description
ID PRIMARY KEY
Labor_ID Lookup for the Labor during which the Measurement Type can be recorded. NOT NULL
Code Shop internal code for the Measurement Type. NOT NULL.
Name Measurement Type name. NOT NULL.
Unit_Of_Measure_ID Lookup for the Unit of Measure of the Measurement Type. NOT NULL
Description Measurement Type description.

Entity: MEASUREMENT

List of all Measurement values registered for a Vehicle during a Labor activity.

Attributes Description
ID PRIMARY KEY
Order_Line_ID Lookup for the Order Line for which the measurement value is recorded. The Order Line should have a Vehicle and a Labor item assigned. NOT NULL
Measurement_Type_ID Lookup for the Measurement Type for which the measurement value is recorded. NOT NULL
Value Value of the measurement. NOT NULL.
Measurement_Time Timestamp when the Measurement was registered. NOT NULL.
Description Measurement description.
Comments Internal notes used for clarifications on record content.

Service entities

Car Service Data Model
Car Service Data Model Click to zoom

Entity: PRICING_TYPE

List of Pricing Types that can be assigned to a Service.

Attributes Description
ID PRIMARY KEY
Name Pricing Type name. NOT NULL UNIQUE. Can't have two Pricing Types with the same Name.
Description Description of the Pricing Type.

Example

PRICING_TYPE

Name
Parts Only
Labor Only
Parts and Labor

Entity: SERVICE_CATEGORY

Hierarchical list of Service categories.

Attributes Description
ID PRIMARY KEY
Parent_Category_ID Lookup for the Service Category that is the parent of the current Service Category. It helps to construct a hierarchy of vehicle Service Categories.
Name Service Category name. NOT NULL UNIQUE. Can't have two Service Categories with the same Name.
Description Description of the Service Category.

Example

SERVICE_CATEGORY

ID Name Parent_Category ID Name
1 Heating and Air Conditioning repair NULL NULL
2 Sensors and switches repair 1 Heating and Air Conditioning repair
3 Filters replacement 1 Heating and Air Conditioning repair

The example above implements the following hierarchy of Service Categories:

Heating and Air Conditioning repair
|___Sensors and switches repair
|___Filters replacement

Entity: SERVICE

Hierarchical list of vehicle Services.

Attributes Description
ID PRIMARY KEY
Service_Category_ID Lookup for the Service Category to which the Service is assigned. NOT NULL
Pricing_Type_ID Lookup for the Pricing Type of the Service. Pricing type can be: 'Part Only', 'Labor Only' or 'Part and Labor'. See Entity: PRICING_TYPE for more information. NOT NULL
Code Shop internal code for the Service. NOT NULL UNIQUE.
Name Service name. NOT NULL UNIQUE.
Description Service description.
Is_Quick_Repair Flag attribute to indicate if the Service is a quick repair. The quick repairs can be displayed to facilitate service selection. NOT NULL
Is_Repair_Package Flag attribute to indicate if the Service is a complex repair package that contains many service items and parts. NOT NULL
Comments Internal notes used for clarifications on record content.

Entity: SERVICE_ITEM

A Service can have many Service Items assigned. A Service Item can be a vehicle Part or a Labor item.

The SERVICE_ITEM entity records all vehicle Parts and Labor items that are assigned to a Service.

Attributes Description
ID PRIMARY KEY
Shop_ID Lookup for the Shop to which the Service is assigned. NOT NULL
Service_ID Lookup for the Service to which the Service item is assigned. NOT NULL
Labor_ID The Service Item record can be created for a vehicle Part or for a Labor item. When the Service Item record is created for a Labor item, the Labor_ID is the lookup for that Labor item. In this case the Part_ID is NULL.
Part_ID The Service Item record can be created for a vehicle Part or for a Labor item. When the Service Item record is created for a vehicle Part, the Part_ID is the lookup for that vehicle Part. In this case the Labor_ID is NULL.
Description Service Item description.
Is_Active Flag attribute to indicate if the Service item is still assigned to Service or it was removed permanently from the Service. NOT NULL
Comments Internal notes used for clarifications on record content.

Accelerate every database schema design with powerful data models

We have already created the database diagram and prepared the SQL DDL scripts to deploy your database schema. Download a free Demo edition and evaluate it in your own database.

Try Data Model Pack freeView pricing