Point Of Sale | Subject area: ORDER

Help DocsPoint Of Sale → Subject area: ORDER

Subject area: ORDER

Order Process and Status

The Order Process and Status Diagram details the process steps and the Order status workflow from the moment when a NEW Order is created till the moment the Order is acknowledged COMPLETE.

Order Header entities

Order Line entities

Order Parameters entities

Order Shipment entities

Entity: ORDER_HEADER

This entity records information about all Orders.

Attributes
ID PRIMARY KEY
Store_ID Lookup for the Store at which the Order was placed. The same database can handle multiple Stores. NOT NULL
Sales_Channel_ID Lookup for the Sales Channel through which the Order was placed: ONLINE, PHONE, IN_STORE, DRIVE_IN. See Entity: SALES_CHANNEL for more information. NOT NULL
Delivery_Type_ID Lookup for the Delivery Type that was choosen for the Order: SHIPMENT, PICK_UP. See Entity: DELIVERY_TYPE for more information. NOT NULL
Payment_Method_ID Lookup for the Payment Method that was choosen for the Order CASH, CARD, DIGITAL. See Entity: PAYMENT_METHOD for more information. NOT NULL
Payment_Time_ID Lookup for the Payment Time that was choosen for the Order: IN_ADVANCE, AT_HANDOFF. See Entity: PAYMENT_TIME for more information. NOT NULL
Order_No Store internal number for the Order. Cannot have two orders with the same Order_No. NOT NULL UNIQUE
Customer_ID Lookup for the Customer of the Order. This field is NULL when the Order was created for an ANONYMOUS Customer by a Store Employee. See Entity: CUSTOMER for more information.
Loyalty_Card_ID Lookup for the Loyalty Card assigned to the Customer of the Order.
This field is NULL in the following situations:
⦁ the Order was placed for an ANONYMOUS Customer by a Store Employee
⦁ the REGISTERED_ONLINE or GUEST Customer does not have a Loyalty Card assigned.
See Entity: CUSTOMER for more information.
Created_Emp_Login_ID Lookup for the Store Employee who created the Order. NOT NULL
Created_Customer_ID Lookup for the REGISTERED_ONLINE Customer who created the Order. This field is NULL when the Order was created for an ANONYMOUS or GUEST Customer by a Store Employee. See Entity: CUSTOMER for more information.
Created_Time Timestamp when Order was created. NOT NULL
Submitted_Emp_Login_ID Lookup for the Store Employee who Submitted the Order.
Approved_Emp_Login_ID Lookup for the Store Employee who Approved the Order.
Managed_Emp_Login_ID Lookup for the Store Employee who managed and has taken care of Order at all stages.
Note: The same Store Employee can be granted with 'Submit Orders' and 'Manage Orders' responsibilities. In this case Submitted_Emp_Login_ID and Managed_Emp_Login_ID are the same. See Subject Area: EMPLOYEE AND ROLE for more information.
Customer_Notes Customers can add notes to Order (for example information about preferred payment method etc. )
Price_Before_Tax Order total price calculated before Tax. NOT NULL
Total_Tax_Value Order total Tax Value. NOT NULL
Price_After_Tax Order total price calculated after Tax. Price_After_Tax = Price_Before_Tax + Total_Tax_Value. NOT NULL
Price_Before_Discount Order total price calculated before Discount. NOT NULL
Order_Items_Discount Order discount calculated as sum of discount values applied at Order Lines level. See Entity: ORDER_LINE, Entity: ORDER_LINE_DISCOUNT and Entity: DISCOUNT for more information. NOT NULL
Order_Discount Order discount value applied at total Order Value ( for example a discount of 25% from total Order value). See Entity: ORDER_DISCOUNT and Entity: DISCOUNT for more information. NOT NULL
Total_Discount_Value Total Discount Value = Order_Items_Discount + Order_Discount. NOT NULL
Price_After_Discount Order total price calculated after Discount. NOT NULL
Price_Adjustment Store Employee can apply a price adjustment at Order level.
Price_Adjustment_Reason Reason for price adjustment.
Price Order final price resulted after taxes, discounts and adjustments were applied. NOT NULL
Latest_Status Latest status of Order. Every time when Order status changes, application should automatically update this attribute and also insert the Order latest status in the ORDER_STATUS_HISTORY entity.
See Entity: ORDER_STATUS_HISTORY for more information. NOT NULL
Latest_Status_Update The timestamp when the Order entered the latest status. Every time when Order status changes, application should automatically update this attribute and also update the ORDER_STATUS_HISTORY.Start_Time attribute for the latest (most recent) Order status. The ORDER_HEADER.Latest_Status_Update attribute should be always synchronized with the ORDER_STATUS_HISTORY.Start_Time attribute for the latest (most recent) Order status.
See Entity: ORDER_STATUS_HISTORY for more information. NOT NULL
Is_Submitted Flag attribute to indicate if Order has been submitted. NOT NULL
Submitted_Time Timestamp when Order was submitted. If Is_Submitted is FALSE then Submitted_Time is NULL.
Is_Approved Flag attribute to indicate if Order has been approved. NOT NULL
Approved_Time Timestamp when Order was Approved. If Is_Approved is FALSE then Approved_Time is NULL.
Is_Canceled Flag attribute to indicate if the Order is Canceled.
Important: An Order can be Canceled before the Order reaches the READY status. After Order reaches the READY status, a Sale Return must be processed. When an Order is Canceled, all the Order Lines are Canceled. See Subject Area: INVENTORY AND STOCK for more information.
Example: Store Employee presses the Cancel Order button. In this case:
Is_Canceled flag changes from FALSE to TRUE
⦁ at the same time the Order Status changes to CANCELED.
⦁ at the same time, the application should insert automatically a record with the most recent Order status (CANCELED) in the ORDER_STATUS_HISTORY entity. NOT NULL
Canceled_Time Timestamp when Order was Canceled.
Cancel_Reason Order Cancel reason.
Is_Scheduled Flag attribute to indicate if the Customer scheduled the Order for later.
Example:
⦁ If Is_Scheduled is TRUE then Customer scheduled the Order for later.
⦁ If Is_Scheduled is FALSE then Order should be delivered immediately. NOT NULL
Scheduled_Time Timestamp for when Order is scheduled.
⦁ If Is_Scheduled is TRUE then must contain the expected Scheduled Time.
⦁ If Is_Scheduled is FALSE then is NULL. See Order Process and Status, Step: Customer Places Order for more information.
Is_Ready Flag attribute to indicate if Order is Ready for delivery.
Important:
When Order has reached this status:
⦁ the Order Items were decreased in stock. Any Cancel or Return request after th Order Is Ready should be registered and processed as a Sale Return. See Subject Area: INVENTORY AND STOCK for more information.
⦁ The Order Invoice is issued See Subject Area: PAYMENT AND INVOICE for more information.
⦁ The Order Payment is claimed. If Order was paid IN_ADVANCE, any Cancel or Return request after the Order Is Ready must be followed by a Refund. See Subject Area: DOCUMENT Entity: SALE_RETURN, Entity: REFUND for more information. NOT NULL
Ready_Time Timestamp when the Order is Ready for delivery.
Is_Delivered Flag attribute to indicate if Order has been delivered. NOT NULL
Delivered_Time Timestamp when Order was delivered.
Important:
ORDER_HEADER.Delivery_Time and SHIPMENT.Shipped_Time should have the same value for an Order with Delivery Type SHIPMENT. See Entity: SHIPMENT for more information
Is_Paid Flag attribute to indicate if Order is paid. NOT NULL
Payment_Time Timestamp when Order was paid. If Is_Paid is FALSE then Payment_Time is NULL.
Is_Completed Flag attribute to indicate when:
⦁ Order is delivered, paid and Customer acknowledged Order completed.
⦁ at the same time the Order Status changes to COMPLETE.
⦁ at the same time application should automatically insert a record with the most recent Order status (COMPLETE) in the ORDER_STATUS_HISTORY entity. NOT NULL
Completed_Time Timestamp when Order has reached COMPLETE status.
Return_Required Flag attribute to indicate if Customer has required to return a part or all of Order Items after the Order reached the Ready status. In this case a Sale Return should be processed. See Subject Area: INVENTORY AND STOCK for more information. NOT NULL
Return_Time Timestamp when Customer requested the Order Items return.
Comments Internal notes used for clarifications on record content.

