Design Guide for Delivery Person Table of Grocery Shopping System in MySQL

Design Guide for the Delivery Person Table of the Grocery Buying System in MySQL

1. Table Design
When designing the delivery person table of the grocery shopping system, we need Consider the information and functionality required for the delivery person role. Below is a design guide for a delivery person table.

1. Table name: couriers (delivery person table)

2. Field design:

   • id: primary key, uniquely identifies each delivery person ID
   • name: delivery person’s name
   • phone: delivery person’s contact number
   • gender: delivery person’s gender
   • age: delivery person’s age
   • address: delivery person’s address
   • status: delivery person’s status, such as whether he is working, available, etc.
   • created_at: creation time
   • updated_at: update time

3. Sample code for creating a table:

   CREATE TABLE couriers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    age INT NOT NULL,
    address VARCHAR(100) NOT NULL,
    status ENUM('在职', '离职') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );

2. Functional implementation
In the grocery shopping system, the delivery person is an important role. There are following functional requirements:

1. Add delivery person:

   INSERT INTO couriers (name, phone, gender, age, address, status)
   VALUES ('张三', '13812345678', '男', 25, '北京市朝阳区', '在职');

2. Update delivery person information:

   UPDATE couriers SET phone = '13987654321' WHERE id = 1;

3. Delete delivery person:

   DELETE FROM couriers WHERE id = 1;

4. Query delivery person list:

   SELECT * FROM couriers;

5. Query delivery person by name:

   SELECT * FROM couriers WHERE name = '张三';

6. Query available delivery persons based on status:

   SELECT * FROM couriers WHERE status = '在职';

7. Query delivery persons based on age range:

   SELECT * FROM couriers WHERE age BETWEEN 20 AND 30;

Through the above functions With the implementation, we can complete the management and query operations of delivery personnel in the grocery shopping system.

Summary:
When designing the delivery person table, you need to take into account the basic information and functional requirements required by the delivery person, such as name, contact number, gender, age and other fields, as well as add, update, delete, query Wait for operations. Through reasonable design and coding, delivery personnel information in the grocery shopping system can be effectively managed and queried.

The above is the detailed content of Design Guide for Delivery Person Table of Grocery Shopping System in MySQL. For more information, please follow other related articles on the PHP Chinese website!