Car Service Data Model | Subject area: ORDER

Order Header entities

Entity: ORDER_STATUS

This entity records the Order statuses that track the Order lifecycle.

Attributes
ID	PRIMARY KEY
Name	Order status name. NOT NULL UNIQUE. Can't have two statuses with the same name.
Description	Order status description.

Example

ORDER_STATUS

Name
NEW
SUBMITTED
APPROVED
IN PROGRESS
CANCELED
READY
PAID
COMPLETE

Entity: ORDER_HEADER

This entity records information about all Orders.

Attributes
ID	PRIMARY KEY
Shop_ID	Lookup for the Shop at which the Order was placed. The same database can handle multiple Shops. NOT NULL
Vehicle_ID	Lookup for the Vehicle for which the Order is placed. See Entity: VEHICLE for more information. NOT NULL
Invoiced_Customer_ID	Lookup for the Customer who is Invoiced (can be the Vehicle Owner). See Entity: VEHICLE for more information. NOT NULL
Contact_Customer_ID	Lookup for the Customer who can be contacted for information related to Order (can be the Vehicle Owner or the Vehicle Contact Person). See Entity: VEHICLE for more information. NOT NULL
Created_Emp_Login_ID	Lookup for the Shop Employee who created the Order. NOT NULL
Created_Time	Timestamp when Order was created. NOT NULL
Submitted_Emp_Login_ID	Lookup for the Shop Employee who Submitted the Order.
Approved_Emp_Login_ID	Lookup for the Shop Employee who Approved the Order.
Managed_Emp_Login_ID	Lookup for the Shop Employee who is responsible for the Order lifecycle management. Note: The same Shop Employee can be granted with 'Submit Orders' and 'Manage Orders' responsibilities. In this case Submitted_Emp_Login_ID and Managed_Emp_Login_ID are the same. See Subject Area: EMPLOYEE AND ROLE for more information.
Order_No	Order reference number/code asssigned by the Shop. Cannot have two orders with the same Order_No. NOT NULL UNIQUE
Complaint_Specifications	Short description of the vehicle repair and maintenance requirements. NOT NULL
Customer_Is_Waiting	Flag attribute to indicate if the Customer is waiting in the Shop till Order is completed. NOT NULL
Customer_Notes	Customer can add notes to an Order (for example information about preferred payment method etc.)
Start_Time	Timestamp when work on Order should start. NOT NULL
Due_Time	Timestamp when work on Order should be completed. NOT NULL
Vehicle_Arrival_Time	Timestamp when Vehicle arrived to the Service Shop. NOT NULL
Is_Vehicle_Towed	Flag attribute to indicate if Vehicle was towed. NOT NULL
Odometer_Value	Vehicle Odometer mileage value.
Odometer_Time	Timestamp when Vehicle Odometer mileage was read.
Price_Before_Tax	Order total price calculated before Tax. NOT NULL
Tax_Value	Order total Tax Value. NOT NULL
Price_After_Tax	Order total price calculated after Tax. Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Price_Before_Discount	Order total price calculated before Discount. NOT NULL
Order_Items_Discount	Order discount calculated as sum of all discounts applied at Order Line level. See Entity: ORDER_LINE, Entity: ORDER_LINE_DISCOUNT and Entity: DISCOUNT for more information. NOT NULL
Order_Discount	Order discount applied at total Order Value (for example a discount of 25% from total Order value). See Entity: ORDER_DISCOUNT and Entity: DISCOUNT for more information. NOT NULL
Total_Discount_Value	Total Discount Value = Order_Items_Discount + Order_Discount. NOT NULL
Price_After_Discount	Order total price calculated after discount. NOT NULL
Price_Adjustment	Shop Employee can apply a price adjustment at Order level.
Price_Adjustment_Reason	Reason for price adjustment.
Price	Order final price resulted after all taxes, discounts and adjustments were applied. NOT NULL
Latest_Status	Latest status of the Order. Every time when the Order status changes, the application should automatically update this attribute and also insert the Order latest status in the ORDER_HISTORY entity. See Entity: ORDER_HISTORY for more information. NOT NULL
Latest_Status_Update	The timestamp when the Order entered the latest status. Every time when the Order status changes, the application should automatically update this attribute and also update the ORDER_HISTORY.Start_Time attribute for the latest (most recent) Order status. The ORDER_HEADER.Latest_Status_Update attribute should be always synchronized with the ORDER_HISTORY.Start_Time attribute for the latest (most recent) Order status. See Entity: ORDER_HISTORY for more information. NOT NULL
Is_Submitted	Flag attribute to indicate if Order has been submitted. NOT NULL
Submitted_Time	Timestamp when Order was submitted. If Is_Submitted is FALSE then Submitted_Time is NULL.
Is_Approved	Flag attribute to indicate if Order has been approved. NOT NULL
Approved_Time	Timestamp when Order was Approved. If Is_Approved is FALSE then Approved_Time is NULL.
Is_Canceled	Flag attribute to indicate if the Order is Canceled. Example: Shop Employee presses the Cancel Order button. In this case: ⦁ Is_Canceled flag changes from FALSE to TRUE ⦁ at the same time the ORDER.Latest_Status changes to CANCELED. ⦁ at the same time, the application should insert automatically a record with the most recent Order status (CANCELED) in the ORDER_HISTORY entity. NOT NULL
Canceled_Time	Timestamp when Order was Canceled.
Cancel_Reason	Order Cancel reason.
Is_Ready	Flag attribute to indicate if the work on all Order Lines is finished. Example: A Shop Employee can press an 'Order Ready' button when the work on all Order Lines is finished. In this case: ⦁ Is_Ready flag changes from FALSE to TRUE. ⦁ at the same time the ORDER.Latest_Status changes from IN PROGRESS to READY. ⦁ at the same time, the application should insert automatically a record with the most recent Order status (READY) in the ORDER_HISTORY entity. NOT NULL
Ready_Time	Timestamp when the work on all Order Lines is finished.
Is_Paid	Flag attribute to indicate if Order has been paid. Example: ⦁ If Is_Paid is TRUE then Order is paid ⦁ If Is_Paid is FALSE then Order has not been paid. NOT NULL
Payment_Time	Timestamp when Order was paid. If Is_Paid is FALSE then Payment_Time is NULL.
Is_Completed	Flag attribute to indicate if Customer acknowledged Order Completed. Example: ⦁ Is_Completed flag changes from FALSE to TRUE when Order work is finished, Order is paid, Customer received Invoice and Customer acknowledged Order Completed. ⦁ at the same time the ORDER.Latest_Status changes to COMPLETE. ⦁ at the same time the application should automatically insert a record with the most recent Order status (COMPLETE) in the ORDER_HISTORY entity. NOT NULL
Completed_Time	Timestamp when Order was acknowledged Complete.
Comments	Internal notes used for clarifications on record content.