Entity: ORDER_DISCOUNT

This entity records the discounts applied at ORDER level (for example 25% discount from Order total value).

Note

The discounts applied at Order Line are recorded in the ORDER_LINE_DISCOUNT entity.

Attributes
ID PRIMARY KEY
Discount_ID Lookup for the Discount to be applied at ORDER level. See Entity: DISCOUNT for more information. NOT NULL
Order_ID Lookup for the Order to which the discount is applied. NOT NULL
Value Calculated Value of the Discount. See Entity: DISCOUNT for more information about discount calculation. NOT NULL
Comments Internal notes used for clarifications on record content.

Entity: ORDER_STATUS

List of all status types an Order can pass through.

Attributes
ID PRIMARY KEY
Name Order status type. NOT NULL UNIQUE. Can't have two status types with the same name.
Description Order status type description.
Is_Active Flag attribute to indicate if the status type is still used to control Order process steps or it was canceled permanently. NOT NULL

Example

ORDER_STATUS

Name
NEW
SUBMITTED
APPROVED
IN PROGRESS
CANCELED
READY
DELIVERED
PAID
COMPLETE

Entity: ORDER_STATUS_HISTORY

This entity records the full status history for an ORDER.

Note

Every time when the Order Status changes from an old Order Status to a new Order Status, the application should automatically:

  • Create a record in the ORDER_STATUS_HISTORY entity to add the new Order Status and the Start Time of the new Order Status.
  • Update the record of the old Order Status in the ORDER_STATUS_HISTORY entity:

    Synchronize the ORDER_STATUS_HISTORY.End_Time of the old Order Status with the ORDER_STATUS_HISTORY.Start_Time of the new Order Status.

