How to Design your Own Online Shopping Website like amazon -- DB design

1. Introduction

Before designing the database for our small online shopping system, let’s review three normalization forms.

1. 1NF: remove repeated/ redundant data
2. 2NF: make sure that all the fields in the table are dependent only on primary key.
3. 3NF: remove calculated(transitive) fields

Now let us discuss about the important entities in this system.

1.In the first place, we must have users.

Field(Attribute)	Explanation
user_id	unique primary key for each user
user_name	user’s name
user_password	password for each user

2.There must be multiple product categories.

Field(Attribute)	Explanation
category_id	unique primary key for each product category
category_name	product category name

3.Then,there should be multiple products in one category, and each product should be in only one category.(For some scenario, it could be in multiple category). At the same time, one one category should have many combinations of properties.(For example, laptops have properties like, CPU, Memory, Grapics,etc).SO, there is a many-to-many relationships between properties and products. therefore, we should create a table to store the relationship between them to satisfy the 3NF forms. Hence, we could design a table called property_value. One property has multiple property values and one product has many property values as well.

Field(Attribute)	Explanation
property_id	unique primary key for each property
property_category_id	foreign key from category table
property_name	name of each property

Product Table

Field(Attribute)	Explanation
product_id	product_id
product_subtitle	subtitle for products
original_price
promote_price
create_time	on sale time

Property Value Table (Relationships)

Field(Attribute)	Explanation
id	property Value primary key
property_id	foreign key product_id
product_id	foreign key product_id
propertye_value
promote_price
create_time	on sale time

Each product has many product image to be shown, therefore, we could create a table to store product images

Field(Attribute)	Explanation
product_image_id	product_id
product_id	foreign key constraints for product id
image_type	indicating whether it is details figure and display image

For product reviews, one product has many reviews and each user can post many reviews. Therefore, reviews table has two foreign key constraints

Field(Attribute)	Explanation
review_id	primary key
review_content	content for review in varchar type
user_id	foreign key constraints for user id
product_id	foreign key constraints for product id
create_date	review generated date

Finally, the most important thing is the order and the ordered item.

Field(Attribute)	Explanation
order_id	primary key
order_code	unique order code
order_address	order_address
order_receiver	order_receiver
order_mobile	order_mobile number
order_user_message	order_user_message
create_date	create_date
pay_date	pay_date
delivery_date	delivery_date
confirmDate	confirmDate
order_status	order_status
user_id:	FOREIGN KEY constraints

user table

CREATE TABLE user (
  user_id int(11) NOT NULL AUTO_INCREMENT,
  user_name varchar(255) DEFAULT NULL,
  user_password varchar(255) DEFAULT NULL,
  PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

category table

CREATE TABLE category (
  category_id int(11) NOT NULL AUTO_INCREMENT,
  category_name varchar(255) DEFAULT NULL,
  PRIMARY KEY (category_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

property table

CREATE TABLE property (
  property_id int(11) NOT NULL AUTO_INCREMENT,
  category_id int(11) DEFAULT NULL,
  property_name varchar(255) DEFAULT NULL,
  PRIMARY KEY (property_id),
  CONSTRAINT fk_property_category FOREIGN KEY (category_id) REFERENCES category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

product_table

CREATE TABLE product (
  product_id int(11) NOT NULL AUTO_INCREMENT,
  product_name varchar(255) DEFAULT NULL,
  product_subtitle varchar(255) DEFAULT NULL,
  original_price float DEFAULT NULL,
  promote_price float DEFAULT NULL,
  stock int(11) DEFAULT NULL,
  category_id int(11) DEFAULT NULL,
  create_date datetime DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES category (category_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

property value table

CREATE TABLE property_value (
  property_value_id int(11) NOT NULL AUTO_INCREMENT,
  product_id int(11) DEFAULT NULL,
  property_id int(11) DEFAULT NULL,
  property_value varchar(255) DEFAULT NULL,
  PRIMARY KEY (property_value_id),
  CONSTRAINT fk_propertyvalue_property FOREIGN KEY (property_id) REFERENCES property (property_id),
  CONSTRAINT fk_propertyvalue_product FOREIGN KEY (product_id) REFERENCES product (product_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

product iamge table

CREATE TABLE product_image (
  product_image_id int(11) NOT NULL AUTO_INCREMENT,
  product_id int(11) DEFAULT NULL,
  product_image_type varchar(255) DEFAULT NULL,
  PRIMARY KEY (product_image_id),
  CONSTRAINT fk_productimage_product FOREIGN KEY (product_id) REFERENCES product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

review table

CREATE TABLE review (
  review_id int(11) NOT NULL AUTO_INCREMENT,
  review_content varchar(4000) DEFAULT NULL,
  user_id int(11) DEFAULT NULL,
  product_id int(11) DEFAULT NULL,
  create_date datetime DEFAULT NULL,
  PRIMARY KEY (review_id),
  CONSTRAINT fk_review_product FOREIGN KEY (product_id) REFERENCES product (product_id),
  CONSTRAINT fk_review_user FOREIGN KEY (user_id) REFERENCES user (user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Order table

CREATE TABLE order_ (
  order_id int(11) NOT NULL AUTO_INCREMENT,
  order_code varchar(255) DEFAULT NULL,
  order_address varchar(255) DEFAULT NULL,
  order_post varchar(255) DEFAULT NULL,
  order_receiver varchar(255) DEFAULT NULL,
  order_mobile varchar(255) DEFAULT NULL,
  order_user_message varchar(255) DEFAULT NULL,
  create_date datetime DEFAULT NULL,
  pay_date datetime DEFAULT NULL,
  delivery_date datetime DEFAULT NULL,
  confirm_date datetime DEFAULT NULL,
  user_id int(11) DEFAULT NULL,
  order_status varchar(255) DEFAULT NULL,
  PRIMARY KEY (order_id),
  CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

order_item table

CREATE TABLE order_item (
  order_item_id int(11) NOT NULL AUTO_INCREMENT,
  product_id int(11) DEFAULT NULL,
  order_id int(11) DEFAULT NULL,
  user_id int(11) DEFAULT NULL,
  order_item_number int(11) DEFAULT NULL,
  PRIMARY KEY (order_item_id),
  CONSTRAINT fk_orderitem_user FOREIGN KEY (user_id) REFERENCES user (user_id),
  CONSTRAINT fk_orderitem_product FOREIGN KEY (product_id) REFERENCES product (product_id),
  CONSTRAINT fk_orderitem_order FOREIGN KEY (order_id) REFERENCES order_ (order_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Generate the E-R diagram by MySQL WORKBENCH.

ER diagram by MySQL