🏷️ 会员模块数据库设计文档

支持多商户·多门店·实物卡·储值消费·积分体系·余额重算 · MySQL 8.0+

📅 版本：v3.0 (多商户版) 📁 数据库：MySQL 8.0 🧩 字符集：utf8mb4 📐 金额精度：分 (BIGINT) 📋 表数量：10 张 🏢 支持：多商户 + 多门店

📖 概述

本数据库设计面向 多商户多门店会员卡管理系统，支持 实物卡（芯片ID） 与 无卡消费（手机号） 两种场景。 核心能力包括：储值充值、折扣消费、积分累积与兑换、余额变动全链路日志、余额重算 机制，以及 多商户/门店数据隔离。 设计时已预留 计次卡 扩展能力。

🏢 多商户多门店支持： 所有会员表已增加 fk_company_id 和 fk_store_id 字段，实现数据隔离。 每个商户/门店可独立配置积分规则、折扣比例、充值策略等。

🔗 实体关系图 (ER)

📋 表结构详细设计

1. member_card 会员卡主表

🏢 多商户关键字段： fk_company_id 和 fk_store_id 实现数据隔离， 查询时务必带上这两个条件，确保不同商户/门店数据互不干扰。

2. member_recharge 开卡/充值记录表

3. member_consume 消费明细表

4. member_balance_log ⭐ 余额变动日志表（核心）

5. member_points_exchange 积分兑换记录表

6. member_balance_recalc 余额重算记录表（审计）

7. member_merchant_config 🆕 商户会员卡配置表

⚙️ 配置优先级： 门店配置（fk_store_id 有值）> 公司配置（fk_store_id 为 NULL）> 系统默认配置

8. member_operation_log 🆕 会员卡操作日志表（审计）

9. system_consume_config 系统配置表

10. v_member_card_full 📊 会员卡完整信息视图

CREATE OR REPLACE VIEW v_member_card_full AS SELECT mc.key_id, mc.card_no, mc.chip_id, mc.phone, mc.card_type, mc.status, mc.current_balance, mc.current_principal, mc.current_gift, mc.current_points, mc.discount_rate, mc.open_date, mc.expire_date, mc.last_consume_time, sm.company_fullname AS company_name, sm.company_shortname AS company_shortname, sm.contact_info AS company_contact, sm.contact_phone AS company_phone, sms.name AS store_name, sms.store_no, sms.phone AS store_phone, sms.address AS store_address, sms.contact AS store_contact, COALESCE(mmc_store.gift_consume_ratio, mmc_company.gift_consume_ratio, sys1.config_value) AS gift_consume_ratio, COALESCE(mmc_store.points_rate, mmc_company.points_rate, sys2.config_value) AS points_rate, COALESCE(mmc_store.points_exchange_rate, mmc_company.points_exchange_rate, sys3.config_value) AS points_exchange_rate FROM member_card mc LEFT JOIN sauna_merchant sm ON mc.fk_company_id = sm.key_id LEFT JOIN sauna_merchant_store sms ON mc.fk_store_id = sms.key_id LEFT JOIN member_merchant_config mmc_store ON mc.fk_store_id = mmc_store.fk_store_id AND mmc_store.is_enabled = 1 LEFT JOIN member_merchant_config mmc_company ON mc.fk_company_id = mmc_company.fk_company_id AND mmc_company.fk_store_id IS NULL AND mmc_company.is_enabled = 1 LEFT JOIN system_consume_config sys1 ON sys1.config_key = 'gift_consume_ratio' LEFT JOIN system_consume_config sys2 ON sys2.config_key = 'points_rate' LEFT JOIN system_consume_config sys3 ON sys3.config_key = 'points_exchange_rate';

⚙️ 核心建表SQL（完整）

