MySQL table design practice: Create a user address table and order table

MySQL table design practice: Create a user address table and order table

In the design and development of the database, table design is a very important part. This article will lead readers to learn how to design and create a user address table and order table to practice the MySQL table design process.

1. User address table design

The user address table is a table that records user shipping address information and contains the following fields:

• id: Address ID, primary key
• user_id: user ID, foreign key, user ID associated to the user table
• receiver_name: consignee name
• receiver_phone: consignee phone number
• province: province
• city: city
• district: district and county
• street: street
• postal_code: postal code

The SQL statement to create the user address table is as follows:

CREATE TABLE address (
  id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL,
  receiver_name VARCHAR(100) NOT NULL,
  receiver_phone VARCHAR(20) NOT NULL,
  province VARCHAR(50) NOT NULL,
  city VARCHAR(50) NOT NULL,
  district VARCHAR(50) NOT NULL,
  street VARCHAR(100) NOT NULL,
  postal_code VARCHAR(10) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id)
);

2. Order table design

The order table is a table that records user order information and contains the following fields:

• id: order ID, primary key
• user_id: user ID, foreign key, user ID associated to the user table
• address_id: address ID, foreign key, Address ID associated to the user address table
• order_date: order date
• total_price: total order amount
• status: order status

Create order The SQL statement of the table is as follows:

CREATE TABLE order (
  id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL,
  address_id INT(11) NOT NULL,
  order_date DATE NOT NULL,
  total_price DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id),
  FOREIGN KEY (address_id) REFERENCES address(id)
);

In the above code, foreign keys are used to associate with other tables. The ID field of the user table is a foreign key to create the user address table and order table. The ID field of the address table is the foreign key used to create the orders table. This ensures data integrity and relevance.

The above is the basic design of the user address table and order table. According to actual needs and business scenarios, more fields and constraints can be added. In the actual development process, table design needs to comprehensively consider factors such as database performance, data consistency, and query efficiency.

Summary:

MySQL table design is an important part of database development. This article introduces how to design and create a user address table and order table. Through reasonable table design, the performance and query efficiency of the database can be improved, and the integrity and consistency of the data can be ensured. In practice, further optimization and improvements can be made according to actual needs.

The above is the detailed content of MySQL table design practice: Create a user address table and order table. For more information, please follow other related articles on the PHP Chinese website!