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

Part 2. Database design (Step 3 and Step 4)

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. Create tables and write SQL statments
4. Create different pages according to tables

2.3 Create tables and write SQL statments

When building the tables, we set the Foreign-Key (FK) constraints, therefore we need to consider the dependencies between tables.
Firstly, build the tables that don’t have FK and whose Primary-Key (PRK) is the FK of other tables. E.g. User and Category.

A. Tables without FK

1) User

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;

2) Category

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;

B. Tables with FK

In the following tables, add FK constraints. SQL statement:
CONSTRAINT fk_table1_table2 FOREIGN KEY (cid) REFERENCES table2 (id)

3) Property
FK: category (category_id)

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;

4) Product
FK: category (category_id)

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;

5) Property Value
FK: property (property_id) and product (product_id)

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_value varchar(255) DEFAULT NULL,
  PRIMARY KEY (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;

6) Product Images
FK: product (product_id)
attributes: product_image_type – there are two image types, one is single image and another is the details figure.

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;

7) Review
FK: product (product_id) and user (user_id)
attributes: uid – user id

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;

8) Order
FK: user (user_id)

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;

9) Order Item
FK: product (product_id), order (order_id) and user (user_id)
attributes: number – the number of purchased goods

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;

The ERD generated by MySQL is shown as follows.