Data Model Pack

Subject area: PRICE AND DISCOUNT

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

Overview

Visualize the subject area with clear database diagram and learn about every entity and attribute.

Car Service Price Data Model
Car Service Price Data Model Click to zoom
Car Service Discount Data Model
Car Service Discount Data Model Click to zoom

Entity: PRICE

This entity records information about the prices of vehicle parts and labor.

Attributes Description
ID PRIMARY KEY
Shop_ID Lookup for the Shop where the price is applied. NOT NULL.
Tax_Type_ID Lookup for the main Tax Type applied to the price. NOT NULL.
Description Price description.
Labor_ID The price record can be created for a vehicle Part or for a Labor item. When the price 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.
Labor_Rate Value of the Labor rate per hour expressed in the Shop's default currency. See Subject Area: SHOP, Entity: SHOP, Attribute: Currency_ID for more information. When the price record is created for a Labor item, the Labor_Rate is NOT NULL.
Labor_Hours Number of hours required to perform the Labor. When the price record is created for a Labor item, the Labor_Hours is NOT NULL.
Labor_Cost Labor Cost calculated as Labor_Rate * Labor_Hours. When the price record is created for a Labor item, the Labor_Cost is NOT NULL.
Part_ID The price record can be created for a vehicle Part or for a Labor item. When the price 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.
Part_Cost Part Cost that was paid to Part supplier. When the price record is created for a vehicle Part, the Part_Cost is NOT NULL
Price_Before_Tax Price of Part or Labor calculated before Tax. NOT NULL
Tax_Value Tax Value calculated as defined by the Tax_Type_ID. See Subject Area: SHOP, Entity: TAX_TYPE for more information. NOT NULL
Price_After_Tax Price of Part or Labor calculated after Tax was applied. Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Markup_Percentage Markup Percentage applied to Part or Labor. NOT NULL
Sale_Price Sale_Price = Price_After_Tax + Price_After_Tax*Markup_Percentage/100. NOT NULL
Created_Time Timestamp when the price record was created. NOT NULL
Start_Time The start timestamp from which the price is applicable. NOT NULL
End_Time The end timestamp after which the price is not applicable. When End_Time is NULL the price is still applicable.
Is_Active Flag attribute to indicate if the Price is still applicable in the Shop or it was canceled permanently from the Shop offer. NOT NULL
Comments Internal notes used for clarifications on record content.

Entity: DISCOUNT_TYPE

List of discount types.

Attributes Description
ID PRIMARY KEY
Shop_ID Lookup for the Shop where the discount type is applicable. NOT NULL.
Name Discount Type name.
Example: 'All items at 25%', '50% discount for minimum 3 items bought' etc. NOT NULL UNIQUE
Description Discount Type description
Is_Percentage Flag to indicate if the discount value is a percentage or a flat amount.
Example:
⦁ If Is_Percentage is TRUE and Value is '40' then it means a discount of 40% from price
⦁ If Is_Percentage is FALSE and Value is '40' then it means a flat discount amount of 40 from price, expressed in Shop's default currency.
See Subject Area: SHOP, Entity: SHOP, Attribute: Currency_ID for more information. NOT NULL
Value Value of discount.
Example:
⦁ If Is_Percentage is TRUE then the Value records the percentage of discount. In this case, a Value of '14.50' means a discount of 14.50% from price.
⦁ If Is_Percentage is FALSE then the Value records a flat discount amount. In this case, a discount Value of '14.50' means a flat discount amount of 14.50 from price, expressed in Shop's default currency. See Subject Area: SHOP, Entity: SHOP, Attribute: Currency_ID for more information. NOT NULL
Created_Time Timestamp when the discount type record was created. NOT NULL
Start_Time The start timestamp from which the discount type is applicable. NOT NULL
End_Time The end timestamp after which the discount type is not applicable. When the End_Time is NULL the price is still applicable.
Coupon_Code The Coupon code assigned to the discount type.
When the Coupon_Code is NULL, it means the discount is applicable without a Coupon_Code.
Min_Order_Value The Minimum Order Value from which the discount type is applicable.
Example:
A Minimum Order Value of '140' means that the discount type is applicable only for Orders with a total value greater than 140 (expressed in Shop's currency). See Subject Area: SHOP, Entity: SHOP, Attribute: Currency_ID for more information. NOT NULL
Min_Item_Quantity The Minimum Item Quantity from which the discount type is applicable.
Example:
A Minimum Item Quantity of '3' means that the discount type is applicable only for Orders that contains the Item added three times at least. NOT NULL
Apply_To_All Flag attribute to indicate if the Discount Type is applicable to all quantity when the quantity is >= Min_Item_Quantity. For example: Apply_To_All flag is 'TRUE', Min_Item_Quantity is 3 and a Customer bought 4 items. In this case the Discount Type is applied to all 4 items bought. NOT NULL
Apply_To_Next Flag attribute to indicate if the Discount Type is applicable only to the next quantity bought after the Min_Item_Quantity. For example: Apply_To_Next flag is 'TRUE', Min_Item_Quantity is 3 and a Customer bought 4 items. In this case the Discount Type is applied only to 1 item (next 1 item bought after Min_Item_Quantity. NOT NULL
Max_Discount_Value The Maximum Discount Value applicable to an Order.
Example:
The discount type Name is 'All items 50% off', Is_Percentage is TRUE, Value is '50', Max_Discount_Value is '30' (expressed in Shop's currency). If this discount type is applied to an Order that contains an Item with a price of '80', the discount value will be '30' instead of '40'. NOT NULL
Is_Active Flag attribute to indicate if the Discount Type is still applicable in the Shop or it was canceled permanently from the Shop offer. NOT NULL
Comments Internal notes used for clarifications on record content.

Entity: DISCOUNT

This entity records information about the discounts applied to vehicle part categories, parts, labor categories or labor items.

Attributes Description
ID PRIMARY KEY
Discount_Type_ID Lookup for the Discount Type to be applied to a part category, a part, a labor category or a labor item. NOT NULL.
Part_Category_ID The discount record can be created for a part category, a part, a labor category or a labor item. When the discount record is created for a Part Category, the Part_Category_ID is the lookup for that Part Category. In this case the Part_ID, Labor_Category_ID and Labor_ID are NULL.
Part_ID The discount record can be created for a part category, a part, a labor category or a labor item. When the discount record is created for a Part, the Part_ID is the lookup for that Part. In this case the Part_Category_ID, Labor_Category_ID and Labor_ID are NULL.
Labor_Category_ID The discount record can be created for a part category, a part, a labor category or a labor item. When the discount record is created for a Labor Category, the Labor_Category_ID is the lookup for that Labor Category. In this case the Part_ID, Part_Category_ID and Labor_ID are NULL.
Labor_ID The discount record can be created for a part category, a part, a labor category or a labor item. When the discount record is created for a Labor item, the Labor_ID is the lookup for that Labor. In this case the Part_ID, Part_Category_ID and Labor_Category_ID are 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