mysql - 如何创建表来存储不同的字段并引用它们？

Question

我有一组不同类型的用户，每种类型都有单独的一组字段存储用户设置。我的想法是将 user_id 和 user_type 存储在一个具有通用字段集的表中，并将其他设置移动到单独的表中。但问题是如何将公用表中的用户与其单独表中的详细信息链接起来。我看到一个解决方案是将与特定用户类型关联的表名存储在另一个表中。但这是最好的解决方案吗？

CREATE TABLE IF NOT EXISTS `mydb`.`user` (
  `user_id` INT NOT NULL,
  `user_name` INT NOT NULL,
  `user_type` INT NULL,
  PRIMARY KEY (`user_id`, `user_name`),
  UNIQUE INDEX `adv_id_UNIQUE` (`user_id` ASC),
  INDEX `adv_type_idx` (`user_type` ASC),
  CONSTRAINT `adv_type`
    FOREIGN KEY (`user_type`)
    REFERENCES `mydb`.`user_type` (`type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

CREATE TABLE IF NOT EXISTS `mydb`.`user_type` (
  `type_id` INT NOT NULL,
  `type_table` VARCHAR(45) NULL,
  UNIQUE INDEX `type_id_UNIQUE` (`type_id` ASC),
  PRIMARY KEY (`type_id`))

//TABLES WITH SEPARATE SET OF FIELDS

CREATE TABLE IF NOT EXISTS `mydb`.`user_details_admin` (
  `user_id` INT NOT NULL,
  `user_admin` VARCHAR(45) NULL,
  PRIMARY KEY (`user_id`))

CREATE TABLE IF NOT EXISTS `mydb`.`user_details_moderator` (
  `user_id` INT NOT NULL,
  `user_moderator` VARCHAR(45) NULL,
  PRIMARY KEY (`user_id`))

Answer 1

这似乎是您想要在数据库中对继承建模的情况。

与其将 user_details_ 表名称存储在 user_types 表中，不如使用类似于以下内容的方法更好地为您服务:

CREATE TABLE IF NOT EXISTS 'mydb'.'user' (
'user_id' INT NOT NULL,
'type_id' INT NOT NULL,
'commonfield1' datatype (NOT) NULL,
'commonfield2' datatype (NOT) NULL,
'commonfield...' datatype (NOT) NULL,
PRIMARY KEY ('user_id', (other field as needed)),
UNIQUE INDEX 'adv_id_UNIQUE' ('user_id' ASC),
INDEX 'adv_type_idx' ('type_id' ASC),
CONSTRAINT 'adv_type'
  FOREIGN KEY ('type_id')
  REFERENCES 'mydb'.'user_type' ('type_id')
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)

CREATE TABLE IF NOT EXISTS 'mydb'.'user_type' (
'type_id' INT NOT NULL,
'type_name' VARCHAR(45) NOT NULL,
UNIQUE INDEX 'type_id_UNIQUE' ('type_id' ASC),
UNIQUE INDEX 'type_name_UNIQUE' ('type_name' ASC),
PRIMARY KEY ('type_id'))

//TABLES WITH SEPARATE SET OF FIELDS

CREATE TABLE IF NOT EXISTS 'mydb'.'user_details_admin' (
'user_id' INT NOT NULL,
'type_id' INT NOT NULL,
'adminfield1' datatype (NOT) NULL,
'adminfield...' datatype  (NOT) NULL,
PRIMARY KEY ('user_id'))
CONSTRAINT user_type_FK
  FOREIGN KEY ('user_id', 'type_id')
  REFERENCES 'mydb'.'user' ('user_id', 'type_id')
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)

CREATE TABLE IF NOT EXISTS 'mydb'.'user_details_moderator' (
'user_id' INT NOT NULL,
'type_id' INT NOT NULL,
'moderatorfield1' datatype (NOT) NULL,
'moderatorfield...' datatype  (NOT) NULL,
PRIMARY KEY ('user_id'))
CONSTRAINT user_type_FK
  FOREIGN KEY ('user_id', 'type_id')
  REFERENCES 'mydb'.'user' ('user_id', 'type_id')
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)

此设计假设用户可能属于一种且只有一种类型。例如，您需要确保仅使用触发器或 View 将 moderator 添加到 user_details_moderator 表，和/或通过在您的应用程序代码中处理它。 MySQL 不对表实现检查约束。无论如何，您可能想要创建 View ，以避免每次要查询时都必须在 user 表和子类型表之间编写 JOIN一个特定的子类型。

注意:user 表中 type_id 的 INDEX 可能没有用或没有必要。

这不是对数据建模的唯一方法。如果您有几个不同类型的字段和/或愿意在您的表中拥有您知道将为 NULL 的字段，您可以将所有字段添加到 user 表。除了 a priori NULL 字段问题之外，这些方法之间的主要区别在于添加了具有新的不同字段的新 user_type。在我提供的示例中，您需要添加一个新表。在单表设计中，您需要向 user 表添加新的可为 null 的字段。哪个更容易维护完全取决于您，但我个人更喜欢 table-per-type 设计，因为在我的使用中添加一个表相对微不足道，而且我不喜欢故意添加我知道将包含 NULL 没有严重优化优势的“值(value)”(在我的情况下不存在，但在你的情况下可能存在)。

另见 How do you effectively model inheritance in a database? ，和/或在 database 下搜索“继承”标记以获取更多信息。