Establish a user delivery address table for the grocery shopping system in MySQL

To establish the user delivery address table of the food shopping system in MySQL, specific code examples are required

When developing a food shopping system, the user's delivery address is very The important part is that a separate database table is needed to store the user's shipping address information. In MySQL, you can use the CREATE TABLE statement to create a user shipping address table.

First, we create a database named "address", and then create a table named "user_address" in the database to store user shipping address information. The structure of the table is as follows:

CREATE TABLE user_address (
    address_id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    recipient_name VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    province VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    district VARCHAR(50) NOT NULL,
    street VARCHAR(100) NOT NULL,
    PRIMARY KEY (address_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

The above code creates a table named "user_address", which contains the following fields:

• address_id: Receiving address ID, which is an auto-increment Integer type, used as the primary key.
• user_id: User ID, associated with the user ID in the user table, used to indicate which user the delivery address belongs to.
• recipient_name: Consignee’s name, stored using VARCHAR(50) type.
• phone_number: Consignee’s mobile phone number, stored using VARCHAR(20) type.
• province: Province, stored using VARCHAR(50) type.
• city: City, stored using VARCHAR(50) type.
• district: district/county, stored using VARCHAR(50) type.
• street: Street address, stored using VARCHAR(100) type.

In the creation of the table, foreign key constraints are used to ensure that the value of the user_id field must exist in the user_id field of the user table. This ensures that each shipping address corresponds to a legitimate user.

Next, you can insert some test data into the user_address table to verify the correctness of the table. The sample code for inserting data is as follows:

INSERT INTO user_address (user_id, recipient_name, phone_number, province, city, district, street)
VALUES
    (1, '张三', '13812345678', '北京市', '北京市', '朝阳区', '朝阳路1号'),
    (1, '张三', '13812345678', '上海市', '上海市', '浦东新区', '浦东路2号'),
    (2, '李四', '13998765432', '广东省', '广州市', '天河区', '天河路3号');

The above code inserts three test address data, which belong to Zhang San with ID 1 and Li Si with ID 2 respectively.

Through the above code example, we have completed the creation and data insertion of the user delivery address table of the food shopping system in MySQL. Developers can make corresponding modifications and extensions according to their actual needs to meet the system's functional and business logic needs.

The above is the detailed content of Establish a user delivery address table for the grocery shopping system in MySQL. For more information, please follow other related articles on the PHP Chinese website!