Data Model Pack

Subject area: STOCK MOVEMENT DOCUMENT

Inventory Management Data Model · release 1.4 · ER Diagram · SQL DDL scripts

Document

Inventory Management Document Data Model
Inventory Management Document Data Model Click to zoom

Sale Order

  • Entity: ORDER_HEADER
  • Entity: ORDER_LINE

See Subject Area: ORDER for more information about Sale Order entities.

Sale Return

Inventory Management Sale Return Data Model
Inventory Management Sale Return Data Model Click to zoom

Purchase

Inventory Management Purchase Data Model
Inventory Management Purchase Data Model Click to zoom

Stock Return

Inventory Management Stock Return Data Model
Inventory Management Stock Return Data Model Click to zoom

Inventory Count

  • Entity: INVENTORY_COUNT
  • Entity: INVENTORY_COUNT_ITEM

See Subject Area: INVENTORY AND STOCK for more information about Inventory Count entities.

Loss and Damage

Inventory Management Loss and Damage Data Model
Inventory Management Loss and Damage Data Model Click to zoom

Entity: DOCUMENT_CATEGORY

List of Document Categories.

Attributes Description
ID PRIMARY KEY
Name Document Category name. NOT NULL UNIQUE. Can't have two Document Categories with the same name.
Description Document Category description.
Is_Active Flag attribute to indicate if the Document Category is still available or it was canceled permanently. NOT NULL
Comments Internal notes used for clarifications on record content.

Example

DOCUMENT_CATEGORY

Name
1 - Expense
2 - Sale
3 - Inventory
4 - Loss and Damage

Entity: DOCUMENT_TYPE

List of Document Types.

Attributes Description
ID PRIMARY KEY
Document_Category_ID Lookup for the Document Category to which the Document Type is assigned. NOT NULL
Name Document Type name. NOT NULL UNIQUE. Can't have two Document Types with the same name.
Description Document Type description.
Is_Active Flag attribute to indicate if the Document Type is still available or it was canceled permanently. NOT NULL
Comments Internal notes used for clarifications on record content.

Example

Document Category Document Type
1 - Expense 100 - Purchase
1 - Expense 120 - Stock Return
2 - Sale 200 - Sale Order
2 - Sale 210 - Sale Return
2 - Sale 220 - Refund
3 - Inventory 300 - Inventory Count
4 - Loss and Damage 400 - Loss and Damage

Entity: DOCUMENT

This entity records the document that justify an Item stock movement: a stock entry (Item quantity added to stock) or a stock exit (Item quantity decresead from stock).

Documents Example:
⦁ Inventory Count
⦁ Sale Order
⦁ Sale Return
⦁ Purchase
⦁ Stock Return etc

Attributes Description
ID PRIMARY KEY
Store_ID Lookup for the Store at which the document is recorded. NOT NULL
Document_Type_ID Lookup for the Document Type. NOT NULL
Number Document number. NOT NULL
External_Number Document external number (for example when the document is a supplier invoice for a purchase).
Order_Header_ID Lookup for the Sale Order if Document Type is '200 - Sale Order'.
Sale_Return_ID Lookup for the Sale Return if Document Type is '210 - Sale Return'.
Purchase_ID Lookup for the Purchase if Document Type is '100 - Purchase'.
Stock_Return_ID Lookup for the Stock Return if Document Type is '120 - Stock Return'.
Inventory_Count_ID Lookup for the Inventory Count if Document Type is '300 - Inventory Count'. See Subject Area: INVENTORY AND STOCK Entity: INVENTORY_COUNT for more information.
Loss_and_Damage_ID Lookup for the Loss and Damage if Document Type is '400 - Loss and Damage'.
Created_Time Timestamp when Document was created. NOT NULL
Created_Emp_Login_ID Lookup for the Store Employee who created the Document. NOT NULL
Description Stock movement description.
Comments Internal notes used for clarifications on record content.

Entity: SALE_RETURN

This entity records information about a Sale Return requested for an Order. If a Customer requests to return a part or all the Order Items after the Order has reached the Ready status, a Sale Return should be processed.See Subject Area: ORDER, Entity: ORDER_HEADER, Attributes: Return_Required, Return_Quantity, Return_Time. for more information.

Attributes Description
ID PRIMARY KEY
Store_ID Lookup for the Store at which the Sale Return is requested. NOT NULL
Order_ID Lookup for the Order for which the Sale Return is requested. NOT NULL
Entry_Stock_Time Timestamp when the Sale Return Items quantities are added to stock. NOT NULL
Is_Refund_Required Flag attribute to indicate if Sale Return must be followed by money Refund. If an Order was paid IN_ADVANCE, any Sale Return or Cancel request issued after the Order has reached the Ready status must be followed by a Refund. See Subject Area: ORDER for more information. NOT NULL
Refund_Amount Refund amount to be paid to Customer for Sale return.
Is_Refunded Flag attribute to indicate if the full amount of Refund was paid to Customer. See Entity: REFUND for more information. NOT NULL
Refund_Time Timestamp when the full amount of Refund was paid to Customer. See Entity: REFUND for more information. NOT NULL
Description Sale Return description.
Comments Internal notes used for clarifications on record content.

Entity: SALE_RETURN_ITEM

This entity records information about all Sale Return Items assigned to a Sale Return.

Attributes Description
ID PRIMARY KEY
Sale_Return_ID Lookup for the Sale Return to which the Sale Return Item is assigned. NOT NULL
Order_Line_ID Lookup for the Order Line that contains the Item to be returned. NOT NULL
Line_No The sequential number of a Sale Return line. A Sale Return can have many lines. Each Sale Return line has a sequential number: 1, 2, 3 etc. This attribute can help to sort the Sale Return lines and also to reference a Sale Return line (for example when Store Employee and Supplier review a Sale Return) etc. NOT NULL
Return_Quantity Item Quantity returned. NOT NULL.
Description Sale Return Item description.
Comments Internal notes used for clarifications on record content.

