MySQL table design practice: Create a membership level table and upgrade record table
When developing membership system applications, we often need to design a membership level system to divide members into different levels. and record their upgrade history. In MySQL, we can achieve this function through reasonable table design. This article will introduce you to how to create a membership level table and upgrade record table, and provide corresponding code examples.
The membership level table is used to store information related to different membership levels, such as level names, points requirements, discount ratios, etc. We can create a table named member_grade
through the following code:
CREATE TABLE member_grade ( id INT(11) NOT NULL AUTO_INCREMENT, grade_name VARCHAR(50) NOT NULL, min_points INT(11) NOT NULL, discount DECIMAL(4,2) NOT NULL, PRIMARY KEY (id) );
In the above code, we used member_grade
as the table name and defined four Fields: id
, grade_name
, min_points
, and discount
. The id
field is used to uniquely identify each level, and we set it as an auto-incrementing primary key. The grade_name
field is used to store the name of the grade, the min_points
field is used to indicate the minimum points required for the grade, the discount
field indicates the discount ratio for the grade, Use DECIMAL type for storage.
The upgrade record table is used to store the member's upgrade history, recording which level the member was upgraded to and the time of the upgrade. We can create a table named upgrade_history
by the following code:
CREATE TABLE upgrade_history ( id INT(11) NOT NULL AUTO_INCREMENT, member_id INT(11) NOT NULL, old_grade_id INT(11) NOT NULL, new_grade_id INT(11) NOT NULL, upgrade_time DATETIME NOT NULL, PRIMARY KEY (id), FOREIGN KEY (member_id) REFERENCES members(id), FOREIGN KEY (old_grade_id) REFERENCES member_grade(id), FOREIGN KEY (new_grade_id) REFERENCES member_grade(id) );
In the above code, we create a table named upgrade_history
and define There are five fields: id
, member_id
, old_grade_id
, new_grade_id
and upgrade_time
. The id
field is used to uniquely identify each upgrade record, and we set it as an auto-incrementing primary key. The member_id
field is used to store the member's ID, the old_grade_id
field represents the grade ID before upgrade, the new_grade_id
field represents the grade ID after upgrade, upgrade_time
The field represents the upgrade time and is stored using the DATETIME type.
It should be noted that we use a foreign key in the upgrade_history
table to associate it with the id
field of the member_grade
table, and we should also The member's id
field is associated with the id
field in the members
table. In order to simplify the code, the definition of the members
table is omitted here. You can define it according to the actual situation.
Next, we will insert some sample data into the member_grade
table and the upgrade_history
table for subsequent operations and Inquire. The following is the code to insert sample data:
INSERT INTO member_grade (grade_name, min_points, discount) VALUES ('普通会员', 0, 1.00), ('银卡会员', 1000, 0.95), ('金卡会员', 5000, 0.90), ('钻石会员', 10000, 0.80); INSERT INTO upgrade_history (member_id, old_grade_id, new_grade_id, upgrade_time) VALUES (1, 1, 2, NOW()), (2, 1, 3, NOW()), (3, 2, 4, NOW());
In the above code, we use two INSERT INTO
statements to insert the member_grade
table and upgrade_history respectively.
Insert data into the table. In the member_grade
table, we inserted four pieces of membership level information, including level name, minimum points requirement and discount ratio. In the upgrade_history
table, we inserted three upgrade records, including member ID, level ID before upgrade, level ID after upgrade, and upgrade time.
Through the above table design and data insertion, we can perform some query operations to obtain relevant information about membership levels and upgrade records. The following are some common query examples:
SELECT grade_name, min_points, discount FROM member_grade;
The above query statement will return the grade names of all membership grades in the member_grade
table, Minimum points requirements and discount percentage.
SELECT u.member_id, m.username, old_grade.grade_name AS old_grade, new_grade.grade_name AS new_grade, u.upgrade_time FROM upgrade_history u LEFT JOIN member_grade old_grade ON u.old_grade_id = old_grade.id LEFT JOIN member_grade new_grade ON u.new_grade_id = new_grade.id LEFT JOIN members m ON u.member_id = m.id WHERE u.member_id = 1;
The above query statement will return the upgrade record of the user with membership ID 1, including user name, pre-upgrade level, and post-upgrade level. and upgrade time.
Through the above examples, we can see that through reasonable MySQL table design, we can implement a membership level table and upgrade record table to store membership level information and upgrade history. These table designs can help us better manage the membership system and provide corresponding query functions.
Of course, based on actual needs and business logic, you may need to further adjust and expand the table structure. In practical applications, you can also combine other tables to implement a more complex membership level system. I hope the examples and ideas provided in this article can help you design and implement a fully functional membership level system in MySQL.
The above is the detailed content of MySQL table design practice: Create a membership level table and upgrade record table. For more information, please follow other related articles on the PHP Chinese website!