Product data model for Inventory Management database design

Overview

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

• Entity: ITEM_CATEGORY
• Entity: ITEM
• Entity: ITEM_TAX_TYPE
• Entity: UNIT_OF_MEASURE
• Entity: BAR_CODE

Entity: ITEM_CATEGORY

Hierarchical list of item categories.

Attributes
ID	PRIMARY KEY
Parent_Category_ID	Lookup for the Item Category that is the parent of the current Item Category. It helps to construct a hierarchy of Item Categories. Parent_Category_ID is NULL for the top of the hierarchy.
Name	Item Category name. NOT NULL UNIQUE.
Description	Description of the Item Category.
Is_Active	Flag attribute to indicate if the Item Category is still available or it was canceled permanently from the Store offer. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: ITEM

List of Items recorded at a Store.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store at which the Item was recorded. NOT NULL
Item_Category_ID	Lookup for the Item Category to which the Item is assigned. NOT NULL
Supplier_ID	Lookup for the Supplier of the Item. NOT NULL
Unit_Of_Measure_ID	Lookup for the Unit of Measure of the Item. NOT NULL
SKU_Code	Store internal code for the Item. NOT NULL UNIQUE.
Name	Item name. NOT NULL .
Description	Item description.
Is_Service	Flag attribute to indicate if the Item is a Service or a Product. Example: ⦁ If Is_Service is TRUE then the Item is a Service (not using stock), otherwise is a Product ( using stock). NOT NULL
In_Stock	Flag attribute to indicate if the Item is in stock. NOT NULL
Using_Default_Quantity	Flag attribute to indicate if there is a default quantity used when adding the Item to an Order. NOT NULL
Default_Quantity	Default quantity used when adding the Item to an Order. If Using_Default_Quantity is FALSE then Default_Quantity is NULL. Default_Quantity at Item level overwrites the Default_Quantity at Store level. See Subject Area: STORE Entity: SETTING for more information.
Current_Stock_Quantity	Current stock quantity of the Item.
Preferred_Stock_Quantity	Preferred stock quantity of the Item.
Min_Stock_Quantity	Minimum stock quantity of the Item. When Current_Stock_Quantity reaches the Min_Stock_Quantity, should start to purchase new Item quantity from supplier. Quantity to be purchased > = Preferred_Stock_Quantity - Current_Stock_Quantity
Low_Stock_Warning	Flag attribute to indicate if an warning is raised when the Current_Stock_Quantity reaches the Low_Stock_Quantity. NOT NULL
Low_Stock_Quantity	Low stock quantity of the Item. When Current_Stock_Quantity reaches the Low_Stock_Quantity, an warning is raised to announce that new Item quantity should be purchased from supplier.
Is_Active	Flag attribute to indicate if the Item is still available at the Store or it was canceled permanently from the Store offer. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: ITEM_TAX_TYPE

List of Tax Types assigned to an Item. An Item can have many Tax Types assigned.

Attributes
ID	PRIMARY KEY
Item_ID	Lookup for the Item to which the Tax Type was assigned. NOT NULL
Tax_Type_ID	Lookup for the Tax Type assigned to the Item. NOT NULL
Created_Time	Timestamp when the Tax type was assigned to Item. 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
Description	Description of the Item Tax Type assigned to Item.

Entity: UNIT_OF_MEASURE

List of all Units of Measure.

Attributes
ID	PRIMARY KEY
Name	Unit Of Measure name. NOT NULL UNIQUE
Symbol	Unit Of Measure symbol (short name). Example: 'unit', 'l' (for liter), 'kg' (for kilogram) etc. NOT NULL UNIQUE
Description	Unit Of Measure description.

Entity: BAR_CODE

List of all bar codes assigned to an Item. An Item can have many bar codes assigned.

Attributes
ID	PRIMARY KEY
Item_ID	Lookup for the Item to which the Bar Code is assigned. NOT NULL
Bar_Code	Bar code of the Item. A blob column that can store a sequence of characters, an image etc . NOT NULL UNIQUE
Is_Active	Flag attribute to indicate if the bar code is still available for the Item. NOT NULL
Description	Unit Of Measure description.