Store data model for Inventory Management database design

Overview

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

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

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