SQL database design for ecommerce

By Posted on

Problem

I need to create a database which stores details products, Manufactures,Suppliers. So initially I divided Products into categories and subcategories.

These are the tables I created.

Manufacturer, Category, Sub_Category, Products,
ProductSpecs, ProductAvailability, Supplier
  • Manufacturer table contains Brand Details (Ex: Apple,Windows..etc).
  • Supplier table should contain supplier details and price they are offering.
  • Category contains provides details like Electronics, Sports…etc.
  • Subcategory table should contains subcategories like Electronics(Mobiles,Laptops…).
  • Products table have only basic product details like ID,Name.
  • ProductSpecs contains specifications. Based on product we should have separate separate table for each subcategory.
  • ProductAvailability should contains product price and availability.

I am not good at database design. But managed to create the below one. Could you please anyone review this? Please help me if it needs any corrections.

E-R diagram

CREATE TABLE Supplier 
  ( 
  SupplierID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SupplierName VARCHAR(30) NOT NULL DEFAULT '', 
  SupplierTag  VARCHAR(30) NOT Null DEFAULT '',
  );

CREATE TABLE Category
    (
    CategoryID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CategoryName VARCHAR(30) NOT NULL DEFAULT '',
    CreatedBy VARCHAR(30) NOT NULL DEFAULT '',
    CreatedOn DATETIME NOT NULL
    );

CREATE TABLE Sub_Category
    (
    SubCategoryID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SubCategoryName VARCHAR(30) NOT NULL,
    CategoryID INT FOREIGN KEY REFERENCES CATEGORY(CategoryID),
    CreatedBy VARCHAR(30) NOT NULL,
    CreatedOn DATETIME NOT NULL
    );

CREATE TABLE Manufacturer
    (
    ManufacturerID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ManufacturerName VARCHAR(30) NOT NULL DEFAULT '',
    SubCategoryID INT FOREIGN KEY REFERENCES SUB_CATEGORY(SubCategoryID),
    );

CREATE TABLE Products 
  (
  ProductID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,    
  ProductName   VARCHAR(50) NOT NULL DEFAULT '', 
  CategoryID INT FOREIGN KEY REFERENCES CATEGORY(CategoryID), 
  SubCategoryID INT FOREIGN KEY REFERENCES SUB_CATEGORY(SubCategoryID),
  ManufacturerID INT FOREIGN KEY REFERENCES Manufacturer(ManufacturerID)
  );

CREATE TABLE ProductSpecs
( 
  RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,      
  ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
  SubCategoryID INT FOREIGN KEY REFERENCES SUB_CATEGORY(SubCategoryID),
  ProductSpec1 VARCHAR(30),
  ProductSpec2 VARCHAR(30),
  ProductSpec3 VARCHAR(30),
)

CREATE TABLE ProductAvailability
( 
  RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
  SupplierID INT FOREIGN KEY REFERENCES Supplier(SupplierID),
  SupplierWEB Varchar(30) NOT NULL,
  ProductPrice decimal(6,2) NOT NULL, 
)

Solution

CREATE TABLE Supplier 
  ( 
  SupplierID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SupplierName VARCHAR(30) NOT NULL DEFAULT '', 
  SupplierTag  VARCHAR(30) NOT Null DEFAULT '',
  );

Why limit the name to thirty characters? Consider names like “Specialty Products Company of Transylvania” — that’s forty-two characters. Unless you have a reason to keep it short, it’s better to pick a larger width than necessary rather than smaller. A VARCHAR(255) with a thirty character string in it is the same length as if it were a VARCHAR(30). You only use storage for what you use.

Also consider using NVARCHAR instead of VARCHAR. Perhaps some of your suppliers will have names that require Unicode to represent. 

CREATE TABLE Category
    (
    CategoryID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CategoryName VARCHAR(30) NOT NULL DEFAULT '',
    CreatedBy VARCHAR(30) NOT NULL DEFAULT '',
    CreatedOn DATETIME NOT NULL
    );

CREATE TABLE Sub_Category
    (
    SubCategoryID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SubCategoryName VARCHAR(30) NOT NULL,
    CategoryID INT FOREIGN KEY REFERENCES CATEGORY(CategoryID),
    CreatedBy VARCHAR(30) NOT NULL,
    CreatedOn DATETIME NOT NULL
    );

You can only have two levels of category and you must have two levels of category. If you want to add a third level, you’d have to add a new table (or refactor the existing tables). It is more common to handle this with a single table that has a ParentID column. 