Attributes
ID PRIMARY KEY
Order_ID Lookup for the Order to which the Order Status is assigned. NOT NULL
Order_Status_ID Lookup for the Order Status to be assigned. NOT NULL
Start_Time Timestamp when Order entered the status. NOT NULL
End_Time Timestamp when Order exit the status. When End_Time is NULL, the Order is still in that status.

Entity: ORDER_REVIEW

This entity records Customer reviews on Orders.

Note

Customers who ordered ONLINE can rate their Order experience on a scale from 1 to 5 and provide Order Reviews.

Attributes
ID PRIMARY KEY
Order_ID Lookup for the Order for which the Order review is recorded. NOT NULL
Customer_ID Lookup for the Customer who recorded the Order review. NOT NULL
Rating Rating from 1 to 5. NOT NULL
Review Order review text filled in by Customer. NOT NULL
Review_Time Timestamp when the Order review was created. NOT NULL
Reply Reply text from Store Employee to Customer review.
Reply_Time Timestamp when the reply to Customer review was created.
Reply_Emp_Login_ID Lookup for the Store Employee who replied to Order review.
Is_Compensated Flag attribute to indicate if Customer received a money compensation for the Review.
Example:
When a Customer sent a negative review for an Order that did not meet the expectations, the Store can decide to offer a money compensation to Customer.
⦁ If Customer received a compensation for the Review then Is_Compensated attribute is set TRUE.
⦁ If Customer did not receive a compensation for the Review then Is_Compensated attribute is set FALSE. NOT NULL
Compensation_Value The money amount received by the Customer as a compensation for the Order Review.
⦁ If Is_Compensated is TRUE then the Compensation_Value is the money amount expressed in Store currency received by Customer as compensation. This money amount is also added to the Customer total Credit.
Customer can use the Credit with the next Order placed at the Store. See Subject Area: CUSTOMER, Entity: CUSTOMER, Attribute: Credit.
⦁ If Is_Compensated is FALSE then Compensation_Value is NULL.
Comments Internal notes used for clarifications on record content.

