E-Commerce Product Management Database Model with Coder's and DBA Perspective

Introduction

This article deals with the following:

1. Requirement Analysis for Database Designing using Coder's Perspective
2. Testing of Designed Database
3. Database for the "Product Section of E-Commerce Website"

This article could be very useful for the students who didn't have any industrial experience till now and they wanted to design database for a large project. This article contains a lot of basics. Hope you will enjoy this article.

Background

Any E-Commerce Enterprise typically has the following components (considering database):

1. Customer Management
2. Product Management
3. Order Processing and Management
4. Shipment Management
5. Warehouse Management
6. Seller Management
7. Admin Activities Area

In this article, I am going to put my focus on point 2 which is "Product Management" means through this section of database entire management of product is achieved.

Requirement Analysis for Product Management Database:

On a high level, the following points could be the typical requirements of any E-Commerce Website.

Requirement 1

Products should be categorized in Categories in Sub-Categories.

Explanation

Suppose of a product i-phone, so in a generalized way, it's a "Mobile Phone", and in a broader way or say in a specialized way, it belongs to "Electronics" category because phone is an electronics product.

So in summary we can say that we need to generalize the products by means of "Category" and "Sub-Category"

Coder's Perspective

If I am a coder, I would like to handle this by creating the following type of screen:

1. Drop Down for Categories and Sub categories; Subcategories drop down will not populate until you selected a category.
2. Once the user has selected the drop down of categories, sub-categories would be populated.

DBA Perspective

If I am a DBA, I would like to handle this by creating the following:

a) Creation of separate lookup tables with one to one relationship for Categories and Sub-Categories.

Long Term Benefits of this

1. Reduction in number of foreign keys in case of future tables.
2. Could be big saving in query execution time since level of normalization is maintained and also query optimization can be achieved easily.

Proposed Schema

Table for Category

Note prefix 'M' is used for keyword 'Master':

CREATE TABLE [dbo].[M_Product_Category](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Product_Type] [nvarchar](50) NOT NULL,
[Description] [nvarchar](100) NULL,
[Updated_By] [nvarchar](50) NULL,
[IsActive] [bit] NULL,
[Last_Updated_Date] [datetime] NULL)

Constraints on the Table

1. Id column will be Primary Key and it will be foreign key for the sub category of products.
2. IsActive if 1 means record is active, if 0 record is not active and while firing any select query, we need to use filter 'Select......where "IsActive=1"
3. 'Updated_By' column will be 'System Admin' for all coming tables. Considering that it will be updated by system admin.
4. Last_Updated_Date will have default value 'GETDATE()' to get the automatic values of date.

Table for Sub Category

CREATE TABLE [dbo].[M_Product_SubCategory](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [FK_Product_Id] [bigint] NOT NULL,
  [Product_Sub_Category] [nvarchar](100) NULL,
  [Description] [nvarchar](200) NULL,
  [Updated_By] [nvarchar](50) NULL,
  [Last_Updated_Date] [datetime] NULL,
  [IsActive] [bit] NULL)

Requirement 2

• 2.1 Product which is getting sold could be in KG,UNIT OR LITERS or in Pound, etc..
• 2.2 Product can be sold in any kind of currency like be it in $, be it in EURO or INR, etc..

Coder's Perspective

If I would be the coder, I will just put 2 dropdowns on the screen one for selling unit related, one for currency related.

DBA Perspective

1. Expecting very less amount of data in this table for future :) so will just create two separate lookups or say Master tables.

Table Schema for Unit Related

CREATE TABLE [dbo].[M_Price_Decision_Factor](

    [Id] [bigint] IDENTITY(1,1) NOT NULL,

    [Price_Decision_Factor] [nvarchar](50) NULL,

    [Description] [nvarchar](500) NULL,

    [Updated_By] [nvarchar](50) NULL,

    [Last_Updated] [datetime] NULL,

    [IsActive] [bit] NULL)

Table Schema for Currency Related

