Data modeling - Example application
The gadget spec URL could not be found

The purpose of this exercise is not to teach data modeling, but just to get you familiarized with the data modeling process. So let's get started.

In our case, listing the functional requirements for the example application will be a good starting point for our data model design. Here's the list of our requirements:
  • Provide product team the ability to maintain category and product details.
  • Provide customer service representatives the ability to take orders and track order status.
  • Provide the functionality to generate purchase receipts that can be saved to a customers account.
A simple way to identify the entities and activities/process is to perform noun-verb analysis on the requirements. The nouns translate into entities, while the verbs translate into activities. Since our aim is to create the data model, we will focus on the entities and leave the activities/process analysis for later.
Identify the data entities
  • Category details
  • Product details
  • Customer details
  • Customer orders
  • User details 

Identify the attributes of the entities
  • Category details
  1. Category code
  2. Category description
  •  Product details
  1. Product code
  2. Product description
  3. Product price
  4. Product colour (>1)
  5. Product size (>1)
  6. Product image (>1)
  •  Customer details
  1. First name
  2. Last name
  3. Full name
  4. Mail ID
  5. Phone
  6. Sex
  7. Age
  8. Date of Birth
  9. Billing address (>1)
  •  Customer orders
  1. Order ID
  2. Order amount
  3. Order status
  4. Order date
  5. Order fulfillment date
  6. Product orders (>1)
  • User details
  1. User login ID
  2. User group/function

 
Organize the data
The process of organizing the data is called as normalization. Data is normalized for two main reasons:
  • Remove redundant data
  • Organize data efficiently by storing only related data together
How do we organize our data for the example application?
  • Product details, customer details and customer orders can be normalized.
  • A product may be available in multiple colours; each coloured tees may be available in one or more sizes -- Small (S), Medium (M), Large (L) etc. Product details can be split into product master, product colour and product size.
  • A customer can have multiple addresses -- billing address, delivery address etc. Customer details can be split into customer master, customer address.
  • When placing an order, a customer can order multiple products. Customer orders can be split into customer orders (to store order summary) and product orders (to store product order details).

Identify the relationship between entities
  • A product must belong to one of the categories.
  • Orders can be placed for only for the products available in the catalog.
  • Feedback can be given only for the orders placed by the customer.

Represent the data model pictorially



 
   TRY IT YOURSELF!

Make the necessary changes in the data model to incorporate the functionality to record customer feedback for each customer order.