Point Of Sale | Subject area: ORDER
Help Docs → Point 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 Parameters 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.
© 2025 Data Model Pack · Terms of Use · Privacy