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