Entity: ORDER_LINE

This entity records information about all Order Lines assigned to an Order. Each time when an Item is added to an Order, an Order Line is created and assigned to the Order.

Attributes
ID PRIMARY KEY
Store_ID Lookup for the Store at which the Order was created. NOT NULL
Order_ID Lookup for the Order to which the Order Line is assigned. NOT NULL
Item_ID Lookup for the Item added to Order. NOT NULL
Line_No The sequential number of an Order Line. An Order can have many lines. Each Order Line has a sequential number: 1, 2, 3 etc. This attribute can help to sort the Order lines and also to reference an Order Line (for example when Store Employee and Customer review an Order) etc. NOT NULL
Description Order Line description.
Customer_Notes Customer Notes at Order Line level.
Quantity Item Quantity added to Order. NOT NULL.
Current_Item_Cost Cost of the Item calculated after last purchase from supplier. See Subject Area: INVENTORY AND STOCK for more information. NOT NULL
Markup_Percentage Markup Percentage applied to Item. See Subject Area: INVENTORY AND STOCK for more information. NOT NULL
Price_Before_Tax Order Line price calculated before Tax. Example: Price_Before_Tax = Current_Item_Cost + Current_Item_Cost*Markup_Percentage/100. NOT NULL
Tax_Value Order Line Tax Value. NOT NULL
Price_After_Tax Order Line price calculated after Tax. Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Price_Before_Discount Order Line price before Discount. NOT NULL
Discount_Value Order Line discount value calculated as a sum of all discounts applied for that Order Line. See Entity: ORDER_LINE_DISCOUNT and Entity: DISCOUNT for more information. NOT NULL
Price_After_Discount Order Line price calculated after Discount. Price_After_Discount = Price_Before_Discount - Discount_Value. NOT NULL
Price_Adjustment Store Employee can apply a price adjustment at Order Line level.
Price_Adjustment_Reason Reason for price adjustment.
Price Order Line final price. NOT NULL
Is_Canceled Flag attribute to indicate if the Order Line is Canceled. An Order Line can be Canceled before the Order reaches the READY status. After Order reaches the READY status, a Sale Return must be processed. See Subject Area: INVENTORY AND STOCK for more information. NOT NULL
Canceled_Time Timestamp when Order Line was Canceled.
Cancel_Reason Order Line Cancel reason.
Return_Required Flag attribute to indicate if Customer has required to return a part or all of the Order Line items after the Order reached the Ready status. In this case a Sale Return should be processed. See Subject Area: INVENTORY AND STOCK for more information. NOT NULL
Return_Quantity The Order Line item quantity to be returned.
Return_Time Timestamp when Customer requested the return.
Customer_Review The REGISTERED_ONLINE Customer who created an Order ONLINE, can add a review at Order Line level.
Note: Customer can add a review (edit the Customer_Review attribute) only after the Order reaches the Order Status: COMPLETE.
Customer_Like The REGISTERED_ONLINE Customer who created an Order ONLINE can edit a Like/Unlike flag at Order Line level. Customer can edit the Customer_Like flag only after the Order reaches the Order Status: COMPLETE.
Comments Internal notes used for clarifications on record content.

Entity: ORDER_LINE_DISCOUNT

This entity records the discounts applied at ORDER LINE level.

Note

The discounts applied at Order Header level are recorded in the ORDER_DISCOUNT entity.

