Car Service Data Model | Subject area: PRICE AND DISCOUNT

Overview

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

Entity: PRICE

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

Attributes
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
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
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.