设计客户表

-- 客户等级规则表
DROP TABLE IF EXISTS `t_customer_level`;
CREATE TABLE `t_customer_level` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '等级编码',
  `level` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '等级',
  `levelname` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '等级名称',
  `pointmin` INT(11) NOT NULL DEFAULT '0' COMMENT '最小积分',
  `pointmax` INT(11) NOT NULL DEFAULT '100' COMMENT '最大积分',
  `discount` DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '等级对应的折扣',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '客户等级表';

-- 客户登录信息表
DROP TABLE IF EXISTS `t_customer_login`;
CREATE TABLE `t_customer_login` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '流水主键',
  `passport` VARCHAR(32) NOT NULL COMMENT '登录凭证',
  `logintype` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '登录类型 0：用户名；1：手机号；2：邮箱',
  `password` VARCHAR(32) NOT NULL COMMENT '密码',
  `salt` CHAR(8) NOT NULL DEFAULT '12345678' COMMENT '密码盐',
  `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '账户状态 0：正常；1：禁用',
  `areaid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '登录区域编码',
  `loginip` VARCHAR(16) NOT NULL DEFAULT '0.0.0.0' COMMENT '上次登录IP',
  `logintime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '上次登录时间',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_passport`(`passport`) USING BTREE,
  INDEX `idx_passport`(`passport`) USING BTREE,
  INDEX `idx_logintype`(`logintype`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '客户登录信息表';

-- 客户详情表
DROP TABLE IF EXISTS `t_customer_info`;
CREATE TABLE `t_customer_info` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '客户编码',
  `passport` VARCHAR(32) NOT NULL COMMENT '登录凭证',
  `nickname` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户昵称',
  `realname` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '真实姓名',
  `avatar` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '用户头像',
  `gender` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '性别 0：女；1：男；2：未知',
  `mobile` CHAR(11) NOT NULL DEFAULT '' COMMENT '手机号',
  `areaid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '籍贯',
  `address` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '详细地址',
  `levelid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '客户等级',
  `hobby` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '个人爱好',
  `sign` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '个性签名',
  `remark` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '备注信息',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_passport`(`passport`) USING BTREE,
  INDEX `idx_passport`(`passport`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '客户详情表';

-- 客户详情历史表
DROP TABLE IF EXISTS `t_customer_info`;
CREATE TABLE `t_customer_info` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '流水主键',
  `customerid` BIGINT(20) UNSIGNED NOT NULL COMMENT '客户编码',
  `passport` VARCHAR(32) NOT NULL COMMENT '登录凭证',
  `nickname` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户昵称',
  `realname` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '真实姓名',
  `avatar` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '用户头像',
  `gender` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '性别 0：女；1：男；2：未知',
  `mobile` CHAR(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号',
  `areaid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '籍贯',
  `address` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '详细地址',
  `levelid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '客户等级',
  `hobby` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '个人爱好',
  `sign` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '个性签名',
  `remark` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '备注信息',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_passport`(`passport`) USING BTREE,
  INDEX `idx_passport`(`passport`) USING BTREE,
  INDEX `idx_customerid`(`customerid`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '客户详情历史表';

-- 客户收货地址表（收货地址本身就会经常改变，所以无需保存历史表）
DROP TABLE IF EXISTS `t_customer_address`;
CREATE TABLE `t_customer_address` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '收货地址编码',
  `customerid` BIGINT(20) UNSIGNED NOT NULL COMMENT '客户编码',
  `realname` VARCHAR(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '收货人姓名',
  `mobile` CHAR(11) NOT NULL DEFAULT '' COMMENT '收货人手机号',
  `areaid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '区域编码',
  `address` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '详细收货地址',
  `isdefault` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否默认地址 0：否；1：是',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_customerid`(`customerid`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '客户收货地址表';

---

感谢支持

更多内容，请移步《超级个体》。