Flipkart product management database model with dba perspective

How an E-commerce website works with DBA perspective

1. FLIPKART PRODUCT MANAGEMENT DATABASE MODEL WITH DBA PERSPECTIVE Presentation by: Archana Jha (NI1302/075/15) Submitted to: Rani Ojha

2. INTRODUCTION Any E-commerce website data is their product. Flipkart is an E-commerce website started in 2007 by Bansal brothers. Now became India’s most visiting online shopping site. A pool of multiple database which handled by DBA using Sql and NoSql databases.

3. Objective Requirement Analysis for database designing using DBA’s perspective. Testing of Designed Database. Database for the “Product Section of Flipkart Website.

4. Background Components of Flipkart Database:- Customer Management Product Management Order Processing and Management Shipment Management Warehouse Management Seller Management Payment Management

5. Requirement analysis for product management database REQUIREMENT 1:- Products Should be categorized in Categories and Sub-Categories

6. Cont…..

7. DBA Perspective:- Creation of separate lookup tables with one to one relationship for categories and sub-categories. Table Schema for Category:- 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)

8. Table Schema 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)

9. REQUIREMENT 2:- Product which is getting sold could be in KG, UNIT or LITRES or in Pound, etc... Product can be sold in any kind of currency like be it in $, be it in EURO or INR, etc…

10. DBA Perspective :- Expecting very less amount of data in this table for future so will just create two separate lookups. 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)

11. 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)

12. REQUIREMENT 3:- 1.Every product information should come up with its seller’s information. 2. Generalization at seller level in order to distinguish between different kind of sellers.

13. 3. Every product information may contain maximum 5 images.

14. DBA Perspective:- Lookup table for seller category Lookup table for sub-category of seller. Lookup tables for pictures. Table Schema 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)

15. Table Schema for Sub-Seller 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 )

16. Table Schema for Picture Category:- 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 , [ Updated_By ] [ nvarchar ](50) NULL, [ Last_Updated ] [ datetime ] NULL , [ IsActive ] [bit] NULL)

17. REQUIREMENT 4:- 1. A product can have multiple varients and for each varient product, price will differ. 2. A Product can have discount.

18. DBA Perspective:- DBA proposed schema for product details

19. Table Schema for Varient Category:- 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 )

20. Table Schema for Values of Varient Category:- 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)

21. Overflow database diagram

22. User request- dba query The final query will be as follows:- 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

23. Conclusion By this presentation we conclude that a DBA has to manage all database in different- different multiple lookups and all these lookups are connected with foreign keys. It not only helpful to manage database in an appropriate manner, also reduce time of query execution and provide all necessary information to user in a short period.

24. Thank-you