CREATE TABLE [dbo].[M_Currency_Domiance](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Currency_Type] [nvarchar](50) NULL,
    [Description] [nvarchar](200) NULL,
    [Updated_By] [nvarchar](50) NULL,
    [Last_Updated] [datetime] NULL,
    [IsActive] [bit] NULL) 

Requirement 3

• 3.1 Every product information should come up with its seller's information.
  • 3.1.1 Generalization at Seller level is needed in order to distinguish between different kind of sellers.
• 3.2 Every product information may contain maximum 10 images and 5 videos.

Coders' Perspective

If I would be the coder, I will again use cascade dropdown list for seller information like first dropdown will display the type of seller and then dropdown of sub category of sellers and then few textboxes for the other details.

DBA Perspective

If I would be the DBA, I would like to do the following:

1. Lookup table for Seller Category
2. Lookup table for Sub category of seller with foreign key references primary key of lookup table of Seller Category.
3. Two different Lookup tables for both Pictures and Videos which will have the Product_Id as a foreign key which will refer to the Primary key of Product Table.

Proposed Schema

Table for Seller Category

CREATE TABLE [dbo].[M_Seller_Category](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Seller_Category] [nvarchar](100) NULL,
[Description] [nvarchar](500) NULL,
[Last_Updated] [datetime] NULL,
[Updated_By] [nvarchar](100) NULL,
[IsActive] [bit] NULL)

Table for Seller Sub Category

CREATE TABLE [dbo].[M_Seller_SubCategory](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [FK_Seller_Id] [bigint] NULL,
  [Seller_Sub_Category] [nvarchar](50) NULL,
  [Description] [nvarchar](200) NULL,
  [Updated_By] [nvarchar](50) NULL,
  [Last_Updated] [datetime] NULL,
  [IsActive] [bit] NULL)

Table for Pictures

CREATE TABLE [dbo].[M_Product_Image_Details](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [FK_Product_Id] [bigint] NOT NULL,
    [Product_Image_1] [nvarchar](300) NULL,
    [Product_Image_2] [nvarchar](300) NULL,
    [Product_Image_3] [nvarchar](300) NULL,
    [Product_Image_4] [nvarchar](300) NULL,
    [Product_Image_5] [nvarchar](300) NULL,
    [Product_Image_6] [nvarchar](300) NULL,
    [Product_Image_7] [nvarchar](300) NULL,
    [Product_Image_8] [nvarchar](300) NULL,
    [Product_Image_9] [nvarchar](300) NULL,
    [Product_Image_10] [nvarchar](300) NULL,
    [Updated_By] [nvarchar](50) NULL,
    [Last_Updated] [datetime] NULL,
    [IsActive] [bit] NULL)

Table for Videos

CREATE TABLE [dbo].[M_Product_Video_Details](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [FK_Product_Id] [bigint] NULL,
    [Product_Video_1] [nvarchar](300) NULL,
    [Product_Video_2] [nvarchar](300) NULL,
    [Product_Video_3] [nvarchar](300) NULL,
    [Product_Video_4] [nvarchar](300) NULL,
    [Product_Video_5] [nvarchar](300) NULL,
    [Updated_By] [nvarchar](50) NULL,
    [Last_Updated] [nchar](10) NULL,
    [IsActive] [bit] NULL)

Requirement 4

• 4.1 A product can have multiple varients and for each varient of product, price will differ.Example by varient means variation of a product like a t-shirt can be red or blue , a book can be a novel or can be an acadmic one a Mobile Phone can be a android or Windows phone ,an mobile cover can be blue or red for same company and same model.
• 4.2 A product can have discount.
• 4.3 A product can have special schemes for selling like If you buy 10 products price of product will be 'A' and if you will buy a 'N' Product price of product will be 'X'. In short to support bulk purchase order price of product should be changed on every increase of count of product.
• 4.4 There will be two modes of orders
  1. Bulk Mode
  2. Normal Mode in case bulk mode price will be different

Coder Perspective

On the order screen as a coder, I would like to have the following:

1. One drop down for bulk mode or normal mode
2. I will do the calculation of price on the code level since on code level, price calculation will be faster

DBA Perspective

My proposed DB schema is as below:

Table for Products

