• Database one to many cases (one tourism category corresponds to multiple tourism routes)

A tourism category corresponds to multiple tourism routes. The category and tourism route list belong to 1-to-many relationship:

#Create a travel category table
CREATE TABLE tab_category(
  #Primary key of tourist route classification
  cid INT PRIMARY KEY AUTO_INCREMENT, 
  #Tourist route classification name 
  cname VARCHAR(100) NOT NULL UNIQUE
);

INSERT INTO tab_category(cname)VALUES('Domestic self-help'),('Exit self-help'),('Domestic regiment'),('Peripheral self driving');

#Create a tour route table
CREATE TABLE tab_route(
  #Primary key of tourist route
  rid INT PRIMARY KEY AUTO_INCREMENT,
  #Name of tourist route
  rname VARCHAR(100) NOT NULL UNIQUE,
  #Price
  price DOUBLE NOT NULL,
  #Line introduction
  routeIntroduce VARCHAR(200),
  #Is it on the shelf?
  rflag CHAR(1) NOT NULL,
  #Shelf time
  rdate VARCHAR(19) NOT NULL,
  #Theme tourism or not
  isThemeTour CHAR(1) NOT NULL,
  #Collection quantity
  acount INT DEFAULT 0,
  #Classification
  cid INT NOT NULL,
  #Thumbnail address
  rimage VARCHAR(200) NOT NULL,
  CONSTRAINT category_route_FK FOREIGN KEY(cid) REFERENCES tab_category(cid)
);

• Many to many relationship (a user can collect multiple travel routes, and a travel route can be collected by the user)

#Create user table
CREATE TABLE tab_user (
uid INT PRIMARY KEY AUTO_INCREMENT, -- user id
username VARCHAR(100) NOT NULL UNIQUE, -- User name
PASSWORD VARCHAR(30) NOT NULL, -- Password
NAME VARCHAR(100), -- Real name
birthday DATE, -- Birthday
sex CHAR(1), -- Gender
telephone VARCHAR(11), -- Cell-phone number
email VARCHAR(100), -- mailbox
STATUS CHAR(1) NOT NULL, -- Active or not
CODE VARCHAR(32) NOT NULL UNIQUE -- Activation code
);

#Add user data
INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, 'Lao Wang', '1977-07-07', 'male', '13888888888', '66666@qq.com', 'yes', '1386'),
(NULL, 'cz119', 654321, 'Xiao Wang', '1999-09-09', 'male', '13999999999', '99999@qq.com', 'yes', '9898');

#Create favorites
CREATE TABLE tab_favorite (
fid INT PRIMARY KEY AUTO_INCREMENT, -- Collection of primary keys
rid INT NOT NULL, -- Tourist route id
DATE DATE NOT NULL, -- Collection time
uid INT NOT NULL -- user id
);

#Add favorite table data
INSERT INTO tab_favorite VALUES
(NULL, 1, '2018-01-01', 1), -- Lao Wang chooses Xiamen
(NULL, 1, '2018-01-01', 2), -- Lao Wang chooses Guilin
(NULL, 1, '2018-01-01', 3), -- Lao Wang chooses Thailand
(NULL, 2, '2018-01-01', 2), -- Xiao Wang chooses Guilin
(NULL, 2, '2018-01-01', 3), -- Xiao Wang chooses Thailand
(NULL, 2, '2018-01-01', 5); -- Xiao Wang chooses Disney

#Describe the relationship between users and tourism routes (many to many relationship)
CREATE TABLE route_user(
     r_id   INT , #Travel route id
     u_id  INT , #User id
     #Establishing a foreign key relationship
     CONSTRAINT route_user_fk1 FOREIGN KEY(r_id)  REFERENCES tab_route(rid),
     CONSTRAINT route_user_fk2 FOREIGN KEY(u_id)  REFERENCES tab_user(uid),
     #Set as union primary key
     PRIMARY KEY(r_id,u_id)
          
);