Marketplace Data Model | Subject area: CUSTOMER

Overview

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

• Entity: CUSTOMER
• Entity: LOYALTY_CARD_TYPE
• Entity: LOYALTY_CARD
• Entity: COUNTRY
• Entity: REGION
• Entity: TIME_ZONE
• Entity: CITY

A Customer can be:

• REGISTERED_ONLINE
  Customer creates an user account and self-registers on Store website.
• GUEST
  Customer makes a phone call to the Store to place an Order and a Store Employee registers the mandatory information about Customer: 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 paid. In this case there is no information recorded about Customer.
  If an ANONYMOUS Customer asks for a detailed invoice, then the Store Employee wil register the mandatory information about Customer and in this case Customer becomes a GUEST 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:

• Phone
• First_Name
• Last_Name
• Is_Company
• Company Name (if Is_Company TRUE)
• Tax_Number (if Is_Company TRUE)
• Is_Tax_Exempted
• 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:

⦁ Store Employee will check if Customer is a new Customer (can be an automatic check on Customer Phone number).

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

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

The Store Employee can create an online account for Customer if Customer agrees. In this case the following CUSTOMER entity attributes are mandatory:

• Email
• Username
• Password (to be changed next time when Customer will access the online account)

Step: Store Employee presses the Submit button

Actions:

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

⦁ If an online user account was created for Customer then the CUSTOMER.Is_Registered_Online attribute is set TRUE otherwise the CUSTOMER.Is_Registered_Online attribute is set FALSE. See Entity: CUSTOMER, Attribute: Is_Registered_Online for more information.

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

Entity: CUSTOMER

Attributes
ID	PRIMARY KEY
City_ID	Lookup for the City where the Customer is located. NOT NULL
Code	Customer code 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 the Customer is a company. NOT NULL
Company_Name	Company Name. If Is_Company is TRUE then Company_Name is mandatory
Tax_Number	Customer Tax Number
Is_Tax_Exempted	Flag attribute to indicate if the Customer is tax exempted. NOT NULL
Billing_Address	Customer Billing Address (used for creating invoice). NOT NULL.
Postal_Code	Customer address Postal Code.
Is_Registered_Online	Flag to indicate if the Customer has an online account. 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	Lookup for the Store Employee who created the Customer record . Created_Emp_Login_ID is NULL if the Customer has registered ONLINE.
Created_At_Store_ID	Lookup for the Store where the Customer record was created by a Store Employee. Created_At_Store_ID is NULL if the Customer has registered ONLINE.
Created_Time	Date and Time when the Customer record was created. NOT NULL
Last_Login_Time	Date and Time of the Customer last login.
Is_Active	Flag attribute to indicate if the Customer account is still operational or is suspended. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: LOYALTY_CARD_TYPE

Loyalty card types (SILVER, GOLD, PLATINUM etc)

Attributes
ID	PRIMARY KEY
Name	Loyalty card type name. NOT NULL UNIQUE
Description	Loyalty card type description

Entity: LOYALTY_CARD

List of Loyalty Cards assigned to Customers.

Attributes
ID	PRIMARY KEY
Loyalty_Card_Type_ID	Lookup for the Loyalty Card Type. NOT NULL
Customer_ID	Lookup for the Customer who has the Loyalty Card assigned. NOT NULL
Numeric_Code	Loyalty Card numeric code. NOT NULL UNIQUE
Bar_Code	Loyalty Card bar code.
Description	Loyalty Card description
Is_Active	Flag to indicate if the Loyalty Card is still active. NOT NULL
Created_Time	Timestamp when the Loyalty Card record was created. NOT NULL
Start_Time	The start timestamp from which the Loyalty Card is active. NOT NULL
End_Time	The end timestamp after which the Loyalty Card is not active. When the End_Time is NULL the Loyalty Card is active. NULL
Comments	Internal notes used for clarifications on record content.

Entity: COUNTRY

List of Countries where Marketplace Stores are located.

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

Entity: REGION

List of Regions where Marketplace Stores are located.

Attributes
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 Marketplace Stores locations.

Attributes
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 Marketplace Stores are located.

Attributes
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