CREATE TABLE member_card ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID', key_id VARCHAR(50) NOT NULL COMMENT '业务主键ID', card_no VARCHAR(20) NOT NULL COMMENT '会员卡编号', chip_id VARCHAR(32) NOT NULL COMMENT '芯片ID唯一码', phone VARCHAR(11) NOT NULL COMMENT '绑定手机号', fk_company_id VARCHAR(50) NOT NULL COMMENT '所属公司ID', fk_store_id VARCHAR(50) NOT NULL COMMENT '所属门店ID', card_type TINYINT NOT NULL DEFAULT 1 COMMENT '卡类型:1-储值卡,2-计次卡', status TINYINT NOT NULL DEFAULT 1 COMMENT '状态:0-冻结,1-正常,2-挂失,3-注销', total_recharge BIGINT NOT NULL DEFAULT 0 COMMENT '累计充值金额', total_gift BIGINT NOT NULL DEFAULT 0 COMMENT '累计赠送金额', total_consume BIGINT NOT NULL DEFAULT 0 COMMENT '累计消费本金', total_gift_consume BIGINT NOT NULL DEFAULT 0 COMMENT '累计赠送消费', current_balance BIGINT NOT NULL DEFAULT 0 COMMENT '当前可用总余额', current_principal BIGINT NOT NULL DEFAULT 0 COMMENT '当前本金余额', current_gift BIGINT NOT NULL DEFAULT 0 COMMENT '当前赠送余额', total_points INT NOT NULL DEFAULT 0 COMMENT '累计获得积分', used_points INT NOT NULL DEFAULT 0 COMMENT '已使用积分', current_points INT NOT NULL DEFAULT 0 COMMENT '当前可用积分', discount_rate DECIMAL(5,2) NOT NULL DEFAULT 100.00 COMMENT '折扣比例', open_date DATETIME NOT NULL COMMENT '开卡日期', expire_date DATETIME DEFAULT NULL COMMENT '有效期', last_consume_time DATETIME DEFAULT NULL COMMENT '最后消费时间', sort_index INT DEFAULT 0 COMMENT '排序码', create_by VARCHAR(50) DEFAULT NULL COMMENT '创建人', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_by VARCHAR(50) DEFAULT NULL COMMENT '修改人', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (id), UNIQUE KEY uk_key_id (key_id), UNIQUE KEY uk_card_no (card_no), UNIQUE KEY uk_chip_id (chip_id), KEY idx_phone (phone), KEY idx_company_store (fk_company_id, fk_store_id), KEY idx_status (status), KEY idx_create_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员卡主表'; CREATE TABLE member_recharge ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, key_id VARCHAR(50) NOT NULL COMMENT '业务主键ID', card_id BIGINT UNSIGNED NOT NULL COMMENT '会员卡自增ID', card_key_id VARCHAR(50) NOT NULL COMMENT '会员卡业务ID', recharge_no VARCHAR(32) NOT NULL COMMENT '充值流水号', recharge_type TINYINT NOT NULL COMMENT '类型:1-开卡,2-充值,3-赠送活动,4-手动调账', amount BIGINT NOT NULL DEFAULT 0 COMMENT '充值金额(本金)', gift_amount BIGINT NOT NULL DEFAULT 0 COMMENT '赠送金额', pay_method TINYINT DEFAULT NULL COMMENT '支付方式', operator_id VARCHAR(50) DEFAULT NULL COMMENT '操作人员ID', remark VARCHAR(255) DEFAULT NULL COMMENT '备注', recharge_time DATETIME NOT NULL COMMENT '充值时间', sort_index INT DEFAULT 0 COMMENT '排序码', create_by VARCHAR(50) DEFAULT NULL COMMENT '创建人', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_by VARCHAR(50) DEFAULT NULL COMMENT '修改人', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (id), UNIQUE KEY uk_key_id (key_id), UNIQUE KEY uk_recharge_no (recharge_no), KEY idx_card_id (card_id), KEY idx_card_key_id (card_key_id), KEY idx_recharge_time (recharge_time), KEY idx_create_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充值/开卡记录表';

🧠 核心业务逻辑

🚀 扩展性设计

📌 索引优化建议

CREATE INDEX idx_company_store ON member_card(fk_company_id, fk_store_id); CREATE INDEX idx_company_store_status ON member_card(fk_company_id, fk_store_id, status); CREATE INDEX idx_company_store_time ON member_card(fk_company_id, fk_store_id, create_time); CREATE INDEX idx_balance_log_card_time ON member_balance_log(card_key_id, create_time DESC); CREATE INDEX idx_balance_log_company ON member_balance_log(card_key_id, create_time DESC); CREATE INDEX idx_consume_card_time ON member_consume(card_key_id, consume_time DESC); CREATE INDEX idx_consume_company_time ON member_consume(card_key_id, consume_time DESC); CREATE INDEX idx_oplog_card_time ON member_operation_log(card_key_id, operation_time DESC); CREATE INDEX idx_oplog_type_time ON member_operation_log(operation_type, operation_time DESC);

📊 枚举值说明

💻 C# 实体类示例

namespace ZR.Model.Merchant { [SugarTable("member_card")] public class MemberCard { [SugarColumn(IsPrimaryKey = true, IsIdentity = false, ColumnName = "key_id")] public string KeyId { get; get; set; } [SugarColumn(ColumnName = "fk_company_id")] public string FkCompanyId { get; get; set; } [SugarColumn(ColumnName = "fk_store_id")] public string FkStoreId { get; get; set; } [SugarColumn(ColumnName = "card_no")] public string CardNo { get; get; set; } [SugarColumn(ColumnName = "chip_id")] public string ChipId { get; get; set; } public string Phone { get; get; set; } [SugarColumn(ColumnName = "current_principal")] public long CurrentPrincipal { get; get; set; } [SugarColumn(ColumnName = "current_gift")] public long CurrentGift { get; get; set; } [SugarColumn(ColumnName = "current_points")] public int CurrentPoints { get; get; set; } [SugarColumn(ColumnName = "sort_index")] public int? SortIndex { get; get; set; } [SugarColumn(ColumnName = "create_by")] public string CreateBy { get; get; set; } [SugarColumn(ColumnName = "create_time")] public DateTime? CreateTime { get; get; set; } [SugarColumn(ColumnName = "update_by")] public string UpdateBy { get; get; set; } [SugarColumn(ColumnName = "update_time")] public DateTime? UpdateTime { get; get; set; } } }