Car Service | Subject area: SHOP

Overview

The Logical Data Model of the SHOP Subject Area includes three groups of entities:

Shop Setting entities

• Entity: SHOP
• Entity: TAX_TYPE
• Entity: LANGUAGE
• Entity: CURRENCY

Workplace entities

• Entity: ORGANIZATION
• Entity: WORKPLACE_TYPE
• Entity: WORKPLACE

Holiday entities

• Entity: HOUR
• Entity: HOLIDAY_REASON
• Entity: HOLIDAY

Shop Setting entities

Entity: SHOP

This entity records information about all the Shops (locations) of the Car Service.

Attributes
ID	PRIMARY KEY
City_ID	Lookup for the City where the Shop is located. NOT NULL
Language_ID	Lookup for the main language. NOT NULL
Currency_ID	Lookup for the main currency. NOT NULL
Admin_User_ID	Lookup for the Admin who created the Shop. NOT NULL
Code	Shop code. UNIQUE identifies the Shop. Cannot register two Shops with the same code.
Name	Shop name. UNIQUE identifies the Shop. Cannot register two Shops with the same Name. NOT NULL UNIQUE
Is_Active	Flag attribute to indicate if the Shop is still operational at location or it is closed permanently. NOT NULL
Legal_Entity_Name	Legal Entity Name of the company who owns the Shop. NOT NULL
Tax_Code	Tax Code of the company who owns the Shop (VAT code). NOT NULL
Address	Shop Address. NOT NULL
Registration_Number	Registration Number of the company who owns the Shop. NOT NULL
GPS_Location	Shop GPS location. Can be used for delivery pick-up location etc
Postal_Code	Shop postal code.
Phone	Shop phone number.
Fax	Shop fax number.
Email	Shop email.
Website	Shop website http address.
Logo	Shop logo (image, etc).
Bank_Branch	Branch of the bank where the company who owns the Shop has the bank account.
Bank_Code	Bank code of the bank where the company who owns the Shop has the bank account.
Bank_Account	Bank Account of the company who owns the Shop.
Comments	Internal notes used for clarifications on record content. Can be seen and edited only by Shop Employees.

Entity: TAX_TYPE

List of Tax types assigned to a Shop.

Attributes
ID	PRIMARY KEY
Shop_ID	Lookup for the Shop for which the Tax Type record was defined. NOT NULL
Name	Tax Type name. Example: 'Value Added Tax' etc. NOT NULL UNIQUE
Code	Tax Type code. Example: 'VAT' etc. NOT NULL
Description	Tax Type description
Is_Percentage	Flag to indicate if the tax value is a percentage or a flat amount. Example: ⦁ If Is_Percentage is TRUE and Value is '40' then it means a 40% Tax ⦁ If Is_Percentage is FALSE and Value is '40' then it means 40 money amount expressed in Shop default currency. See Entity: SHOP, Attribute: Currency_ID for more information. NOT NULL
Value	Value of Tax. Example: ⦁ If Is_Percentage is TRUE then the Value is the Tax percentage. For example a value of '14.50' means 14.50% Tax. ⦁ If Is_Percentage is FALSE then the Value is the money amount fix Tax. For example a Tax Value of '14.50' means a Tax of 14.50 expressed in Shop default currency. See Entity: SHOP, Attribute: Currency_ID for more information. NOT NULL
Created_Time	Timestamp when the Tax type record was created. NOT NULL
Start_Time	The start timestamp from which the Tax type is applicable. NOT NULL
End_Time	The end timestamp after which the Tax type is not applicable. When the End_Time is NULL the Tax type is still applicable. NULL
Is_Active	Flag attribute to indicate if the Tax_Type is still active. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: LANGUAGE

Languages list

Attributes
ID	PRIMARY KEY
Name	Language name. NOT NULL UNIQUE
Description	Language description

Entity: CURRENCY

Currencies list

Attributes
ID	PRIMARY KEY
Name	Currency name NOT NULL UNIQUE
Description	Currency description

Workplace entities

Entity: ORGANIZATION

This entity records all the Organization units defined within a Shop. An Organization can be an Inventory organization (for example a Deposit) or a Labor Organization (for example a Technical Team).

Attributes
ID	PRIMARY KEY
Shop_ID	Lookup for the Shop for which the Organization was defined. NOT NULL
Name	Organization name. UNIQUE identifies the Organization. Cannot register two Organizations with the same Name. NOT NULL UNIQUE
Description	Organization description
Is_Inventory	Flag attribute to indicate if the Organization is an Inventory organization (for example a Deposit). NOT NULL
Is_Labor	Flag attribute to indicate if the Organization is a Labor Organization (for example a Technical Team). NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: WORKPLACE_TYPE

List of Workplace types. Example: 'Vehicle ramp', 'Vehicle paint room', 'Wheels diagnose installation' etc

Attributes
ID	PRIMARY KEY
Name	Workplace type name. NOT NULL UNIQUE
Description	Workplace type description

Entity: WORKPLACE

This entity records all Workplaces defined within a Shop.

Attributes
ID	PRIMARY KEY
Shop_ID	Lookup for the Shop for which the Workplace record was defined. NOT NULL
Workplace_Type_ID	Lookup for the Workshop Type for which the Workplace record was defined. NOT NULL
Inventory_Organization_ID	Lookup for the Inventory organization assigned to the Workplace. NOT NULL
Labor_Organization_ID	Lookup for the Labor organization assigned to the Workplace. NOT NULL
Name	Workplace name. NOT NULL UNIQUE
Description	Workplace description

Holiday entities

Entity: HOUR

Day hours list. This entity facilitates Shop hours selection and display.

Attributes
ID	PRIMARY KEY
Name	Hour name NOT NULL
AM_PM	Ante Meridian or Post Meridian hour. This attribute can have 'AM' or 'PM' value. NOT NULL
Description	Hour description

CONSTRAINT Hour_UK UNIQUE (Name, AM_PM)).

Entity: HOLIDAY_REASON

List of holiday reasons. Could be legal holidays or other ad-hoc shop holidays.

Attributes
ID	PRIMARY KEY
Name	Holiday reason (holiday name) NOT NULL UNIQUE
Description	Holiday description

Entity: HOLIDAY

This entity records all Holidays for each Shop.

Attributes
ID	PRIMARY KEY
Shop_ID	Lookup for the Shop for which the Holiday record was defined. Each Shop can have many Holiday records. NOT NULL
Holiday_Reason_ID	Lookup for the Holiday reason (Holiday name) for which the Holiday record was defined. NOT NULL
Start_Date	Holiday start date. NOT NULL
Start_Hour_ID	Lookup for the Holiday start hour. NOT NULL
End_Date	Holiday end date. NOT NULL
End_Hour_ID	Lookup for the Holiday end hour. NOT NULL
Comments	Internal notes used for clarifications on record content. Can be seen and edited only by Shop Employees.

CONSTRAINT UK_Holiday UNIQUE (Shop_ID, Holiday_Reason_ID, Start_Date). Cannot have two Holidays for the same Shop, with the same Holiday Reason and starting on the same date.