Entity: ORDER_HISTORY

This entity records the Order status history.

Note

Every time when the Order Status changes from an old status to a new status, the ORDER_HISTORY entity should be updated as follows:

Create a new record in the ORDER_HISTORY entity to add the new Order Status and the Start Time of the new Order Status.
Update the ORDER_HISTORY entity to synchronize the ORDER_HISTORY.End_Time of the old Order Status record with the ORDER_HISTORY.Start_Time of the new Order Status record.

Attributes
ID	PRIMARY KEY
Order_ID	Lookup for the Order to which the Order Status is assigned. NOT NULL
Order_Status_ID	Lookup for the Order Status to be assigned. NOT NULL
Start_Time	Timestamp when Order entered the status. NOT NULL
End_Time	Timestamp when Order exited the status. When End_Time is NULL, it means Order is still in that status.

Entity: ORDER_DISCOUNT

This entity records the discounts applied at ORDER level. The database design supports multiple discounts applied at ORDER level simultaneously. For example the same Order can benefit from "Winter promotion - 25% discount for all products" and "15% discount for Orders paid with cash".

Note

The discounts applied at Order Line level are recorded in the ORDER_LINE_DISCOUNT entity.

Attributes
ID	PRIMARY KEY
Discount_ID	Lookup for the Discount to be applied at ORDER level. See Entity: DISCOUNT for more information. NOT NULL
Order_ID	Lookup for the Order to which the discount is applied. NOT NULL
Value	Calculated Value of the Discount. See Entity: DISCOUNT for more information about discount calculation. NOT NULL
Comments	Internal notes used for clarifications on record content.

Order Line entities

Entity: ORDER_LINE_CAUSE

List of causes for creating an Order Line.