Column Name	DataType	Comment
Id	bigint	Primary Key
FK_Price_Decision_Factor	bigint	Foreign Key of Price_Decision_Factor
FK_Product_Subcategory	bigint	Foreign Key of Product Sub category
FK_Seller_Id	bigint	Foreign Key of Seller Sub Category
FK_Product_Varient	bigint	Foreign Key of Product Varient
FK_Currency_Dominance	bigint	Foreign Key of Currency Dominance
Product_Name	nvarchar(100)	Name of Product
Description	nvarchar(MAX)	Description of Product
Price	nvarchar(100)	Price of Product
SKU	nvarchar(50)	SKU of Product
Availability_Count	bigint	Available counts of Product
Percentage_Discount	float	Percent Discount in case if not bulk order
Special_Offer_Price	nvarchar(100)	Special Offer Price
Special_Offer_Minimum_Quantity	nvarchar(10)	Special Offer Minimum Quantity
Special_Offer_Maximum_Quantity	nvarchar(10)	Special Offer Maximum Quantity
Special_Offer_Discount_Factor	float	In case of Bulk order Price=(Special_Offer_Discount_Factor*Price of Single Unit)
Minimum_Allowed_Buy_Quantity	nvarchar(10)	If not Bulk order lower limit of allowed quantity
Maximum_Allowed_Buy_Quantity	nvarchar(10)	If not Bulk order upper limit of allowed quantity
Updated_By	nvarchar(50)
Last_Updated_Date	datetime
IsActive	bit

Table for Varients

Note: Varient will contain two tables one is for master and one is for varient values which will have one to N relationship with the master table means the primary key of M_Product_Varient table will be a foreign key for Product_Varient_Value table.

Master Table for Varients

CREATE TABLE [dbo].[M_Product_Varients](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Varient_Name] [nvarchar](50) NULL,
    [Varient_Description] [nvarchar](100) NULL,
    [Updated_By] [nvarchar](50) NULL,
    [Last_Update] [datetime] NULL,
    [IsActive] [bit] NULL)

Table for Values of Varients

CREATE TABLE [dbo].[Product_Varient_Value](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [FK_Varient_Id] [bigint] NULL,
    [Varient_Value] [nvarchar](50) NULL,
    [Description] [nvarchar](max) NULL,
    [Updated_By] [nvarchar](50) NULL,
    [Last_Updated_Date] [datetime] NULL,
    [IsActive] [bit] NULL)

Finally Overall Database Diagram

Using the Code

Steps

1. Create the tables as described
2. The final query will be as follows: (note you can customize or can create query as per your need)

SELECT 
Product.Product_Name,
Product.Description, Product.Price, Product.SKU,
Product_Varient_Value.Varient_Value,Seller.Seller_Sub_Category,
Currency.Currency_Type,Price_Decision.Price_Decision_Factor,
Product.Availability_Count, Product.Percentage_Discount,
Product.Special_Offer_Price,Product.Special_Offer_Minimum_Quantity,
Product.Special_Offer_Maximum_Quantity,Product.Special_Offer_Discount_Factor,
Product.Minimum_Allowed_Buy_Quantity,Product.Maximum_Allowed_Buy_Quantity,
Subcategory.Product_Sub_Category,M_Product_Category.Product_Type 
FROM
Product INNER JOIN Product_Varient_Value ON Product.FK_Product_Varient = Product_Varient_Value.Id
Inner Join M_Seller_SubCategory Seller ON Seller.Id=Product.FK_Seller_Id 
inner join M_Currency_Domiance Currency on
Currency.Id=Product.FK_Currency_Dominance inner join M_Price_Decision_Factor Price_Decision
ON Price_Decision.Id=Product.FK_Price_Decision_Factor 
inner join M_Product_SubCategory Subcategory
ON Subcategory.Id=Product.FK_Product_Subcategory 
inner join M_Product_Category M_Product_Category
ON M_Product_Category.Id=Subcategory.FK_Product_Id Where Product.IsActive=1

The final output will be as follows:

A big thanks to those who read my article. Hope you have gained something from this article. :) Thanks again for reading :)