ER relation with unique key I've a problem about my ER diagram. Assume that I want to draw a diagram with student, member and team. Here is my specs:


A student does not have to be a member of any team.
A student could be a member of at most one team.
member has a unique member ID.


I did my ER Diagram:

                                                  member-id (key attribute)
                                                            |
--------                          -                    -----------
- team -------------------- -  - mem -   ------------- - student -
--------                          -                    -----------


As I said before, student does not have to be a member of any team. In this case, my diagram works fine? I asked it because of the member_id is a unique key.
For now, I see two possible problems in your model:


Member of a team can exist without a reference to Student.

1.1. If you delete a Member with ON DELETE CASCADE foreign key option in Student it will remove your student.

1.2. If you delete a Student, there might be a Member, that not referenced to any Student.
If you would like to place Member properties to Student table, then you will have to set them to NULL each time, then you need to unreference Student and Member. Also it will lead to table Student will loose it's independence. If you would not do that, this proplem will never happen.




I might suggest something like this instead:



Where: AI = AUTO_INCREMENT, NN = NOT NULL, UN = UNSIGNED.


Student can not exist in two teams, because there might be only one record in table Member for each Student record.
Student can be a member if record in the Member table exists for the Student table record. However, he might have no record in the Member, but still have a record in the Student table. Means, that he is not member of any team.


How to achieve that?


Make a primary key of the Member table to be a foreign key, that referes to Student table's primary key. PRIMARY KEYs are UNIQUE by default and can not have duplicate values. So there wouldn't be two records in the Member table for one record of table Student.
Make a foreign key for the table Member, that referes to the Team table's primary key. Only member can have a team.
As there is only one record for each Student can be placed in Member table, each student will have to be only in one team.
If you delete Member record, Student associated record will no longer be a member of any team, but still be a student.


Test script (assuming MySQL RDBMS):

DROP SCHEMA IF EXISTS `test`;
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `test`;

CREATE TABLE IF NOT EXISTS `Team` (
  `IDTeam` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `etc` VARCHAR(45) NULL,
  PRIMARY KEY (`IDTeam`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Student` (
  `IDStudent` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`IDStudent`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Member` (
  `IDMember` INT UNSIGNED NOT NULL,
  `TeamID` INT UNSIGNED NOT NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`IDMember`),

  INDEX `fk_Member_Team` (`TeamID` ASC),

  CONSTRAINT `fk_Member_Student`
    FOREIGN KEY (`IDMember`)
    REFERENCES `Student` (`IDStudent`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `fk_Member_Team`
    FOREIGN KEY (`TeamID`)
    REFERENCES `Team` (`IDTeam`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;