Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. A student does not have to be a member of any team.
  2. A student could be a member of at most one team.
  3. 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.

share|improve this question

1 Answer 1

For now, I see two possible problems in your model:

  1. 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.

  2. 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:

Diagram

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

  1. Student can not exist in two teams, because there might be only one record in table Member for each Student record.
  2. 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?

  1. 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.
  2. Make a foreign key for the table Member, that referes to the Team table's primary key. Only member can have a team.
  3. 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.
  4. 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;
share|improve this answer
1  
It can be done simpler: just add a nullable TeamId to your Students table. –  A-K Oct 6 '13 at 22:17
    
@AlexKuznetsov But what if Members may have properties, that not related to Students ? –  HAL9000 Oct 7 '13 at 2:29
1  
I do not see this requirement just yet. As such, we should use the simplest working solution - we can easily refactor and add complexity later. YAGNI. –  A-K Oct 7 '13 at 3:04
    
@AlexKuznetsov , you may be right ;). Still I believe, that YAGNI more suits to application code, but not to database model. Normalization will kill application implementation too, if there is no abstraction layer (like stored routines) between actual storage structure and application sql queries. Primarily opinion-based suggestion. You may write your version as another answer, of course. Both version have rights exist. Good note though, thanks. –  HAL9000 Oct 7 '13 at 4:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.