Attributes
ID	PRIMARY KEY
Name	Order line cause. NOT NULL UNIQUE. Can't have two Order Line causes with the same name.
Description	Order line cause description.

Example

ORDER_LINE_CAUSE

Name
Preventive Maintenance
Reparation
etc.

Entity: ORDER_LINE_STATUS

This entity records the Order Line statuses that track the Order Line lifecycle.

Attributes
ID	PRIMARY KEY
Name	Order Line status. NOT NULL UNIQUE. Can't have two statuses with the same name.
Description	Order Line status description.

Example

ORDER_STATUS

Name
NEW
SUBMITTED
APPROVED
IN PROGRESS
CANCELED
READY
PAID
COMPLETE

Important

The application code should maintain the Order Status and the Order Lines Status logically synchronized through Order lifecycle. For example:

When a 'NEW' Order is created, all the assigned Order Lines will also have the Status 'NEW'
When an Order is 'SUBMITTED' for approval, all the Order Lines will also be 'SUBMITTED' for approval.
An Order is 'APPROVED' only when all Order Lines are 'APPROVED'.
An Order is 'IN PROGRESS' only when at least one Order Line is 'IN PROGRESS'.
An Order is 'CANCELED' only when all Order Lines are 'CANCELED'.
An Order is 'READY' only when all Order Lines are 'READY'.
An Order is 'PAID' only when all Order Lines are 'PAID'.
An Order is 'COMPLETE' only when all Order Lines are 'COMPLETE'.

Entity: ORDER_LINE

This entity records information about all the Order Lines assigned to an Order. Every time when a vehicle Part or a Labor item is added to an Order, an Order Line is created and assigned to the Order.

Important

An Order Line is created when:

a Labor item is selected and added to an Order
a Part is selected and added to an Order
a Service is selected and added to an Order. In this case, for each Service Item assigned to the Service, an Order Line is added to the Order. Depending on the assigned Service Item, the added Order Line can be for a vehicle Part or for a Labor item. See Entity: SERVICE_ITEM for more information.

