SSM Project - An On-line Shopping Website (2-1)

Part 2. Database design (Step 1 and Step 2)

Proper design of database is an important step, which can provide a strong backup for our following APP design. The design can be categorized into these steps:

1. Database creation
2. Design ERD between different tables
3. Write SQL statments
4. Create different pages according to tables

2.1 Create Database

DROP DATABASE IF EXISTS tmall_ssm;
CREATE DATABASE tmall_ssm DEFAULT CHARACTER SET utf8; 

2.2 Tables

In this project, we design 9 schemas, the ERD is shown as follows.

The schemas include:

Table Name	Description
Category	Category info, e.g. clothes, toys, books, etc.
Property	Property info, e.g. weight, color, type, manufacturer, etc.
Product	Product info, e.g. name.
PropertyValue	Property value info, e.g. weight is 900g, color is pink, etc.
ProductImage	Product images
Review	Customers reviews
User	User info, e.g. user name,...
Order	Order info, e.g. mailing address, telephone,...
OrderItem	Order item info, e.g. the type or the number of the ordered product

2.3 Relationships between tables

There are three types of Ralationships in SQL Serveer

1. One-One Ralationship (1-1 Relationship)
2. One-Many Relationship (1-M Relationship)
3. Many-Many Relationship (M-M Relationship)

In SQL Server, these relationships are defined using Primary Key-Foreign Key constraints. A link is created between two tables where the primary key of one table is associated with the foreign key of another table using database relationships.

2.3.1 One-One Ralationship (1-1 Relationship)

One-to-One Relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one matching row. This relationship can be created using Primary key-Unique foreign key constraints.

2.3.2 One-Many Relationship (1-M Relationship)

The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using Primary key-Foreign key relationship.

2.3.3 Many-Many Relationship (M-M Relationship)

If two tables have a M-M Relationship, we need another table which is used to store their relationships. In the third table, the Primary key from both these two tables should be the Foreign key constraints in the third table.

For example, in the above ERD the table Product and User has a M-M Relationship, say a user can buy many product and at the same time a produce can be bought by many users. So we create an OrderItem table. The table Product and OrderItem has a 1-M Relationship and the table User and OrderItem has a 1-M Relationship. That is
Product <-> User is M : M; ==> Product -> OrderItem is 1 : M; + User -> OderItem is 1 : M.
In the above ERD, table property and table product also has a M-M relationship.

In the above ERD, the 1-M Relationship is summarized as below.

One	Many
Category	Product
Category	Property
Property	PropertyValue
Product	PropertyValue
Product	ProductImage
Product	Review
User	Order
Product	OrderItem
User	OrderItem
Order	OrderItem
User	Review