如何基于用户类型创建数据库表 解决单用户关联多性别账号问题

首先明确第一个问题的结论：males和females表必须设置user_id字段。
这个字段是关联用户公共信息与性别专属信息的唯一桥梁，没有这个字段，你根本无法把males/females表里存储的专属数据和users表里的公共用户记录对应起来，整个分表设计就失去了意义。

针对你提到的「单个用户关联多条同性别表记录、甚至跨表关联异常」的问题，可以通过三层约束彻底解决：

• 单表唯一约束：给males、females两张表的user_id字段都添加UNIQUE唯一约束，从表结构层面直接限制同一个user_id在单张专属表中最多只能存在1条记录，直接杜绝单用户在同性别表下有多条记录的问题。
• 关联一致性约束：将两张表的user_id设置为外键，关联users表的主键id，保证专属表中写入的user_id一定是users表中真实存在的用户，不会出现无效关联。同时需要在users公共表中新增性别标识字段（比如gender字段，用枚举或整数标记用户性别），可以通过数据库检查约束、触发器两种方式做校验：向males表插入数据时，必须保证对应user_id在users表中的性别标记为男性；向females表插入数据时，必须保证对应user_id的性别标记为女性，从根源避免同一个用户同时在两张专属表中存在记录。
• 应用层前置校验：不要把所有校验逻辑都堆在数据库层，业务代码执行写入操作前，先查询目标用户的性别、以及对应专属表中是否已存在该用户的记录，提前拦截重复写入、跨表写入的非法请求，减少数据库的异常报错。

补充说明：如果后续业务可能新增更多性别分类，这种按性别拆表的设计扩展性会比较差，可根据业务迭代预期评估是否要调整为单表加扩展字段的设计，但如果当前业务边界明确只有两类用户，上述方案完全可以满足数据一致性要求。

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