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.
The user address table is a table that records user shipping address information and contains the following fields:
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) );
The order table is a table that records user order information and contains the following fields:
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!