Click here to Skip to main content
15,867,990 members
Articles / Database Development / SQL Server / SQL Server 2008R2

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

Rate me:
Please Sign up or sign in to vote.
2.70/5 (7 votes)
10 Apr 2016CPOL6 min read 53.8K   5   14
Learn an interesting way to design database with coder's 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':

SQL
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.

Image 1

Table for Sub Category

SQL
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)

Image 2Requirement 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
SQL
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)

Image 3

Table Schema for Currency Related
SQL
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) 

Image 4

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
SQL
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)

Image 5

Table for Seller Sub Category
SQL
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)

Image 6

Table for Pictures
SQL
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
SQL
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
SQL
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)

Image 7

Table for Values of Varients
SQL
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)

Image 8

Finally Overall Database Diagram

Image 9

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)
SQL
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:

Image 10

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
SuggestionQuery update Pin
Rushikesh Panchal2-Dec-21 16:13
Rushikesh Panchal2-Dec-21 16:13 
GeneralRe: Query update Pin
Abhinav Kumar singh (Aman)2-Dec-21 18:07
Abhinav Kumar singh (Aman)2-Dec-21 18:07 
QuestionQuation about eCom Pin
rutvik solanki20-Jun-21 6:48
rutvik solanki20-Jun-21 6:48 
AnswerRe: Quation about eCom Pin
Abhinav Kumar singh (Aman)20-Jun-21 20:54
Abhinav Kumar singh (Aman)20-Jun-21 20:54 
GeneralRe: Quation about eCom Pin
rutvik solanki21-Jun-21 18:33
rutvik solanki21-Jun-21 18:33 
QuestionQuetion Pin
rutvik solanki20-Jun-21 2:31
rutvik solanki20-Jun-21 2:31 
AnswerRe: Quetion Pin
Abhinav Kumar singh (Aman)20-Jun-21 5:17
Abhinav Kumar singh (Aman)20-Jun-21 5:17 
QuestionQuetion Pin
rutvik solanki20-Jun-21 2:23
rutvik solanki20-Jun-21 2:23 
AnswerRe: Quetion Pin
Abhinav Kumar singh (Aman)20-Jun-21 5:26
Abhinav Kumar singh (Aman)20-Jun-21 5:26 
AnswerRe: Quetion Pin
Abhinav Kumar singh (Aman)20-Jun-21 5:33
Abhinav Kumar singh (Aman)20-Jun-21 5:33 
GeneralThankful for posting content Pin
alokinfo3010-Feb-21 9:54
professionalalokinfo3010-Feb-21 9:54 
GeneralRe: Thankful for posting content Pin
Abhinav Kumar singh (Aman)20-Jun-21 5:21
Abhinav Kumar singh (Aman)20-Jun-21 5:21 
Questioncould i get insert command to add new product Pin
Sunil Patro23-Aug-17 15:35
Sunil Patro23-Aug-17 15:35 
AnswerRe: could i get insert command to add new product Pin
Abhinav Kumar singh (Aman)19-Sep-17 21:02
Abhinav Kumar singh (Aman)19-Sep-17 21:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.