Attributes
ID PRIMARY KEY
Discount_ID Lookup for the Discount to be applied for the ORDER LINE. See Entity: DISCOUNT for more information. NOT NULL
Order_Line_ID Lookup for the Order Line to which the discount is applied. NOT NULL
Value Calculated Value of the Discount. See Entity: DISCOUNT for more information on calculation formula. NOT NULL
Comments Internal notes used for clarifications on record content.

Entity: SALES_CHANNEL

List of Sales Channels.

Attributes
ID PRIMARY KEY
Name Sales Channel name. NOT NULL UNIQUE. Can't have two Sales Channels with the same name.
Description Sales channel description.
Is_Active Flag attribute to indicate if the Sales Channel is still available or it was canceled permanently. NOT NULL

Example

SALES_CHANNEL

Name Description
ONLINE The REGISTERED_ONLINE Customer places an Order on Store website.
PHONE The GUEST Customer makes a phone call to the Store to place an Order. Store Employee answers the call, asks for the mandatory information about Customer and places the Order.
IN_STORE The ANONYMOUS Customer steps into Store and asks to place an Order. Store Employee places the Order on customer behalf.
DRIVE_IN The ANONYMOUS Customer drives to Store ordering window / microphone station and asks to place an Order. Store Employee places the Order on customer behalf.

Entity: DELIVERY_TYPE

List of Delivery Types.

Attributes
ID PRIMARY KEY
Name Delivery Type name. NOT NULL UNIQUE. Can't have two Delivery Types with the same name.
Description Delivery Type description.
Is_Active Flag attribute to indicate if the Delivery Type is still available, or it was canceled permanently. NOT NULL

Example

DELIVERY_TYPE

Order can be handed-off to Customer through many Delivery Types:

Name Description
SHIPMENT Order is delivered to Customer at Shipment_Address. See Entity: SHIPMENT, Attribute: Shipment_Address for more information.
PICK_UP Order is picked-up by Customer from Store.

Entity: PAYMENT_METHOD

List of payment methods.

Attributes
ID PRIMARY KEY
Name Payment method name. NOT NULL UNIQUE. Can't have two payment methods with the same name.
Code Payment method code. NOT NULL UNIQUE
Sequence_No The sequence number helps to order the Payment methods list when displayed.
Is_Active Flag to indicate if the Payment Method is still applicable or it was suspended. NOT NULL
Is_Customer_Required Flag to indicate if Customer must be selected (information about Customer is required) before the Payment with selected Payment Method is applied. NOT NULL
Description Payment method description.

Example

PAYMENT_METHOD

Name
CASH
CARD
Paypal
Stripe
etc.

Entity: PAYMENT_TIME

List of payment time (when can be an Order paid). Example: IN_ADVANCE, AT_HANDOFF

Attributes
ID PRIMARY KEY
Name Payment Time name. NOT NULL UNIQUE. Can't have two payment times with the same name.
Description Payment Time description.
Is_Active Flag attribute to indicate if the Payment Time is still available or it was canceled permanently. NOT NULL

Example

PAYMENT_TIME

Name
IN_ADVANCE
AT_HANDOFF
etc.

Entity: PAYMENT_TERM

The PAYMENT_TERM entity implements the allowed combinations of SALES_CHANNEL, DELIVERY_TYPE, PAYMENT_METHOD, PAYMENT_TIME for an Order.

Every time when a Customer or a Store Employee is selecting the Delivery Type, Payment Time and Payment Method for an Order, the application should automatically check if that combination plus the Order Sales Channel is an allowed combination recorded in the PAYMENT_TERM entity.

Attributes
ID PRIMARY KEY
Sales_Channel_ID Lookup for the Sales Channel. NOT NULL
Delivery_Type_ID Lookup for the Delivery Type. NOT NULL
Payment_Method_ID Lookup for the Payment Method. NOT NULL
Payment_Time_ID Lookup for the Payment Time. NOT NULL
Is_Allowed Flag attribute to indicate if the combination of Sales Channel, Delivery Type, Payment Method, Payment Time makes sense and is allowed. FALSE - is not allowed, TRUE - is allowed. NOT NULL
Is_Active Flag attribute to indicate if the combination of Sales Channel, Delivery Type, Payment Method, Payment Time is still available or it was canceled permanently. NOT NULL
Comments Internal notes used for clarifications on record content.

