The Logical Data Model of the ORDER Subject Area ensures support for the following functionalities:

1. Order can be created ONLINE

The REGISTERED_ONLINE Customer login on Store website and places an Order.

2. Order can be handed-off to Customer through SHIPMENT

Order is delivered to Customer at SHIPMENT.Shipment_Address. See Entity: SHIPMENT, Attribute: Shipment_Address for more information.

3. Order can be paid with CARD or DIGITAL (Stripe, PayPal, digital wallets etc)

4. Order must be paid IN_ADVANCE

The Order Process and Status Diagram details the process steps and the Order statuses from the moment the Customer initiates a NEW Order till the moment the Customer acknowledges the Order COMPLETE:

Process Steps and Use Cases

Case: Customer Places Order through ONLINE Sales Channel

A REGISTERED_ONLINE Customer can login on Store website and place an Order.

See Subject Area: CUSTOMER, Entity: CUSTOMER for more information.

Step: Customer Places Order

Actions:

⦁ Customer login on Store website.

⦁ Customer chooses the Order type: NOT_SCHEDULED or SCHEDULED

⦁ If Customer chooses NOT_SCHEDULED Order then the Order must be delivered as soon as possible. In this case:

Order status is initialized to NEW.

Order Status: NEW

⦁ If Customer chooses SCHEDULED Order then the Order can be delivered later, at a time specified by Customer. For example Customer can place the Order in the morning and schedule the delivery for the evening. In this case:

ORDER_HEADER.Is_Scheduled attribute is set TRUE

ORDER_HEADER.Scheduled_Time attribute is set to the estimated time for delivery. See Entity: ORDER_HEADER.

Order status is initialized to NEW_SCHEDULED.

Order Status: NEW_SCHEDULED

⦁ Customer is asked to make Payment IN_ADVANCE. Next step is:

Step: Customer Makes PAYMENT IN_ADVANCE

Actions:

⦁ Customer should fill in the payment details and then press a Submit payment button.

⦁ Payment is validated through the payment processor (external system).

⦁ ORDER_HEADER.Is_Paid attribute is set TRUE. See Entity: ORDER_HEADER for more information.

⦁ All the PAYMENT attributes are recorded in the PAYMENT entity.

⦁ All the INVOICE attributes are recorded in the INVOICE entity.
See Subject Area: PAYMENT AND INVOICE for more information.

Next step is:

Step: Customer Receives Invoice

Actions:

⦁ Customer can view and download the INVOICE.
See Subject Area: PAYMENT AND INVOICE for more information.

Next step is:

Step: Store Employee Reviews Order

In this step Store Employee reviews the Orders queue.

The Review Order step outcome can be:

Case 1: Store Employee does not confirm the Order

For example Store Employee does not confirm the Order when the Store does not have enough ingredients to prepare the Order (an Item is not in stock).

In this case Order can be Canceled.

Step: Store Employee Cancels Order

Actions:

⦁ Store Employee should fill in the ORDER_HEADER.Cancel_Reason and then press a Cancel Order button.

⦁ When Store Employee presses the Cancel Order button and the CANCEL event occurs, the application should check if ORDER_HEADER.Is_Paid attribute is TRUE. That means the Order was already paid IN_ADVANCE.

⦁ If ORDER_HEADER.Is_Paid attribute is TRUE, then the next steps are:

Step: Store Employee Cancels Payment and

Step: Store Employee Cancels Invoice

See Subject Area: PAYMENT AND INVOICE, Entity: PAYMENT, Entity: INVOICE for more information.

⦁ Order Status changes to CANCELED.

Order Status: CANCELED

Case 2: Store Employee confirms a NEW_SCHEDULED Order

Step: Store Employee Confirms a NEW_SCHEDULED Order

Actions:

⦁ Store Employee browses the Orders queue and presses the Confirm Order button for an Order in NEW_SCHEDULED status. An Order in NEW_SCHEDULED status has the ORDER_HEADER.Is_Scheduled attribute set TRUE. This is an Order with preparation scheduled for later. See Entity: ORDER_HEADER for more information.

In this case:

Order Status changes from NEW_SCHEDULED to SCHEDULED.

Order Status: SCHEDULED

At the time the SCHEDULED Order preparation should start:

Step: Store Employee Starts Prepare Order

Actions:

Store Employee presses the Start Prepare Order button to confirm Order preparation started.

Order Status changes from SCHEDULED to IN PROGRESS.

Order Status: IN_PROGRESS

Order passes to next step. See Step: Store Employee Prepares Order

Case 3: Store Employee confirms a NEW Order

Step: Store Employee Confirms a NEW Order

Actions:

⦁ Store Employee browses the Orders queue and presses the Confirm Order button for an Order in NEW status. An Order in NEW status has the ORDER_HEADER.Is_Scheduled attribute set FALSE. This is an Order with immediate preparation. See Entity: ORDER_HEADER for more information.

In this case:

Order Status changes from NEW to IN_PROGRESS.

Order Status: IN_PROGRESS

Order passes to next step. See Step: Store Employee Prepares Order

