E-commerce | Subject area: STORE

Overview

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

Store Setting entities

• Entity: STORE
• Entity: SETTING
• Entity: TAX_TYPE
• Entity: LANGUAGE
• Entity: CURRENCY

Store Holiday entities

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

Store Media entities

• Entity: MEDIA_TYPE
• Entity: MEDIA

Store Setting entities

Entity: STORE

This entity records information about Stores. The same database can manage many Stores.

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

Entity: SETTING

This entity records the Settings (parameters) defined at Store level.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store for which the Setting was defined. NOT NULL
Default_Payment_Method_ID	Lookup for the Default Payment Method assigned to Store.
Default_Tax_Type_ID	Lookup for the Default Tax Type assigned to Store.
Default_Quantity	Default quantity for the Item added to an Order. Example: ⦁ If Default_Quantity is set to 1 then each time an Item is added to an Order the Item Quantity is set to 1 and can be edited (if necessary) before submitting the Order.
In_Stock_Check	Flag attribute to indicate if a stock check is done before an Item is added to Order. ⦁ If In_Stock_Check attribute is set TRUE then the system will check if there is enough quantity on stock before Item is added to the Order. ⦁ If there is not enough quantity on stock and the Negative_Stock_Allowed is then a message will be displayed to inform that the Order can not be completed. ⦁ If there is not enough quantity on stock and the Negative_Stock_Allowed is then a message will be displayed to inform that the Order can be completed and the Item will go negative on stock. NOT NULL
Negative_Stock_Allowed	Flag attribute to indicate if negative stock is allowed. Example: ⦁ If Negative_Stock_Allowed attribute is set FALSE then an warning message will be displayed to prevent negative stock . NOT NULL
Price_Includes_Tax	Flag attribute to indicate how the price is displayed and printed (on screen, receipts, invoices etc). Example: ⦁ If Price_Includes_Tax attribute is set TRUE then prices will be displayed and printed after tax. ⦁ If Price_Includes_Tax attribute is set FALSE then prices will be displayed and printed before tax. NOT NULL
Negative_Price_Allowed	Flag attribute to indicate if negatives prices can be assigned to items and can be used in sales. NOT NULL
Moving_Average_Price	Flag attribute that specifies whether the Moving Average Price method is applied. ⦁ If Moving_Average_Price attribute is set TRUE then the Moving Average Price method is applied. When the Moving Average Price method is applied , the cost price is recalculated after each purchase (items acquisition) as follows: Current_Item_Cost = ( Initial_Stock_Quantity * Initial_Item_Cost + Purchased Item Quantity * Purchased Item Cost ) / ( Initial_Stock_Quantity + Purchased Item Quantity ) See Subject Area: INVENTORY AND STOCK, Entity: STOCK_HISTORY for more information. . NOT NULL
Discount_Before_Tax	Flag attribute that specifies whether discount is applied before or after taxes are applied. This flag is very important as applying fixed discounts before or after taxes results in different totals. Example: ⦁ If Discount_Before_Tax attribute is set TRUE then discounts are applied before taxes. ⦁ If Discount_Before_Tax attribute is set FALSEthen discounts are applied after taxes. NOT NULL
Decimal_Places	Number of Decimal Places displayed and printed
Default_Due_Days	Default due days for an unpaid Order. Example: ⦁ If Default_Due_Days is set to 1 then the payment must be done in 1 day (maximum 24 hours) after the Order is delivered (if credit payments are allowed). ⦁ If Default_Due_Days is NULL then the Order must be paid before the Order is delivered to Customer (credit payments are not allowed).
Public_Reviews_Allowed	Flag attribute to indicate if the Customer order reviews are public and displayed on the Store website. NOT NULL. Example: ⦁ If Public_Reviews_Allowed attribute is set TRUE then the Customer reviews are public and can be viewed by other Customers on Store website ⦁ If Public_Reviews_Allowed attribute is set FALSE then the Customer reviews are private and cannot be viewed by other Customers on Store website. See Subject Area: ORDER, Entity: ORDER_REVIEW for more information.
Created_Time	Timestamp when the Setting record was created. NOT NULL
Start_Time	The start timestamp from which the Setting is applicable. NOT NULL
End_Time	The end timestamp after which the Setting is not applicable. When the End_Time is NULL the Setting is still applicable. NULL
Is_Active	Flag attribute to indicate if the Setting is still active. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: TAX_TYPE

List of Tax types assigned to a Store.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store for which the Tax Type record was defined. NOT NULL
Name	Tax Type name. Example: 'Value Added Tax' etc. NOT NULL
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 Store default currency. See Entity: STORE, 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 Store default currency. See Entity: STORE, 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

Store Holiday entities

Entity: HOUR

Day hours list. This entity facilitates Store 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 store 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 Store.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store for which the Holiday record was defined. Each Store 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 Store Employees.

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

Store Media entities

Entity: MEDIA_TYPE

List of Media types.

Attributes
ID	PRIMARY KEY
Name	Media Type name. Cannot have two Media Types with the same name. NOT NULL UNIQUE.
Description	Media Type description

Entity: MEDIA

This entity can record the Media that is relevant for each Store. Media can be assigned to:

⦁ a Store (for example Store location photos (JPG), Store presentation (PPT) etc)

⦁ a Store Employee (for example Store Employee badge photo (JPG) etc )

⦁ an Item to be sold (for example item photos (JPG), the item User Manual (PDF) etc)

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store to which the Media is assigned. NOT NULL
Media_Type_ID	Lookup for the Media Type. NOT NULL
Employee_ID	If Media is assigned to a Store Employee then Employee_ID is the lookup for the Store Employee. If the document is not assigned to a Store Employee then Employee_ID is NULL.
Item_ID	If Media is assigned to an Item then Item_ID is the lookup for that Item. If Media is not assigned to an Item then Item_ID is NULL.
Name	The name of Media. Cannot have two Media documents with the same name. NOT NULL UNIQUE
File_Name	The name of the file uploaded for Media. NOT NULL
File_Source	The file source of the Media. The file source is uploaded into the File_Source blob column. NOT NULL
Created_Time	Timestamp when the Media record was created. NOT NULL
Created_Emp_Login_ID	Lookup for the login session of the Employee who created the Media record. NOT NULL
Comments	Internal notes used for clarifications on record content.