Attributes
ID	PRIMARY KEY
Shop_ID	Lookup for the Shop at which the Order was placed. NOT NULL
Order_ID	Lookup for the Order to which the Order Line is assigned. NOT NULL
Order_Line_Cause_ID	Lookup for the Order Line cause. NOT NULL
Created_Emp_Login_ID	Lookup for the Shop Employee who created the Order Line. NOT NULL
Created_Time	Timestamp when Order Line was created. NOT NULL
Submitted_Emp_Login_ID	Lookup for the Shop Employee who Submitted the Order Line.
Approved_Emp_Login_ID	Lookup for the Shop Employee who Approved the Order Line.
Managed_Emp_Login_ID	Lookup for the Shop Employee who is responsible for Order lifecycle management.
Line_No	The sequential number of an Order Line. An Order can have many lines. Each Order Line has a sequential number: 1, 2, 3 etc. This attribute can help to sort the Order lines and also to reference an Order Line. NOT NULL
Is_Customer_Item	Flag attribute to indicate if the Order Line is created for a Customer Item. For example when the Customer bought a vehicle Part from elsewhere and brings that Part to the Car Sevice Shop to be used for a reparation. In this case the Part is registered as an Order Line with Part_Cost = 0 and Is_Customer_Item flag is set 'TRUE'. NOT NULL
Customer_Notes	Customer Notes at Order Line level.
Description	Order Line description.
Service_ID	The Order Line record can be created for a Service, for a Labor item or for a vehicle Part. When the Order Line is created for a Service, the Service_ID is the lookup for that Service. In this case the Labor_ID and the Part_ID are NULL.
Workplace_ID	Lookup for the Workplace where the Labor work for the Order Line is performed. If the Order Line was not created for a Labor item then Workplace_ID is NULL.
Labor_ID	The Order Line record can be created for a Service, for a Labor item or for a vehicle Part. When the Order Line is created for a Labor item, the Labor_ID is the lookup for that Labor item. In this case the Service_ID and the Part_ID are NULL.
Labor_Rate	Labor Rate for the Labor assigned to the Order Line. Standard Labor Rate is obtained from the Entity: LABOR using the lookup Labor_ID. If the Order Line was not created for a Labor item then Labor Rate is NULL.
Labor_Hours	Labor Hours for the Labor assigned to the Order Line. Labor Hours are obtained from the Entity: LABOR using the lookup Labor_ID. If the Order Line was not created for a Labor item then Labor Hours is NULL.
Labor_Cost	Labor Cost for the Labor assigned to the Order Line. Labor_Cost=Labor_Rate*Labor_Hours. If the Order Line was not created for a Labor item then Labor_Cost is NULL.
Part_ID	The Order Line record can be created for a Service, for a Labor item or for a vehicle Part. When the Order Line is created for a vehicle Part, the Part_ID is the lookup for that vehicle Part. In this case the Service_ID and the Labor_ID are NULL.
Part_Cost	Cost of the Part assigned to the Order Line. See Entity: PRICE for more information. The ORDER_LINE.Part_Cost and PRICE.Part_Cost attributes should be synchronized. If the Order Line was not created for a Part then Part_Cost is NULL.
Quantity	Part Quantity. If the Order Line was not created for a Part then Quantity is NULL.
Price_Before_Tax	Order Line price calculated before Tax. If the Order Line was created for a Part then Price_Before_Tax=Part_CostQuantity. If the Order Line was created for a Labor item then Price_Before_Tax = Labor_Cost = Labor_RateLabor_Hours. NOT NULL
Tax_Value	Order Line Tax Value. NOT NULL
Price_After_Tax	Order Line price calculated after Tax. Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Markup_Percentage	Markup Percentage applied to Part or Labor. NOT NULL
Price_Before_Discount	Order Line price calculated before Discount. Price_Before_Discount = Price_After_Tax + Price_After_Tax*Markup_Percentage/100. NOT NULL
Discount_Value	Order Line discount value calculated as a sum of all discounts applied for that Order Line. See Entity: ORDER_LINE_DISCOUNT and Entity: DISCOUNT for more information. NOT NULL
Price_After_Discount	Order Line price calculated after Discount. Price_After_Discount = Price_Before_Discount - Discount_Value. NOT NULL
Price	Order Line final price. NOT NULL
Latest_Status	Latest status of Order Line. Every time when Order Line status changes, application should automatically update this attribute and also insert the Order Line latest status in the ORDER_LINE_HISTORY entity. See Entity: ORDER_LINE_HISTORY for more information. NOT NULL
Latest_Status_Update	The timestamp when the Order Line entered the latest status. Every time when Order Line status changes, application should automatically update this attribute and also update the ORDER_LINE_HISTORY.Start_Time attribute for the latest (most recent) Order Line status. The ORDER_LINE.Latest_Status_Update attribute should be always synchronized with the ORDER_LINE_HISTORY.Start_Time attribute for the latest (most recent) Order Line status. See Entity: ORDER_LINE_HISTORY for more information. NOT NULL
Is_Submitted	Flag attribute to indicate if Order Line has been submitted. NOT NULL
Submitted_Time	Timestamp when Order Line was submitted. If Is_Submitted is FALSE then Submitted_Time is NULL.
Is_Approved	Flag attribute to indicate if Order Line has been approved. NOT NULL
Approved_Time	Timestamp when Order Line was Approved. If Is_Approved is FALSE then Approved_Time is NULL.
Is_Canceled	Flag attribute to indicate if the Order Line is Canceled. Example: Shop Employee presses the Cancel Order Line button to announce when Order Line is Canceled. In this case: ⦁ Is_Canceled flag changes from FALSE to TRUE ⦁ at the same time the Order Line Status changes to CANCELED. ⦁ at the same time, the application should insert automatically a record with the most recent Order Line status (CANCELED) in the ORDER_LINE_HISTORY entity. NOT NULL
Canceled_Time	Timestamp when Order Line was Canceled.
Cancel_Reason	Order Line Cancel reason.
Is_Ready	Flag attribute to indicate if the work on Order Line is finished. Example: A Shop Employee can press an 'Order Line Ready' button to announce when the work on the Order Line is finished. In this case: ⦁ Is_Ready flag changes from FALSE to TRUE. ⦁ at the same time the Order Line Status changes from IN_PROGRESS to READY. ⦁ at the same time, the application should insert automatically a record with the most recent Order Line status (READY) in the ORDER_LINE_HISTORY entity. NOT NULL
Ready_Time	Timestamp when the work on the Order Line is finished.
Is_Paid	Flag attribute to indicate if Order Line has been paid. Example: ⦁ If Is_Paid is TRUE then Order Line is paid ⦁ If Is_Paid is FALSE then Order Line has not been paid. NOT NULL
Payment_Time	Timestamp when Order Line was paid. If Is_Paid is FALSE then Payment_Time is NULL.
Is_Completed	Flag attribute to indicate if Customer acknowledged Order Line Completed. Example: ⦁ Is_Completed flag changes from FALSE to TRUE when Order Line work is finished, Order Line is paid, Customer received Invoice and Customer acknowledged Order Line Completed. ⦁ at the same time the Order Line Status changes to COMPLETE. ⦁ at the same time application should automatically insert a record with the most recent Order Line status (COMPLETE) in the ORDER_LINE_HISTORY entity. NOT NULL
Completed_Time	Timestamp when Order Line was acknowledged Complete.
Comments	Internal notes used for clarifications on record content.