Step: Store Employee Prepares Order

Actions:

⦁ At this step, Store Employees prepare all the Orders that have IN_PROGRESS status. An Order remains in the IN_PROGRESS status during the Prepare Order step.

The Prepare Order step duration is impacted by the following ORDER_HEADER attributes:

Placed_Time

Scheduled_Time

Delayed_Time

Ready_Time

and the SETTING attributes:

Average_Preparation_Time

Busy_Preparation_Time

Prepare Order duration for an usual (not scheduled) Order

Start: Placed_Time

Duration: Average_Preparation_Time

End: Placed_Time + Average_Preparation_Time = Ready_Time

Prepare Order duration for a scheduled Order

Start: Scheduled_Time - Average_Preparation_Time

Duration: Average_Preparation_Time

End: Scheduled_Time= Ready_Time

Prepare Order duration for an usual (not scheduled) Order that is delayed

Start: Placed_Time

Duration: Average_Preparation_Time+Delayed_Time

End: Placed_Time + Average_Preparation_Time+Delayed_Time = Ready_Time

Prepare Order duration for scheduled Order that is delayed

Start: Scheduled_Time - Average_Preparation_Time

Duration: Average_Preparation_Time+Delayed_Time

End: Scheduled_Time+Delayed_Time= Ready_Time

Prepare Order duration for an usual (not scheduled) Order when Is_Busy_Mode TRUE

Start: Placed_Time

Duration: Busy_Preparation_Time

End: Placed_Time + Busy_Preparation_Time = Ready_Time

Prepare Order duration for scheduled Order when Is_Busy_Mode TRUE

Start: Scheduled_Time - Busy_Preparation_Time

Duration: Busy_Preparation_Time

End: Scheduled_Time+Busy_Preparation_Time= Ready_Time

Step: Store Employee Finishes Prepare Order

Actions:

⦁ At this step, Store Employee presses a Finish Prepare Order button to announce the Order Preparation step is finished, and the Order is ready to be handed-off:

• to a Shipment person. The Shipment person could be a Store Employee or a person from an external shipment company.

⦁ ORDER_HEADER.Is_Ready attribute is set TRUE. See Entity: ORDER_HEADER for more information.

⦁ Order Status changes from IN_PROGRESS to READY_FOR_SHIPMENT:

Order Status: READY_FOR_SHIPMENT

Next step is:

Step: Store Employee Assigns Shipment to Order

Actions:

⦁ Order is assigned to Shipment. See Entity: SHIPMENT for more information).

Step: Shipment person Ships the Order

Actions:

⦁ Order is handed-off to a shipment person.

⦁ Order is shipped to the SHIPMENT.Shipment_Address. See Entity: SHIPMENT for more information).

⦁ Order status changes from READY_FOR_SHIPMENT to SHIPPING:

Order Status: SHIPPING

Step: Shipment person Fulfills Order Hand-off

Actions:

⦁ Customer aknowledges that has received the Order content.

⦁ Order Status changes from SHIPMENT to HANDED_OFF.

Order Status: HANDED_OFF

Step: Customer Acknowledges Order Completed

Actions:

⦁ Customer aknowledges Order Completed.

⦁ Order Status changes from HANDED_OFF to COMPLETE.

Order Status: COMPLETE

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

Order content entities

Order status entities

Order shipment entities

Order review entities

This entity records information about all Orders.

