mysql - 将多个配置文件表连接到用户表

Question

我有 3 个个人资料表:

-学生-老师-工作人员

所有配置文件表都有不同的列(很少有公共(public)列)。这些表必须连接用户表，每个员工、老师、学生都必须在用户表中有帐户:

user table
-id
-login
-password
-etc.

将配置文件表连接到用户表的最佳方法是什么。

我找到了一个解决方案:

Storing User Profile as properties and values in tables

*i.e. Table to store possible options, table to store user_id, option_id and value*

-No redundant data stored, all data is relevant
-Most normalised method
-Slower to retrieve and update data

已更新

下面，我按照@user1032531所说创建了数据库。

Answer 1

创建一个名为“people”的 super 表。将学生、教师和工作人员共有的所有属性放入此表中。

然后为学生、教师和员工创建一个表，其中每个表的主键与人员表一一对应，并在这些表中放入唯一的属性。

-- MySQL Script generated by MySQL Workbench
-- 07/30/14 05:51:12
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`people`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`people` (
  `idpeople` INT UNSIGNED NOT NULL,
  `name` VARCHAR(45) NULL,
  `birthday` DATE NULL,
  `sex` CHAR(1) NULL,
  PRIMARY KEY (`idpeople`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`teachers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`teachers` (
  `people_idpeople` INT UNSIGNED NOT NULL,
  `unique_column` VARCHAR(45) NULL,
  PRIMARY KEY (`people_idpeople`),
  CONSTRAINT `fk_teachers_people`
    FOREIGN KEY (`people_idpeople`)
    REFERENCES `mydb`.`people` (`idpeople`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`students`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`students` (
  `people_idpeople` INT UNSIGNED NOT NULL,
  `unique_column` VARCHAR(45) NULL,
  PRIMARY KEY (`people_idpeople`),
  CONSTRAINT `fk_students_people1`
    FOREIGN KEY (`people_idpeople`)
    REFERENCES `mydb`.`people` (`idpeople`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`staff`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`staff` (
  `people_idpeople` INT UNSIGNED NOT NULL,
  `unique_column` VARCHAR(45) NULL,
  PRIMARY KEY (`people_idpeople`),
  CONSTRAINT `fk_staff_people1`
    FOREIGN KEY (`people_idpeople`)
    REFERENCES `mydb`.`people` (`idpeople`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;