Inheritance in SQL - way to not repeat ourselves (DRY) while creating new tables

Inheritance with SQL

If you are a back end developer who has to deal with the design and development of a SQL based Data Base Management System, there is a fair chance that you might have come across situations, where you have to figure out how to represent  similar data sets in tables without repeating yourself. This is the kind of situations where you can benefit by using inheritance based approach in the Database  Design.

We'll take a virtual university for the sake of explanation, which will make things much easier. A typical university can contain two types of people, students and staffs. And staffs can be further classified into lecturers, front office, janitors, etc and students can be further classified into undergrads, grads, etc, but for the sake of simplicity we'll just stay with only one level in depth of simple inheritance in this post.

Know what you're building

Lets jot down all the parameters that constitutes students and staffs. I'll try to be minimalistic here, as our goal is not to build a system to keep track of university records but to give you guys an idea of how to build something like that using inheritance to achieve DRY(Don't Repeat Yourself) principle.

Students

  • Student ID
  • First Name
  • Last Name
  • Date of Birth
  • Major
  • Enrolled Date
  • Mobile Number
  • Mail
  • Student Type - Undergrad/ Grad /etc
  • Address

Staffs

  • Staff ID
  • First Name
  • Last Name
  • Title
  • Date of Birth
  • Department
  • Joined Date
  • Mobile Number
  • Mail
  • Staff Type - Lecturer / Professor / Front office/ Janitor/ etc
  • Address

Find the similarities

and so the differences

After listing all the parameters we need, we can now see substantial similarities between the parameters of students and staffs. Now lets pair the similarities and merge them together to a more generic type called "Users", which will include the overlapping details of both students and staffs.

Users

  • Enrollment ID -  which is previously called as Student ID and Staff ID, but does the same purpose
  • First Name
  • Last Name
  • Date of Birth
  • Enrolled Date - this parameter does the same purpose of the "JoinedDate" parameter of Staffs
  • Mobile Number
  • Mail
  • Address

Then let's list down all the remaining dissimilar parameter

Students

  • Major
  • Student Type

Staffs

  • Title
  • Department

Define Tables, and Relations

Now we know what exactly to be done with the system(university record system), it's easier for us to convert the above abstract version of parameters and data types to a more defined version, tables and relations in a SQL database system. One thing, one has to notice in the above abstract representation is that, there's not linkage between Users data type and Students, and Users and Staffs. We can use a unique parameter(or a group of parameters) from the "Users" datatype as Primary Key for the "Users" Table(A SQL table representing the Users data type) and add the same as Foreign Key in the "Users" and "Staffs" Tables.

Now the abstract version of the data types look like this

Users

  • Enrollment ID - Primary Key - unique to all the users
  • First Name
  • Last Name
  • Date of Birth
  • Enrolled Date
  • Mobile Number
  • Mail
  • Address

Students

  • Enrollment ID - Foreign Key - added to link the record to the "Users" table
  • Major
  • Student Type

Staffs

  • Enrollment ID - Foreign Key - added to link the record to the "Users" table
  • Title
  • Department

Below are the queries to create the Tables with Primary Keys and Foreign Keys as discussed above,

CREATE DATABASE `university_record`;

CREATE TABLE `users` (
  `EnrollmentID` int(11) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(45) DEFAULT NULL,
  `LastName` varchar(45) DEFAULT NULL,
  `DateOfBirth` datetime(6) DEFAULT NULL,
  `EnrolledDate` datetime(6) DEFAULT NULL,
  `MobileNumber` varchar(15) DEFAULT NULL,
  `Mail` varchar(500) DEFAULT NULL,
  `Address` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`EnrollmentID`)
);

CREATE TABLE `students` (
  `EnrollmentID` int(11) NOT NULL,
  `Major` varchar(45) DEFAULT NULL,
  `StudentType` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`EnrollmentID`),
  CONSTRAINT `StudentEnrollmentID` FOREIGN KEY (`EnrollmentID`) REFERENCES `users` (`EnrollmentID`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE `staffs` (
  `EnrollmentID` int(11) NOT NULL,
  `Title` varchar(45) DEFAULT NULL,
  `Department` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`EnrollmentID`),
  CONSTRAINT `StaffEnrollmentID` FOREIGN KEY (`EnrollmentID`) REFERENCES `users` (`EnrollmentID`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

The Entity Relationship diagram for our virtual university record system will look like,

ERR Diagram

Query through the data

The good data-base design approach should not only support DRY principle but should also run efficient and flexible queries.

SELECT

SELECT * FROM users INNER JOIN students ON users.EnrollmentID=students.EnrollmentID;

UPDATE

UPDATE users INNER JOIN students ON users.EnrollmentID=students.EnrollmentID SET users.Address="City,State,Country" WHERE students.EnrollmentID=1;

DELETE

To delete the whole record you should use a property of ForeignKey called "Cascade" during "On Delete" event

To set that to the "Students" table, one should alter the table like below

ALTER TABLE `inheritance`.`students` DROP FOREIGN KEY `StudentEnrollmentID`; ALTER TABLE `inheritance`.`students` ADD CONSTRAINT `StudentEnrollmentID` FOREIGN KEY (`EnrollmentID`) REFERENCES `inheritance`.`users` (`EnrollmentID`) ON DELETE CASCADE ON UPDATE NO ACTION;

 

And running the normal delete query on "Users" table(Parent table) will also remove the record from the "Students" table,

DELETE users FROM users WHERE users.EnrollmentID=1;

 

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.