Entity: REFUND

This entity records information about all Refunds applied for a Sale Return.

Attributes Description
ID PRIMARY KEY
Sale_Return_ID Lookup for the Sale Return for which the Refund was applied. A Sale Return can have one or many refunds applied. For example a Sale Return has two refunds applied when: 30% of the total amount is refunded with CASH and the rest is refunded on Customer CARD. NOT NULL
Payment_Method_ID Lookup for the Payment Method used for the Refund. NOT NULL
Refund_No Refund number that Store associates with the Refund. Cannot have two refunds with the same Refund_No. NOT NULL UNIQUE
Amount The Amount that is refunded. NOT NULL
Cash_Paid This is the amount handed off to Customer if Cashier paid with Cash.
Cash_Change This is the amount returned by Customer to Cashier if Cashier paid with Cash.
Refund_Time Timestamp when refund was registered. NOT NULL
Comments Internal notes used for clarifications on record content.

Entity: PURCHASE

This entity records information about a Purchase receive from a Supplier.

Attributes Description
ID PRIMARY KEY
Store_ID Lookup for the Store at which the Purchase is recorded. NOT NULL
Supplier_ID Lookup for the Supplier of the purchased items. NOT NULL
Entry_Stock_Time Timestamp when the Purchase Items quantities are added to stock. NOT NULL
Description Purchase description.
Is_Paid Flag attribute to indicate if Purchase is paid. NOT NULL
Comments Internal notes used for clarifications on record content.

Entity: PURCHASE_ITEM

This entity records information about all Purchase Items assigned to a Purchase.

Attributes Description
ID PRIMARY KEY
Purchase_ID Lookup for the Purchase to which the Purchase Item is assigned. NOT NULL
Item_ID Lookup for the Item added to Purchase. NOT NULL
Line_No The sequential number of a Purchase line. A Purchase can have many lines. Each Purchase line has a sequential number: 1, 2, 3 etc. This attribute can help to sort the Purchase lines and also to reference a Purchase line (for example when Store Employee and Supplier review a Purchase) etc. NOT NULL
Quantity Item Quantity added to Purchase. NOT NULL.
Supplier_Price_Before_Tax Price before tax. NOT NULL
Supplier_Tax_Value Tax Value applied. NOT NULL
Supplier_Price_After_Tax Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Supplier_Discount_Value Discount value applied. NOT NULL
Supplier_Price Purchase Item final price. NOT NULL
Description Purchase Item description.
Comments Internal notes used for clarifications on record content.

Entity: STOCK_RETURN

This entity records information about a Stock Return to a Supplier.

Attributes Description
ID PRIMARY KEY
Store_ID Lookup for the Store at which the Stock Return is recorded. NOT NULL
Purchase_ID Lookup for the Purchase for which the Stock Return is recorded. NOT NULL
Supplier_ID Lookup for the Supplier. NOT NULL
Exit_Stock_Time Timestamp when the Stock Return Items quantities are decreased from stock. NOT NULL
Description Stock Return description.
Is_Paid Flag attribute to indicate if Stock Return is paid by Supplier. NOT NULL
Comments Internal notes used for clarifications on record content.

Entity: STOCK_RETURN_ITEM

This entity records information about all Stock Return Items assigned to a Stock Return.

Attributes Description
ID PRIMARY KEY
Stock_Return_ID Lookup for the Stock Return to which the Stock Return Item is assigned. NOT NULL
Item_ID Lookup for the Item assigned to the Stock Return. NOT NULL
Line_No The sequential number of a Stock Return line. A Stock Return can have many lines. Each Stock Return line has a sequential number: 1, 2, 3 etc. This attribute can help to sort the Stock Return lines and also to reference a Stock Return line (for example when Store Employee and Supplier review a Stock Return) etc. NOT NULL
Quantity Item Quantity assigned to Stock Return. NOT NULL.
Supplier_Price_Before_Tax Price before tax. NOT NULL
Supplier_Tax_Value Tax Value applied. NOT NULL
Supplier_Price_After_Tax Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Supplier_Discount_Value Discount value applied. NOT NULL
Supplier_Price Price paid at purchase. NOT NULL
Return_Amount Amount paid by Supplier at return. NOT NULL
Description Stock Return Item description.
Comments Internal notes used for clarifications on record content.

Entity: LOSS_AND_DAMAGE

List of Loss and Damage evaluations performed at a Store.

Attributes Description
ID PRIMARY KEY
Store_ID Lookup for the Store at which the Loss and Damage evaluation was performed. NOT NULL
Exit_Stock_Time Timestamp when the results of the Loss and Damage evaluation were applied to Stock.
Description Loss and Damage evaluation description.
Comments Internal notes used for clarifications on record content.

Entity: LOSS_AND_DAMAGE_ITEM

List of Items evaluated during a Loss and Damage evaluation performed at a Store.

Attributes Description
ID PRIMARY KEY
Loss_and_Damage_ID Lookup for the Loss and Damage evaluation to which the evaluated Item is assigned. NOT NULL
Item_ID Lookup for the Item that was evaluated. NOT NULL
Quantity Item quantity evaluated as Loss and Damage. NOT NULL .
Description Item evaluation description.
Comments Internal notes used for clarifications on record content.

Accelerate every database schema design with powerful data models

We have already created the database diagram and prepared the SQL DDL scripts to deploy your database schema. Download a free Demo edition and evaluate it in your own database.

Try Data Model Pack freeView pricing