Data Model Pack

Subject area: CUSTOMER

Restaurant Data Model · release 5.9 · ER Diagram · SQL DDL scripts

Overview

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

Restaurant - Customer Data Model
Restaurant - Customer Data Model Click to zoom

Customer is an individual or a company who can place Orders to a restaurant Store

A Customer can be:

  • REGISTERED_ONLINE
    Customer self-registers on Store website and a user account is created for Customer.
  • GUEST
    Customer makes a phone call to the Store to place an Order and a Store Employee registers the Customer basic information: First Name, Last name, Phone, etc.
  • ANONYMOUS
    Customer places an Order in Store or at a Drive-In station and the Order is picked-up by Customer as soon as the Order is prepared. In this case there is no information recorded about Customer.

See Subject Area: ORDER - Order Status and Process for more information.

Scenario 1: A new Customer self-registers on Store website

This is the scenario for a REGISTERED_ONLINE Customer.

Step: Customer presses the Sign up button

Step: Customer must fill in the registration form

Actions:

Customer must fill in all the mandatory attributes for a REGISTERED_ONLINE Customer:

  • First_Name
  • Last_Name
  • Is_Company
  • Company Name (if Is_Company TRUE)
  • Company_VAT_No (if Is_Company TRUE)
  • Phone
  • Billing_Address
  • Email
  • Username
  • Password

Step: Customer presses the Submit button

Actions:

A new record for a REGISTERED_ONLINE Customer is created in the CUSTOMER entity.

CUSTOMER.Is_Registered_Online attribute flag is set TRUE. See Entity: CUSTOMER for more information.

Customer can start to place Orders ONLINE. See Subject Area: ORDER - Order Status and Process for more information.

Scenario 2: A Customer makes a phone call to the Store to place an Order

This is the scenario for a GUEST Customer.

Step: Customer makes a phone call to the Store

Step: Store Employee records the Customer information

Actions:

The Store Employee who answers the phone call will check if the Customer is a new Customer (can be an application automatic check on Customer Phone number).

If the Customer is a new Customer, the Store Employee will record all the mandatory information about Customer:

  • First_Name
  • Last_Name
  • Is_Company
  • Company Name (if Is_Company TRUE)
  • Company_VAT_No (if Is_Company TRUE)
  • Phone
  • Billing_Address

In this case the following attributes are NOT mandatory:

  • Email
  • Username
  • Password

Step: Store Employee presses the Submit button

Actions:

A new record for a GUEST Customer is created in the CUSTOMER entity.

CUSTOMER.Is_Registered_Online flag attribute is set FALSE. In this case the Customer will NOT have an ONLINE user account created. See Entity: CUSTOMER, Attribute: Is_Registered_Online for more information.

The Store Employee will place the Order on behalf of the GUEST Customer. See Subject Area: ORDER - Order Status and Process for more information.

Entity: CUSTOMER

Attributes Description
ID PRIMARY KEY
City_ID Lookup for the City where the Customer is located. NOT NULL
Phone Customer phone number. UNIQUE identifies the Customer. Cannot register two Customers with the same phone number NOT NULL UNIQUE
First_Name Customer First Name NOT NULL
Last_Name Customer Last Name NOT NULL
Is_Company Flag attribute to indicate if there is an individual or a company Customer. NOT NULL
Company_Name Company Name. If Is_Company is TRUE then Company_Name is mandatory
Company_Tax_Code Company Tax Code is used for creating invoice. If Is_Company is TRUE then Company_Tax_Code is mandatory
Billing_Address Billing Address is used for creating invoice. Billing Address can differ from Shipment Delivery Address. See Subject Area: ORDER, Entity: SHIPMENT, Attribute: Shipment_Address for more information. NOT NULL.
Is_Registered_Online Flag to indicate if the Customer is a REGISTERED_ONLINE Customer or a GUEST Customer. If the Customer is REGISTERED_ONLINE then: Email, Username, Password, Last_Login_Time are mandatory. NOT NULL
Email Customer email address. If the Customer is REGISTERED_ONLINE then Email is mandatory. UNIQUE identifies the Customer. Cannot register two Customers with the same email
Username Customer username. If the Customer is REGISTERED_ONLINE then Username is mandatory. UNIQUE identifies the Customer. Cannot register two Customers with the same username.
Password Customer password. If the Customer is REGISTERED_ONLINE then Password is mandatory.
Credit Total Credit Amount of the Customer. Customer can receive Credit as result of ORDER_REVIEW.Compensation.
See Subject Area: ORDER, Entity: ORDER_REVIEW, Attribute: Compensation for more information.
Created_Emp_Login_ID The Customer record can be created for a GUEST Customer or for a REGISTERED_ONLINE Customer.
When the Customer record is created for a GUEST Customer, the Created_Emp_Login_ID is the lookup for the Store Employee who created the GUEST Customer record.
When the Customer record is created for a REGISTERED_ONLINE Customer (who self-registered on Store website), the Created_Emp_Login_ID is NULL.
Created_Time Date and Time when the Customer record was created. NOT NULL
Last_Login_Time Date and Time of the Customer last login.
For a GUEST Customer, Last_Login_Time is NULL.
For a REGISTERED_ONLINE Customer, Last_Login_Time is NOT NULL.
Comments Internal notes used for clarifications on record content. Can be seen and edited only by Store Employees

Entity: COUNTRY

List of Countries where restaurant Stores are located.

Attributes Description
ID PRIMARY KEY
Name Country name. Cannot have to countries with the same name. NOT NULL UNIQUE

Entity: REGION

List of Regions where restaurant Stores are located.

Attributes Description
ID PRIMARY KEY
Name Region name NOT NULL
Country_ID Lookup for Country. NOT NULL

CONSTRAINT UK_Region UNIQUE (Name, Country_ID) Cannot have two regions with the same name in a Country.

Entity: TIME_ZONE

List of Time Zones for the restaurant Stores locations.

Attributes Description
ID PRIMARY KEY
Name Time Zone name. Cannot have two Time Zones with the same name. NOT NULL UNIQUE
Description Time Zone Description

Entity: CITY

List of Cities where restaurant Stores are located.

Attributes Description
ID PRIMARY KEY
Name City name NOT NULL
Region_ID Lookup for Region. NOT NULL
Time_Zone_ID Lookup for Time zone. NOT NULL
Zip_Code City Zip Code

CONSTRAINT UK_City UNIQUE (Name, Region_ID)) Cannot have two cities with the same name in a Region

Accelerate every database schema design with powerful data models

We have already created the database diagram and prepared the SQL DDL scripts to deploy your database schema. Download a free Demo edition and evaluate it in your own database.

Try Data Model Pack freeView pricing