E-commerce | Subject area: DOCUMENT

Help Docs → E-commerce → Subject area: DOCUMENT

Subject area: DOCUMENT

Document

E-commerce Document Data Model

Sale Order

Important

Each Sale Order (ORDER_HEADER) record must have a Document record assigned with Document Type '200 - Sale Order'.

Important

For each Sale Order Item (ORDER_LINE) assigned to a Sale Order (ORDER_HEADER), must update the STOCK_HISTORY with the Item quantity that was sold. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

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

Sale Return

E-commerce Sale Return Data Model

Purchase

E-commerce Purchase Data Model

Stock Return

E-commerce Stock Return Data Model

Inventory Count

Important

Each Inventory Count record must have a Document record assigned with Document Type '300 - Inventory Count'.

Important

For each Inventory Count Item assigned to an Inventory Count, must update the STOCK_HISTORY to reflect the Item quantity resulted after Inventory Count. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

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

Loss and Damage

E-commerce Loss and Damage Data Model

Entity: DOCUMENT_CATEGORY

List of Document Categories.

Attributes
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
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

List of documents recorded at a Store.

Attributes
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.

Important

Each Sale Return record must have a Document record assigned with Document Type '210 - Sale Return'.

Attributes
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.

Important

For each Sale Return Item assigned to a Sale Return must update the STOCK_HISTORY with the returned Item quantity. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Attributes
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
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.

Important

Each Purchase record must have a Document record assigned with Document Type '100 - Purchase'.

Attributes
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.

Important

For each Purchase Item added to a Purchase must update the STOCK_HISTORY with the received Item quantity. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Attributes
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.

Important

Each Stock Return record must have a Document record assigned with Document Type '120 - Stock Return'.

Attributes
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.

Important

For each Stock Return Item assigned to a Stock Return must update the STOCK_HISTORY with the returned Item quantity. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Attributes
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.

Important

Each Loss and Damage record must have a Document record assigned with Document Type '400 - Loss and Damage'.

Attributes
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.

Important

For each Item assigned to a Loss and Damage, must update the STOCK_HISTORY with the Item quantity that resulted as Loss and Damage during evaluation. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Attributes
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 designed the data model and prepared the SQL scripts for creating the database schema objects.

Try Data Model Pack Free

Data Model Pack

Product

Resources

Support

© 2025 Data Model Pack · Terms of Use · Privacy

Top