Subject area: ORDER
Overview
The Logical Data Model of the ORDER Subject Area ensures support for the following functionalities:
1. Order can be created through many Sales Channels:
| ONLINE | The REGISTERED_ONLINE Customer login on Store website and places an Order. |
|---|---|
| PHONE | The GUEST Customer makes a phone call to the Store and asks to place an Order. Store Employee answers the call, fills in the mandatory information about Customer and places the Order on Customer behalf. |
| IN_STORE | The ANONYMOUS Customer steps into Store and asks to place an Order. A 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. A Store Employee places the Order on Customer behalf. |
| See Entity: SALES_CHANNEL and Entity: CUSTOMER for more information. | |
2. Order can be handed-off to Customer through many Delivery Types:
| SHIPMENT | Order is delivered to Customer at SHIPMENT.Shipment_Address. See Entity: SHIPMENT, Attribute: Shipment_Address for more information. |
|---|---|
| PICK_UP | Order is picked-up by Customer from Store. |
| DINE_IN | Order is served by Customer in Store. |
| See Entity: DELIVERY_TYPE for more information. | |
3. Order can be paid through many Payment Methods:
| CASH | |
|---|---|
| CARD | |
| DIGITAL | (Stripe, PayPal, digital wallets etc) |
| See Entity: PAYMENT_METHOD for more information. | |
4. Order can be paid:
| IN_ADVANCE | |
|---|---|
| AT_HANDOFF | |
| See Entity: PAYMENT_TIME for more information. | |
5. The PAYMENT_TERM entity implements the allowed combinations of SALES_CHANNEL, DELIVERY_TYPE, PAYMENT_METHOD, PAYMENT_TIME for an Order:
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 | ||||
| See Entity: PAYMENT_TERM for more information. | ||||
Order Process and Status
The Logical Data Model supports the Order processing for all the allowed combinations of SALES_CHANNEL, DELIVERY_TYPE, PAYMENT_METHOD, PAYMENT_TIME.
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 1: 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, Attribute: Is_Registered_Online 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 chooses the Delivery Type for Order
Customer chooses the Payment Method for Order
Customer chooses the Payment Time for Order
Customer chooses the Payment Time for Order: IN_ADVANCE or AT_HANDOFF.
If Customer chooses Payment Time IN_ADVANCE then next step is:
Step: Customer Makes PAYMENT IN_ADVANCE
Example
Every time when Customer selects 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 in the PAYMENT_TERM entity.
For example the below combination does not make sense and is outlined as Not allowed in the PAYMENT_TERM entity:
| SALES CHANNEL | DELIVERY TYPE | PAYMENT METHOD | PAYMENT TIME | Is Allowed |
|---|---|---|---|---|
| ONLINE | SHIPMENT | CASH | IN_ADVANCE | N |
The above combination is NOT allowed because for an Order placed ONLINE and delivered through SHIPMENT, it's impossible to make a CASH payment IN_ADVANCE.
But the below combination makes sense and is allowed:
| SALES CHANNEL | DELIVERY TYPE | PAYMENT METHOD | PAYMENT TIME | Is Allowed |
|---|---|---|---|---|
| ONLINE | SHIPMENT | CASH | AT_HANDOFF | Y |
Case 2: Customer Asks to Place Order. A Store Employee places an Order on Customer behalf
Step: Customer Asks to Place Order
Customer can make this request through one of the following Sales Channels:
| PHONE | The GUEST Customer makes a phone call to the Store to place an Order. Store Employee answers the call, fills in the mandatory attributes of Customer entity and receives the Order details. |
|---|---|
| IN_STORE | The ANONYMOUS Customer steps into Store and asks to place an Order. A Store Employee receives the Order details. |
| DRIVE_IN | The ANONYMOUS Customer drives to Store ordering window / microphone station and asks to place an Order. A Store Employee receives the Order details. |
Step: Store Employee Places Order
Store Employee Places Order based on Order details received from Customer.
Actions:
Select the Order type: NOT SCHEDULED or SCHEDULED
Select the Delivery Type
Select the Payment Method
Select the Payment Time: IN_ADVANCE or AT_HANDOFF
If chooses Payment Time: IN_ADVANCE than next step is:
Step: Store Employee Receives PAYMENT IN_ADVANCE
In this case:
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.
See Subject Area: PAYMENT AND INVOICE for more information.
Example
Customer steps into Store (Sales Channel: IN_STORE), asks Store Employee to place an Order, makes the payment cash (Payment Method: CASH) and in advance (Payment Time: IN_ADVANCE).
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 if the Order Delivery Type is SHIPMENT. The Shipment person could be a Store Employee or a person from an external shipment company.
- to a Customer if the Order Delivery Type is PICK_UP or DINE_IN
ORDER_HEADER.Is_Ready attribute is set TRUE. See Entity: ORDER_HEADER for more information.
if Order Delivery Type is SHIPMENT
Order Status changes from IN_PROGRESS to READY_FOR_SHIPMENT:
Order Status: READY_FOR_SHIPMENT
if Order Delivery Type is PICK-UP
Order Status changes from IN_PROGRESS to READY_FOR_PICK_UP:
Order Status: READY_FOR_PICK_UP
if Order Delivery Type is DINE-IN
Order Status changes from IN_PROGRESS to READY_FOR_DINE_IN:
Order Status: READY_FOR_DINE_IN
Next step depends on the Order Delivery Type: SHIPMENT, PICK_UP or DINE_IN.
Case 1: Order Delivery Type is SHIPMENT
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: Shipment person Checks if Order is Paid
Actions:
If ORDER_HEADER.Is_Paid attribute is FALSE then asks Customer to pay the Order.
Step: Customer Makes Payment AT_HANDOFF
In this case:
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.
See Subject Area: PAYMENT AND INVOICE for more information.
Step: Customer Receives Invoice
Step: Customer Acknowledges Order Completed
Actions:
Customer aknowledges Order Completed.
Order Status changes from HANDED_OFF to COMPLETE.
Order Status: COMPLETE
Case 2: Order Delivery Type is PICK-UP
Step: Customer Picks-up the Order
Actions:
Customer steps into Store to pick-up the Order.
Store Employee reviews the Orders queue and presses a button to confirm an Order with READY_FOR_PICK_UP status is waiting for Customer pick-up.
Order Status changes from READY_FOR_PICK_UP to PICKING-UP:
Order Status: PICKING-UP
Step: Store Employee Fulfills Order Hand-off
Actions:
Order content is handed-off to Customer.
Order Status changes from PICKING-UP to HANDED_OFF:
Order Status: HANDED_OFF
Step: Store Employee Checks if the Order Is_Paid
Actions:
If ORDER_HEADER.Is_Paid attribute is FALSE then asks Customer to pay the Order.
Step: Customer Makes Payment AT_HANDOFF
In this case:
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.
See Subject Area: PAYMENT AND INVOICE for more information.
Step: Customer Receives Invoice
Step: Customer Acknowledges Order Completed
Actions:
Customer aknowledges Order Completed.
Order Status changes from HANDED_OFF to COMPLETE.
Order Status: COMPLETE
Case 3: Order Delivery Type is DINE_IN
Step: Serving Order to Customer
Actions:
Customer steps into Store.
Store Employee reviews the Orders queue and presses a button to confirm an Order with READY_FOR_DINE_IN status is waiting for Customer.
Customer takes place to a table and Order is served to Customer.
Order Status changes from READY_FOR_DINE_IN to DINEING:
Order Status: DINEING
Step: Store Employee Fulfills Order Hand-off
Actions:
Customer aknowledges has finished dine in.
Order Status changes from DINEING to HANDED_OFF:
Order Status: HANDED_OFF
Step: Store Employee Checks if the Order Is Paid
Actions:
If ORDER_HEADER.Is_Paid attribute is FALSE then asks Customer to pay the Order.
Step: Customer Makes Payment AT_HANDOFF
In this case:
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.
See Subject Area: PAYMENT AND INVOICE for more information.
Step: Customer Receives Invoice
Step: Customer Acknowledges Order Completed
Actions:
Customer aknowledges Order Completed.
Order Status changes from HANDED_OFF to COMPLETE.
Order Status: COMPLETE
Order Entities
The Logical Data Model of the ORDER Subject Area includes the following groups of entities:
Order content entities
Order parameters entities
Order status entities
Order shipment entities
Order review entities
Entity: ORDER_HEADER
This entity records information about all Orders.
| Attributes | Description |
|---|---|
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 |
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, DINE_IN. 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 |
Placed_Customer_ID |
Lookup for the Customer who placed the Order. This field is NULL when the Order was placed for an ANONYMOUS Customer by a Store Employee. See Entity: CUSTOMER for more information. |
Placed_Emp_Login_ID |
Lookup for the Store Employee who placed the Order. This field is NULL when the Order was placed ONLINE by a REGISTERED_ONLINE Customer. See Entity: CUSTOMER for more information. |
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 Note: The same Employee can be granted with both responsibilities: placing orders and serving orders. In this case Placed_Emp_Login_ID and Served_Emp_Login_ID are the same. See Subject Area: EMPLOYEE AND ROLE for more information.
|
Customer_Notes |
REGISTERED_ONLINE and GUEST 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 restaurant 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, READY_FOR_PICK_UP or READY_FOR_DINE_IN depending on delivery type choosen by Customer. ⦁ at the same time, the application should insert automatically a record with the most recent Order status (READY_FOR_SHIPMENT, READY_FOR_PICK_UP or READY_FOR_DINE_IN) 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 |
Entity: ORDER_LINE
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 | Description |
|---|---|
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 |
Entity: SALES_CHANNEL
List of Sales Channels.
| Attributes | Description |
|---|---|
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 for Customers to select, or it was canceled permanently. NOT NULL |
Example
SALES_CHANNEL
| Name | Description |
|---|---|
| ONLINE | The REGISTERED_ONLINE Customer login on Store website and places an Order. |
| PHONE | The GUEST Customer makes a phone call to the Store to place an Order. Store Employee answers the call, fills in the mandatory attributes of Customer entity 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 | Description |
|---|---|
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 for Customers to select, 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. |
| DINE_IN | Order is served by Customer in Store. |
Entity: PAYMENT_METHOD
List of payment methods.
| Attributes | Description |
|---|---|
ID |
PRIMARY KEY |
Name |
Payment method name. NOT NULL UNIQUE. Can't have two payment methods with the same name. |
Description |
Payment method description. |
Is_Active |
Flag attribute to indicate if the Payment method is still available for Customers to select, or it was canceled permanently. NOT NULL |
Example
PAYMENT_METHOD
| Name |
|---|
| CASH |
| CARD |
| etc. |
Entity: PAYMENT_TIME
List of payment times.
| Attributes | Description |
|---|---|
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 for Customer to select, 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 Customer selects 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 in the PAYMENT_TERM entity.
| Attributes | Description |
|---|---|
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 for Customers to select, or it was canceled permanently. NOT NULL |
Comments |
Internal notes used for clarifications on record content. Can be seen and edited only by Store Employees |
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 |
Entity: ORDER_STATUS_TYPE
List of all status types an Order can pass through.
| Attributes | Description |
|---|---|
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_TYPE
| Name |
|---|
| NEW |
| NEW_SCHEDULED |
| SCHEDULED |
| CANCELED |
| IN_PROGRESS |
| READY_FOR_PICK_UP |
| READY_FOR_DINE_IN |
| READY_FOR_SHIPMENT |
| PICKING_UP |
| DINEING |
| SHIPPING |
| HANDED_OFF |
| COMPLETED |
Entity: ORDER_STATUS_HISTORY
This entity records the full status history for an ORDER.
| Attributes | Description |
|---|---|
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 |
Entity: SHIPMENT
This entity records information about Order shipments for Orders with Delivery Type SHIPMENT.
| Attributes | Description |
|---|---|
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 |
Entity: ORDER_REVIEW
This entity records Customer reviews on Orders.
| Attributes | Description |
|---|---|
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 |