E/R Zero or One to Many relationships

While 1:n, 1:1 and n:m relationships are thoroughly used to design logical database models, there another another kind, {0|1}:n that may come in handy in some cases. Lets analyze this relationship carefully.

Imagine a relationship between students and classrooms. Every classroom can have N students, meaning this zero, one, or many pupils. From the other side, every student must have a classroom, otherwise she won’t be allowed to study. This is a classic 1:n relationship, used in most of the real cases that we may encounter.

But what if, let’s say, a student had finished her studies and, however, wasn’t deleted from the database? (just to keep the records and other sensible information). In that case, the statement ‘every student must have one and only one classroom’ would be wrong. Instead, we could say ‘every student can have one classroom or none’. Now we are talking about zero or one to many relationships.

A practical situation for this type of relationships are tree structures on auto-referenced tables. For instance, a category tree where every tuple can have a parent or none (thus being at root level). While a possible solution to this problem might be to create a ‘fake’ root element that all the others inherit from unless said otherwise, this approach forces the design to include a ‘meta element’, not meant to represent data at all. That’s to say, it has to be programmatically removed from all database queries so not to have it poking around.

The right solution to this problem is making the foreign key nullable so that we can effectively represent the case when there is no parent while respecting relational integrity. In addition, the cascading options on deletion should be set as ‘set null’ so that a possible deletion of a classroom does not delete all the students that might be related:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- ------------------------------------------
-- Table classrooms 
-- ------------------------------------------
CREATE TABLE classrooms(
  classroomID INT NOT NULL AUTO_INCREMENT ,
  classroomName VARCHAR(50) NOT NULL ,
  PRIMARY KEY (classroomID)
) ENGINE = InnoDB;
 
-- ------------------------------------------
-- Table students
-- ------------------------------------------
CREATE TABLE students (
  studentID INT NOT NULL AUTO_INCREMENT ,
  studentName VARCHAR(50) NOT NULL ,
  classroomID INT DEFAULT NULL ,
  PRIMARY KEY (studentID) ,
  CONSTRAINT fk_students_classrooms
    FOREIGN KEY (classroomID)
    REFERENCES classrooms.classroomID
    ON DELETE SET NULL
    ON UPDATE CASCADE
) ENGINE = InnoDB;

Further reading: here and here.

2 thoughts on “E/R Zero or One to Many relationships

  1. Thanks for your question Luis. The answer is no, you are not breaking the foreign key rule because the rule itself allows you to have null values.
    Regarding this precise example, it all depends on the context and the meaning of your data. In some schools a student wouldn’t be allowed not to be assigned to a classroom. However, on a different scenario, a student might need to be registered or kept after he finishes his courses, without necessarilly having a classrom assigned.
    I am not an authority on the matter, though – you should always ask your current teacher / professor about particular cases.

    Regards,

Leave a Reply

Your email address will not be published. Required fields are marked *