代码之家 › 专栏 › 技术社区 › samara

表之间的mysql关系

diagram sql mysql

-2

samara · 技术社区 · 10 年前

我的问题是:
如何构建一个图表,我不理解多对多,或一对多,以及主键和外键等。。

这是我的密码。关于一家医院,我有:

NURSES , WARD , PATIENTS , DOCTORS .

CREATE TABLE Nurses(
  `idNurses` INT NOT NULL  AUTO_INCREMENT,
  `Fname` VARCHAR(45) NULL,
  `Lname` VARCHAR(45) NULL,
  `Speciality` VARCHAR(45) NULL,
  PRIMARY KEY (`idNurses`))
ENGINE = InnoDB;


CREATE TABLE Doctors (
  `idDoctors` INT NOT NULL  AUTO_INCREMENT,
  `Fname` VARCHAR(45) NULL,
  `Lname` VARCHAR(45) NULL,
  `Speciality` VARCHAR(45) NULL,
  PRIMARY KEY (`idDoctors`))
ENGINE = InnoDB;



CREATE TABLE Ward(
  `idWard` INT NOT NULL  AUTO_INCREMENT,
  `Name` VARCHAR(45) NULL,
  `Location` VARCHAR(45) NULL,
  `Nurses_idNurses` INT NOT NULL,
  `Doctors_idDoctors` INT NOT NULL,
  PRIMARY KEY (`idWard`, `Nurses_idNurses`, `Doctors_idDoctors`),
  INDEX `fk_Ward_Nurses_idx` (`Nurses_idNurses` ASC),
  INDEX `fk_Ward_Doctors1_idx` (`Doctors_idDoctors` ASC),
  CONSTRAINT `fk_Ward_Nurses`
    FOREIGN KEY (`Nurses_idNurses`)
    REFERENCES `mydb`.`Nurses` (`idNurses`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Ward_Doctors1`
    FOREIGN KEY (`Doctors_idDoctors`)
    REFERENCES `mydb`.`Doctors` (`idDoctors`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE Patient (
  `idPatient` INT NOT NULL  AUTO_INCREMENT,
  `Fname` VARCHAR(45) NULL,
  `Lname` VARCHAR(45) NULL,
  `DOB` DATE NULL,
  `Phone_number` INT NULL,
  `Ward_idWard` INT NOT NULL,
  `Ward_Nurses_idNurses` INT NOT NULL,
  `Ward_Doctors_idDoctors` INT NOT NULL,
  PRIMARY KEY (`idPatient`, `Ward_idWard`, `Ward_Nurses_idNurses`, `Ward_Doctors_idDoctors`),
  INDEX `fk_Patient_Ward1_idx` (`Ward_idWard` ASC, `Ward_Nurses_idNurses` ASC, `Ward_Doctors_idDoctors` ASC),![enter image description here][1]
  CONSTRAINT `fk_Patient_Ward1`
    FOREIGN KEY (`Ward_idWard` , `Ward_Nurses_idNurses` , `Ward_Doctors_idDoctors`)
    REFERENCES `mydb`.`Ward` (`idWard` , `Nurses_idNurses` , `Doctors_idDoctors`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

1 回复 | 直到 10 年前

Eric 10 年前

表之间的关系取决于您想要的设计类型,即这是您必须决定的。

1:病房有多名患者。
2:患者有一个病房。
3:医生可以有多名护士与他们一起工作,也可以有多名患者。
4:山姆和护士在一起,还有多个或只有一个病房分配给他们。

正如你所看到的,这里有n种可能的组合,没有绝对的解决方案。根据您的设计,提出数据库模式。