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:
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
);
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
);
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!