MySQL implements the preferential activity management function of the ordering system

MySQL realizes the preferential activity management function of the ordering system

Introduction:

With the development of the Internet, the catering industry has gradually entered the digital stage era. The emergence of the ordering system has greatly facilitated restaurant operations and customers' dining experience. In the ordering system, preferential activities are one of the important means to attract and retain customers. This article will introduce how to use the MySQL database to implement the preferential activity management function of the ordering system, and provide specific code examples.

1. Design the database table structure

Create a database named "discount" in MySQL, and then create the following three tables:

1. activity table: Used to store information about promotions.

CREATE TABLE activity (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
discount DECIMAL(5, 2) NOT NULL
);

2. dish table: used to store dish information.

CREATE TABLE dish (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price DECIMAL(5, 2) NOT NULL,
category VARCHAR(50) NOT NULL
);

3. activity_dish table: used to store the relationship between promotional activities and dishes.

CREATE TABLE activity_dish (
activity_id INT NOT NULL,
dish_id INT NOT NULL,
PRIMARY KEY (activity_id, dish_id),
FOREIGN KEY (activity_id) REFERENCES activity (id),
FOREIGN KEY (dish_id) REFERENCES dish(id)
);

2. Insert data

Example of inserting a promotional activity data into the activity table:

INSERT INTO activity (name, start_date, end_date, discount)
VALUES ('Weekend Special', '2022-09-01', '2022-09-30', 0.8);

Example of inserting a dish data into the dish table:

INSERT INTO dish (name, price, category)
VALUES ('Kung Pao Chicken', 28.00, 'Sichuan Cuisine');

Example of inserting the relationship between dishes and promotions in the activity_dish table:

INSERT INTO activity_dish (activity_id, dish_id)
VALUES (1, 1);

3. Query data

The following is a sample code to query the promotions and corresponding dishes that are valid within a specific date:

SELECT a.name AS activity_name, d.name AS dish_name
FROM activity a
INNER JOIN activity_dish ad ON a.id = ad.activity_id
INNER JOIN dish d ON ad.dish_id = d.id
WHERE CURDATE() BETWEEN a.start_date AND a.end_date;

4. Update data

The following is a sample code to update discount activity:

UPDATE activity
SET discount = 0.6
WHERE id = 1;

5. Delete data

The following is a sample code to delete a promotion and its associated dishes:

DELETE FROM activity_dish
WHERE activity_id = 1;

DELETE FROM activity
WHERE id = 1;

6. Summary

By using the MySQL database, we can easily implement the preferential activity management function of the ordering system. By creating and operating the three tables activity, dish and activity_dish, we can implement functions such as inserting, querying, updating and deleting preferential activities. These functions can help restaurants flexibly develop promotions and associate them with dishes to enhance customers' dining experience.

Tips:

In the actual ordering system, in order to improve query efficiency and ensure data consistency, it is recommended to add indexes to relevant fields and use transactions to operate the database.

The above is an introduction and sample code for using MySQL to implement the preferential activity management function of the ordering system. I hope it will be helpful to relevant developers and restaurant operators.

The above is the detailed content of MySQL implements the preferential activity management function of the ordering system. For more information, please follow other related articles on the PHP Chinese website!