CONSTRAINT UK_Payment_Term UNIQUE (Sales_Channel_ID, Delivery_Type_ID, Payment_Method_ID, Payment_Time_ID).
Cannot have the same combination of Sales Channel, Delivery Type, Payment Method, Payment Time recorded twice in the PAYMENT_TERM entity.

Example

Entity: PAYMENT_TERM

SALES CHANNEL DELIVERY TYPE PAYMENT METHOD PAYMENT TIME Is Allowed
ONLINE SHIPMENT CASH IN_ADVANCE FALSE
ONLINE SHIPMENT CASH AT_HANDOFF TRUE
ONLINE SHIPMENT CARD IN_ADVANCE TRUE
ONLINE SHIPMENT CARD AT_HANDOFF TRUE
etc

For example the below combination does not make sense and is outlined as Not allowed. For an Order placed ONLINE and delivered through SHIPMENT it's impossible to make a CASH payment IN_ADVANCE.

SALES CHANNEL DELIVERY TYPE PAYMENT METHOD PAYMENT TIME Is Allowed
ONLINE SHIPMENT CASH IN_ADVANCE FALSE

Important

Is the role of application Administrator to maintain the SALES_CHANNEL, DELIVERY_TYPE, PAYMENT_METHOD, PAYMENT_TIME, PAYMENT_TERM entity records. See Subject Area: EMPLOYEE AND ROLE Entity: ADMIN_USER for more information.

The data model is flexible enough to accommodate new Sales Channels, Delivery Types etc. The Administrator can add new Sales Channels, Delivery Types, Payment Methods (for example can add a BANK_WIRED_TRANSFER Payment Method etc).

Entity: SHIPMENT

This entity records information about Order shipment for an Order with Delivery Type SHIPMENT.

Note

The Data Model is flexible enough to accommodate the following scenarios:

⦁ The REGISTERED_ONLINE Customer can place an Order through ONLINE Sales Channel and select the Delivery Type: SHIPMENT

⦁ The GUEST Customer can place an Order through PHONE Sales Channel and select the Delivery Type: SHIPMENT

⦁ The ANONYMOUS Customer steps into Store and asks to place an Order with Delivery Type: SHIPMENT at a Shipment Address. In this case the Store Employee records the Customer as a GUEST Customer and the process continues as for a GUEST Customer.

See Order Process and Status, Step: Customer Places Order and Step: Store Employee Assigns Shipment to Order.

Attributes
ID PRIMARY KEY
Store_ID Lookup for the Store where the Shipment request was registered. NOT NULL
Order_ID Lookup for the Order that should be delivered through Shipment. NOT NULL
Shipment_City_ID Lookup for the City where the Order should be delivered. NOT NULL
Shipment_Address Shippment Address where the Order should be delivered. NOT NULL
GPS_Location GPS location where the Order should be delivered.
Postal_Code Postal code of the location where the Order should be delivered.
Shipment_Notes Customer notes on Shipment. For example can contain details about how to reach on Shipment Address etc.
Picked_up_Time Timestamp when Order was picked-up from Store for Shipment.
Note:
For the efficient Stores, the time interval between Order is Ready (ORDER_HEADER.Ready_Time) and Order is Picked-up (SHIPMENT.Picked_up_Time) should be close to 0. NULL
Shipped_Time Timestamp when the Order was handed-off to Customer at Shipment_Address. When Shipped_Time is NULL, Order has NOT been yet delivered. NULL
Comments Internal notes used for clarifications on record content. Can be seen and edited only by Store Employees

Important

SHIPMENT.Shipped_Time and ORDER_HEADER.Delivery_Time should have the same value for an Order with Delivery Type SHIPMENT

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

© 2025 Data Model Pack · Terms of Use · Privacy

Top