CREATE TABLE Category
    (
    CategoryID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ParentID INT NOT NULL DEFAULT 0,
    CategoryName VARCHAR(30) NOT NULL DEFAULT '',
    CreatedBy VARCHAR(30) NOT NULL DEFAULT '',
    CreatedOn DATETIME NOT NULL
    );

Example showing how to use a table like this: Getting all the children of a parent using MSSQL query.

CREATE TABLE Manufacturer
    (
    ManufacturerID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ManufacturerName VARCHAR(30) NOT NULL DEFAULT '',
    SubCategoryID INT FOREIGN KEY REFERENCES SUB_CATEGORY(SubCategoryID),
    );

This says that a Manufacturer is associated with a SUB_CATEGORY. This seems overly restrictive. For example what if your subcategories are video and music? A company may very well deal in both. 

CREATE TABLE Products 
  (
  ProductID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,    
  ProductName   VARCHAR(50) NOT NULL DEFAULT '', 
  CategoryID INT FOREIGN KEY REFERENCES CATEGORY(CategoryID), 
  SubCategoryID INT FOREIGN KEY REFERENCES SUB_CATEGORY(SubCategoryID),
  ManufacturerID INT FOREIGN KEY REFERENCES Manufacturer(ManufacturerID)
  );

Given your original schema, you should be careful about having both a CategoryID and a SubCategoryID. This is what is called a denormalized schema. You can associate a CategoryID with a Product in two different ways. In a normalized schema, you only want there to be one association. Consider the case where CategoryID is not the parent of SubCategoryID.

Note that if you change to a single table with a ParentID, that it will also make sense to only associate the product with one level of category.

Consider the possibility that you might need products to be in more than one category at once. For example, you might create a Specials category. In that case, you might need to link Products and Category with a table rather than a column.

Note that you usually either want all the names to be singular (like Category and Manufacturer) or all to be plural (like Products). Pick a convention and stick to it. 

CREATE TABLE ProductSpecs
( 
  RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,      
  ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
  SubCategoryID INT FOREIGN KEY REFERENCES SUB_CATEGORY(SubCategoryID),
  ProductSpec1 VARCHAR(30),
  ProductSpec2 VARCHAR(30),
  ProductSpec3 VARCHAR(30),
)

Given this description, you might be better off adding three columns to Products. Although I would strong consider making a Specification table and a SpecificationToProduct table. That way you can easily add more than three specifications to a product and multiple products can have the same specification.

Try to avoid names like RowID. Go ahead and name this ProductSpecID or leave off that column altogether. 

CREATE TABLE ProductAvailability
( 
  RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
  SupplierID INT FOREIGN KEY REFERENCES Supplier(SupplierID),
  SupplierWEB Varchar(30) NOT NULL,
  ProductPrice decimal(6,2) NOT NULL, 
)

This seems a good place to use a composite primary key based on ProductID and SupplierID rather than RowID. 

CREATE TABLE ProductAvailability
( 
  ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
  SupplierID INT FOREIGN KEY REFERENCES Supplier(SupplierID),
  SupplierWEB Varchar(30) NOT NULL,
  SupplierPrice decimal(6,2) NOT NULL, 
  PRIMARY KEY (ProductID, SupplierID)
)

If ProductPrice is the supplier’s price for the product, then SupplierPrice might be a better name.

It’s not clear to me what a SupplierWEB is nor why it would be associated with the Supplier/Product link. If it is the supplier’s product page, then a name change might be clearer. Also, it should probably be wider than thirty characters if it is meant to hold a link.

Your diagram does not match your table specifications. I reviewed the table specifications. If the diagram better reflects your actual design, you should post another question where the table specifications match the diagram.

There are already some excellent answers, so these comments are add-ons.

Personally I am never keen on not null default ” as a declaration. It tends to complicate queries, and can cause obscure bugs if someone puts a string of spaces in that field. I suggest you make these fields NOT NULL or leave them as nullable. In the case of a supplier or manufacturer, I would expect they must have a name, so NOT NULL seems reasonable.

Also you have Price fields in the database, but no way of tracking price variations over time. If you are just interested in the current price, renaming the field to CurrentPrice would be a good idea.

For someone who claims to be not good at database design, it is a great effort.

I think ProductAvailability is a link table so ProductPrice looks like a attribute of Product and SupplierWEB looks like a attribute of Supplier:

ProductAvailability 
( 
  RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
  SupplierID INT FOREIGN KEY REFERENCES Supplier(SupplierID)  
)

Leave a Reply

Your email address will not be published. Required fields are marked *