Entity: ORDER_LINE_HISTORY

This entity records the Order Line status history.

Note

Every time when the Order Line Status changes from an old status to a new status, the ORDER_LINE_HISTORY entity should be updated as follows:

Create a new record in the ORDER_LINE_HISTORY entity to add the new Order Line Status and the Start Time of the new Order Line Status.
Update the ORDER_LINE_HISTORY entity to synchronize the ORDER_LINE_HISTORY.End_Time of the old Order Line Status record with the ORDER_LINE_HISTORY.Start_Time of the new Order Line Status record.

Attributes
ID	PRIMARY KEY
Order_Line_ID	Lookup for the Order Line to which the Order Line Status is assigned. NOT NULL
Order_Line_Status_ID	Lookup for the Order Line Status to be assigned. NOT NULL
Start_Time	Timestamp when Order Line entered the status. NOT NULL
End_Time	Timestamp when Order Line exited the status. When End_Time is NULL, it means Order Line is still in that status. NULL

Entity: ORDER_LINE_DISCOUNT

This entity records the discounts applied at ORDER LINE level.

Note

The discounts applied at Order Header level are recorded in the ORDER_DISCOUNT entity.

Attributes
ID	PRIMARY KEY
Discount_ID	Lookup for the Discount to be applied for the ORDER LINE. See Entity: DISCOUNT for more information. NOT NULL
Order_Line_ID	Lookup for the Order Line to which the discount is applied. NOT NULL
Value	Calculated Value of the Discount. See Entity: DISCOUNT for more information on calculation formula. NOT NULL
Comments	Internal notes used for clarifications on record content.

Order Review entities

Entity: ORDER_REVIEW

This entity records Customer reviews on Orders.

Note

Customers who have an online account can rate their Order experience on a scale from 1 to 5 and provide Order Reviews.

Attributes
ID	PRIMARY KEY
Order_ID	Lookup for the Order for which the Order review is recorded. NOT NULL
Customer_ID	Lookup for the Customer who recorded the Order review. NOT NULL
Rating	Rating from 1 to 5. NOT NULL
Review	Order review text filled in by Customer. NOT NULL
Review_Date	Timestamp when the Order review was created. NOT NULL
Reply	Reply text from Shop Employee to Customer review.
Reply_Date	Timestamp when the reply to Customer review was created.
Is_Compensated	Flag attribute to indicate if Customer received a money compensation for the Review. Example: When a Customer sent a negative review for an Order that did not meet the expectations, the Shop can decide to offer a money compensation to Customer. ⦁ If Customer received a compensation for the Review then Is_Compensated attribute is set TRUE. ⦁ If Customer did not receive a compensation for the Review then Is_Compensated attribute is set FALSE. NOT NULL
Compensation_Value	The money amount received by the Customer as a compensation for the Order Review. ⦁ If Is_Compensated is TRUE then the Compensation_Value is the money amount expressed in Shop currency received by Customer as compensation. This money amount is also added to the Customer total Credit. Customer can use the Credit with the next Order placed at the Shop. See Subject Area: CUSTOMER, Entity: CUSTOMER, Attribute: Credit. ⦁ If Is_Compensated is FALSE then Compensation_Value is NULL.
Comments	Internal notes used for clarifications on record content.

Accelerate every database schema design with powerful data models

We have already designed the data model and prepared the SQL scripts for creating the database schema objects.

Try Data Model Pack Free

Product

Resources

Support