Attributes
ID	PRIMARY KEY
Order_No	Order number that Store associates with the Order. Cannot have two orders with se same Order_No. NOT NULL UNIQUE
Store_ID	Lookup for the Store at which the Order was placed. The same application can handle multiple Stores. NOT NULL
Placed_Customer_ID	Lookup for the Customer who placed the Order. See Entity: CUSTOMER for more information. NOT NULL
Served_Emp_Login_ID	Lookup for the Store Employee who served the Order to Customer. Is the Store Employee who has taken care of Order at all stages: Reviewed the Order, Confirmed the Order, etc. See the ORDER PROCESS AND STATUS steps that required Store Employee actions. NOT NULL See Subject Area: EMPLOYEE AND ROLE for more information.
Customer_Notes	REGISTERED_ONLINE Customers can add notes to Order (for example information about preferred shipment time interval, etc.)
Placed_Time	Timestamp when Order was placed. NOT NULL
Price_Calculated	Price calculated for Order (the sum of all order lines prices). See Subject Area: PRICE AND DISCOUNT for more information. NOT NULL
Price_Adjustment	Employee Store can apply a price adjustment at Order level.
Price_Adjustment_Reason	Reason for price adjustment.
Subtotal	Price Calculated + Price Adjustment ( if any). NOT NULL
Tax	Tax calculated. NOT NULL
Total	Total calculated. 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_Paid	Flag attribute to indicate if Order has been paid. Example: ⦁ If Is_Paid is TRUE then Order is paid ⦁ If Is_Paid is FALSE then Order has not been paid. NOT NULL
Payment_Time	Timestamp when Order was paid. If Is_Paid is FALSE then Payment_Time is NULL.
Is_Scheduled	Flag attribute to indicate if the Customer scheduled the Order for later or not. 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_Preparation_Delayed	Flag attribute to indicate if Order preparation is delayed. Store Employee can delay a specific Order. Example: When the Online Food Ordering kitchen is very busy and does not want to delay all Orders but only a specific Order, the Store Employee can browse the Orders queue and press a Preparation Delayed button for that Order. In this case the Is_Preparation_Delayed flag is set TRUE for the selected Order. NOT NULL
Delayed_Time	Number of minutes the Order is delayed. Example: The Store Employee can choose the delay interval from a list of values: 5, 10, 15, 30 minutes. This delay interval is added to the SETTING.Average_Preparation_Time when calculate the Order preparation time. Optionally, for an ONLINE placed Order, the application can display this Delayed_Time to Customer. See Subject Area: STORE, Entity: SETTING and Order Process and Status - Step: Customer Places Order for more information.
Is_Canceled	Flag attribute to indicate if the Order is Canceled. Example: Store Employee presses the Cancel Order button to announce when Order is Canceled. In this case: ⦁ Is_Canceled flag changes from FALSE to TRUE ⦁ at the same time the Order Status changes from NEW or NEW_SCHEDULED 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. See the ORDER PROCESS AND STATUS - Step: Store Employee Cancels Order for more information. NOT NULL
Canceled_Time	Timestamp when Order was Canceled. NULL
Cancel_Reason	Cancel reason. See the ORDER PROCESS AND STATUS - Process Step: Cancel Order for more information.
Is_Ready	Flag attribute to indicate if the Order preparation is finished and the Order is ready for delivery. Example: Store Employee presses a Finish Prepare Order button to announce when Order preparation is finished. In this case: ⦁ Is_Ready flag changes from FALSE to TRUE. ⦁ at the same time the Order Status changes from IN_PROGRESS to READY_FOR_SHIPMENT. ⦁ at the same time, the application should insert automatically a record with the most recent Order status (READY_FOR_SHIPMENT) in the ORDER_STATUS_HISTORY entity. See the ORDER PROCESS AND STATUS - Step: Store Employee Finishes Prepare Order for more information. NOT NULL
Ready_Time	Timestamp when Order preparation was finished. NULL
Is_Completed	Flag attribute to indicate if Customer acknowledged Order Completed. Example: ⦁ Is_Completed flag changes from FALSE to TRUE when Order was handed-off to Customer, Order is paid, Customer received Invoice and Customer acknowledged Order Completed. ⦁ at the same time the Order Status changes from HANDED_OFF 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. See the ORDER PROCESS AND STATUS Step: Customer Acknowledges Order Completed for more information. NOT NULL
Completed_Time	Timestamp when Order was acknowledged Completed by Customer. NULL
Comments	Internal notes used for clarifications on record content. Can be seen and edited only by Store Employees

This entity records information about all the Order Lines assigned to an Order Header (Order). Each time when a menu Item or a menu Item Option is added to an Order, an Order Line is created.

Attributes
ID	PRIMARY KEY
Order_ID	Lookup for the Order Header to which the Order Line is assigned. NOT NULL
Item_ID	The Order Line record is created when an Item or an Item Option is added to an Order. When the Order Line is created for an Item, the Item_ID is the lookup to that Item. In this case the Item_Option_ID is NULL.
Item_Option_ID	The Order Line record is created when an Item or an Item Option is added to an Order. When the Order Line is created for an Item Option, the Item_Option_ID is the lookup to that Item Option. In this case the Item_ID is 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 the Store Employee and the Customer review an Order) etc. NOT NULL
Quantity	The number of the same menu Items / Item Options added to an Order Line (for example 5 x 'Espresso'). NOT NULL
Customer_Review	The REGISTERED_ONLINE Customer who placed 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 placed 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.
Is_Canceled	Flag attribute to indicate if the Order Line is Canceled. NOT NULL Note: When an Order is Canceled, all the Order Lines are canceled for that Order.
Canceled_Time	Timestamp when Order was Canceled and all the Order Lines were also Canceled. See the Order Process and Status - Step: Store Employee Cancels Order for more information. NULL
Cancel_Reason	Reason for which the Order Line was Canceled. Note: Can be copied from ORDER_HEADER.Cancel_Reason.
Comments	Internal notes used for clarifications on record content. Can be seen and edited only by Store Employees

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

This entity records the full status history for an ORDER.

Attributes
ID	PRIMARY KEY
Order_ID	Lookup for the Order to which the Order Status is assigned. NOT NULL
Order_Status_Type_ID	Lookup for the Order Status Type 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, it means Order is still in that status. NULL

This entity records information about the Order shipment.

Attributes
ID	PRIMARY KEY
Order_ID	Lookup for the Order that should be delivered. NOT NULL
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 Shippment Address 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

This entity records Customer reviews on Orders.

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_Date	Timestamp when the Order review was created. NOT NULL
Reply	Reply text from Store Employee to Customer review. NULL
Reply_Date	Timestamp when the reply to Customer review was created. NULL
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. Can be seen and edited only by Store Employees. NULL