php - Membership card, points, and level management design issues.

Question

Scenes

Basic membership card management system, consumption records, etc.

How to design more optimized?

Currently it is the redundant points membership level field of the membership card table.

Consumption record table, consumption amount (1 yuan 1 point), with new consumption records, you must increase the points of the membership card and update his membership level based on the changed points.

Redeeming points is as simple as subtracting points and keeping the same level.

How do the masters design this common membership card management system?

Answer 1

First of all, no matter which table requires an ID, which is the primary key. You must have a user table, and then the user table has corresponding membership card fields. There is also a membership card table with membership card number, points, and membership level. Then there is the consumption record sheet. The following are the most basic tables and fields. Other fields or tables can be added according to your business
user:
id, username, cardId
card:
id, cardNum, point, level
purchase:
id, money, thing

Answer 2

User table userinfo
id username score (points) level_id

Card table cardinfo
id uid card_name token

Member level level_info
id min_score max_score level_name