MySQL table design practice: Create a membership level table and upgrade record table

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.

1. Create a membership level table

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.

2. Create an upgrade record table

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_timeThe 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.

3. Insert data

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.

4. Query membership level and upgrade records

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:

Query membership grade and discount information

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.

Query the upgrade record of a user

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!