电商店铺忠诚度计划数据库设计咨询：已有Users与Orders表

Hey there! Let's walk through your initial ideas and refine the database design for your loyalty program—great job thinking through the core pieces already.

Let's Break Down Your Proposed Steps #

First, let's evaluate each of your ideas to see their value and how they fit together:

• Creating a user_bonuses table (with user_id, order_id, bonuses): This is a non-negotiable must-have. This table acts as your full audit trail for all积分 transactions—every time a user earns, spends, loses, or gets refunded points, you’ll have a clear, traceable record tied to its source (like an order). It’s critical for user support (when someone asks, "Why did my points disappear?"), debugging, and keeping track of historical activity.

• Adding a bonuses field to the Users table: This is a smart performance optimization. Instead of calculating a user’s current available points by summing every entry in user_bonuses every time (which gets slow as your user base grows), this field acts as a cached total of their active points. Just make sure you keep it in sync with the user_bonuses table (more on that later).

• Adding a bonuses field to the Orders table: This also makes perfect sense. It directly ties an order to the number of points earned from it, which simplifies workflows like processing refunds—when an order is refunded, you can quickly look up how many points to deduct without digging through the user_bonuses table.

Recommended Full Design #

Here’s how to combine these ideas into a robust, scalable setup, with a few extra fields to handle edge cases like point expiration or redemptions:

1. Core user_bonuses Transaction Table

This is the single source of truth for all point movements. Extend it to cover more scenarios than just earning points:

CREATE TABLE user_bonuses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_id INT NULL, -- Leave NULL when points are redeemed (not tied to an order)
    bonuses INT NOT NULL, -- Positive = earned, Negative = spent/refunded/expired
    transaction_type ENUM('earned_from_order', 'redeemed_for_reward', 'expired', 'refunded_order') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expired_at DATETIME NULL, -- Include if your points have an expiration date
    FOREIGN KEY (user_id) REFERENCES Users(id),
    FOREIGN KEY (order_id) REFERENCES Orders(id) ON DELETE SET NULL
);

2. Users Table Cached Points Field

Add a field to store the user’s current available points for quick, O(1) access:

ALTER TABLE Users ADD COLUMN available_bonuses INT DEFAULT 0;

3. Orders Table Earned Points Field

Add a field to track exactly how many points were earned from this specific order:

ALTER TABLE Orders ADD COLUMN earned_bonuses INT DEFAULT 0;

Key Workflow & Data Consistency Tips #

To keep all data in sync, always handle point transactions in database transactions (so if one step fails, nothing is saved to avoid inconsistencies):

• When an order is completed successfully:
  1. Insert a earned_from_order entry into user_bonuses with the points value.
  2. Update Orders.earned_bonuses to match the points earned.
  3. Update Users.available_bonuses by adding the points value.
• When an order is refunded:
  1. Insert a refunded_order entry into user_bonuses with a negative points value (matching the original earned amount).
  2. Update Users.available_bonuses by subtracting the points value.
• When a user redeems points for a reward:
  1. Insert a redeemed_for_reward entry into user_bonuses with a negative points value.
  2. Update Users.available_bonuses by subtracting the points value.

Why This Setup Works #

• Full Auditability: You can trace every point change back to its exact source (order, redemption, expiration, etc.).
• Performance: The available_bonuses field lets you fetch a user’s current points instantly, no expensive database sums needed.
• Maintainability: Tying points directly to orders makes refund/return workflows straightforward and error-proof.

内容的提问来源于stack exchange，提问作者Alexxosipov