How to Represent Inheritance in a Database?

1. Introduction

Inheritance in a database helps establish connections between tables or entities. Although inheritance is a core concept in object-oriented programming, its representation in databases requires careful planning.

In this tutorial, we’ll explore three main strategies for representing inheritance in a database: Single Table Inheritance, Class Table Inheritance, and Concrete Table Inheritance. We’ll simulate data to illustrate their pros and cons, focusing on PostgreSQL, MySQL, and SQL Server.

2. Inheritance Representation in a Database

The subclass and superclass concepts are crucial for effectively grasping and implementing inheritance in databases. These concepts form the foundation of inheritance representation and provide a structured way to model relationships between entities. So, here are a few benefits of understanding these terms in the context of database inheritance:

• Clarity in Database Design – Knowing what a superclass (parent table) and subclass (child table) are helps in designing a clear and logical database schema
• Reduced Redundancy – We can avoid duplicating common attributes across multiple tables by understanding superclass-subclass relationships
• Improved Data Integrity – Inheritance ensures that shared attributes are consistent across subclasses, as they’re defined in the superclass
• Efficient Querying – Understanding superclass-subclass relationships helps us write efficient queries by knowing where to find specific data

So, understanding the concept of the subclass and superclass is core to properly executing inheritance in databases.

3. Methods of Representing Inheritance

Let’s explore each of the different strategies for representing inheritance in a database. These strategies are applicable to PostgreSQL, MySQL, and SQL Server.

3.1. Single Table Inheritance

For Single Table Inheritance, also called table-per-hierarchy, we keep all the types in an inheritance structure in one table. The approach uses a special column called a discriminator to distinguish between the different types.

To illustrate this concept, let’s create a custom table named Record and populate it with relevant data:

CREATE TABLE Record (
    id BIGINT PRIMARY KEY,
    type VARCHAR(20) NOT NULL, -- discriminator column (Student or Faculty)
    name VARCHAR(255),
    national_id VARCHAR(20),
    birth_date DATE,
    enrollment_date DATE,      -- specific to Student
    graduation_date DATE,      -- specific to Student
    gpa DECIMAL(3, 2),         -- specific to Student
    position VARCHAR(50),      -- specific to Faculty
    start_date DATE,           -- specific to Faculty
    end_date DATE              -- specific to Faculty
);

After creating the table, we populate it with sample data:

-- Insert Students
INSERT INTO Record (id, type, name, national_id, birth_date, enrollment_date, graduation_date, gpa, position, start_date, end_date)
VALUES 
(1001, 'Student', 'John Liu', '123345566', '2001-04-05', '2020-01-15', '2024-06-15', 4.0, NULL, NULL, NULL),
(1003, 'Student', 'Rita Ora', '132345166', '2001-01-14', '2020-01-15', '2024-06-15', 4.2, NULL, NULL, NULL);

-- Insert Faculty
INSERT INTO Record (id, type, name, national_id, birth_date, enrollment_date, graduation_date, gpa, position, start_date, end_date)
VALUES 
(1, 'Faculty', 'Anubha Gupta', '1018901231', NULL, NULL, NULL, NULL, 'Professor', '2010-01-11', '2027-03-11'),
(2, 'Faculty', 'Peter Pan', '2130989011', NULL, NULL, NULL, NULL, 'Professor', '2007-05-11', NULL);

Now, let’s query the Record table:

SELECT * FROM Record;
 1001 | Student | John Liu     | 123345566   | 2001-04-05 | 2020-01-15      | 2024-06-15      | 4.00 |           |            | 
 1003 | Student | Rita Ora     | 132345166   | 2001-01-14 | 2020-01-15      | 2024-06-15      | 4.20 |           |            | 
    1 | Faculty | Anubha Gupta | 1018901231  |            |                 |                 |      | Professor | 2010-01-11 | 2027-03-11
    2 | Faculty | Peter Pan    | 2130989011  |            |                 |                 |      | Professor | 2007-05-11 | 

In Single Table Inheritance, all the data are stored in a single table. For example, student and faculty data are stored in a single table called Record. In this case, we can use the type column, which acts as a discriminator field, to query for either student or faculty data. This method makes it simple to manage and maintain the database schema and queries.

However, maintaining data integrity and appropriately handling NULL values are crucial. In terms of advantages, the Single Table Inheritance approach simplifies querying with efficient, JOIN-free operations like SELECT * FROM Record.

On the other hand, it leads to wasted space due to NULL values in type-specific columns and lacks flexibility, as adding new subclasses requires altering the table schema.

3.2. Class Table Inheritance

Class Table Inheritance, or table-per-class, is a strategy to represent inheritance that involves creating a separate table for each class in the hierarchy. In this concept, subclass tables reference the superclass table via a foreign key. To illustrate this, we’ll create a superclass (base) table, Record_Mod, and two subclass tables: Student_Mod and Faculty_Mod:

CREATE TABLE Record_Mod (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    national_id VARCHAR(20),
    birth_date DATE
);

CREATE TABLE Student_Mod (
    id BIGINT PRIMARY KEY,
    enrollment_date DATE,
    graduation_date DATE,
    gpa DECIMAL(3, 2),
    FOREIGN KEY (id) REFERENCES Record_Mod(id)
);

CREATE TABLE Faculty_Mod (
    id BIGINT PRIMARY KEY,
    position VARCHAR(50),
    start_date DATE,
    end_date DATE,
    FOREIGN KEY (id) REFERENCES Record_Mod(id)
);

INSERT INTO Record_Mod (id, name, national_id, birth_date)
VALUES
(1001, 'John Liu', '123345566', '2001-04-05'),
(1003, 'Rita Ora', '132345166', '2001-01-14'),
(1, 'Anubha Gupta', '1018901231', NULL),
(2, 'Peter Pan', '2130989011', NULL);

INSERT INTO Student_Mod (id, enrollment_date, graduation_date, gpa)
VALUES
(1001, '2020-01-15', '2024-06-15', 4.0),
(1003, '2020-01-15', '2024-06-15', 4.2);

INSERT INTO Faculty_Mod (id, position, start_date, end_date)
VALUES
(1, 'Professor', '2010-01-11', '2027-03-11'),
(2, 'Professor', '2007-05-11', NULL);

From the custom schema above, we see that this strategy eliminates wasted space introduced by NULL compared to the previous concept. Therefore, it ensures that each table contains only relevant columns, avoiding NULL values in type-specific fields.

Additionally, it provides a clear separation of concerns, making it straightforward to add new subclasses, such as a Staff_Mod table. As a result, we can simply create a new table without modifying existing ones.

However, this approach also has its drawbacks. Querying data across the hierarchy requires joining multiple tables, as illustrated in the follow-up example query:

SELECT p.id, p.name, p.national_id, p.birth_date,
       s.enrollment_date, s.graduation_date, s.gpa,
       f.position, f.start_date, f.end_date
FROM Record_Mod p
LEFT JOIN Student_Mod s ON p.id = s.id
LEFT JOIN Faculty_Mod f ON p.id = f.id;

1    | Anubha Gupta | 1018901231 |            |            |            |      | Professor | 2010-01-11 | 2027-03-11
2    | Peter Pan    | 2130989011 |            |            |            |      | Professor | 2007-05-11 | 
1003 | Rita Ora     | 132345166  | 2001-01-14 | 2020-01-15 | 2024-06-15 | 4.20 |           |            | 
1001 | John Liu     | 123345566  | 2001-04-05 | 2020-01-15 | 2024-06-15 | 4.00 |           |            | 

Consequently, as shown above, querying this schema can become complex and less efficient, particularly for large datasets. Furthermore, the increased number of tables can result in a more complex schema, which can make maintenance and understanding of the database structure more challenging.

3.3. Concrete Table Inheritance

Concrete Table Inheritance, also known as table-per-concrete-class, creates a separate table for each concrete class in the hierarchy. Unlike other inheritance strategies, each table includes all attributes from both the base class and the subclass, eliminating the need for a shared superclass table.

To illustrate this, we’ll create two tables: Student_CTI and Faculty_CTI, each containing all relevant attributes:

CREATE TABLE Student_CTI (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    national_id VARCHAR(20),
    birth_date DATE,
    enrollment_date DATE,
    graduation_date DATE,
    gpa DECIMAL(3, 2)
);

CREATE TABLE Faculty_CTI (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    national_id VARCHAR(20),
    birth_date DATE,
    position VARCHAR(50),
    start_date DATE,
    end_date DATE
);

INSERT INTO Student_CTI (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
VALUES
(1001, 'John Liu', '123345566', '2001-04-05', '2020-01-15', '2024-06-15', 4.0),
(1003, 'Rita Ora', '132345166', '2001-01-14', '2020-01-15', '2024-06-15', 4.2);

INSERT INTO Faculty_CTI (id, name, national_id, birth_date, position, start_date, end_date)
VALUES
(1, 'Anubha Gupta', '1018901231', NULL, 'Professor', '2010-01-11', '2027-03-11'),
(2, 'Peter Pan', '2130989011', NULL, 'Professor', '2007-05-11', NULL);

From the schema and data above, we observe that Concrete Table Inheritance avoids the need for joins when querying specific subclasses. For example, let’s carry out a simple task of retrieving all students:

SELECT * FROM Student_CTI;
 1001 | John Liu | 123345566   | 2001-04-05 | 2020-01-15      | 2024-06-15      | 4.00
 1003 | Rita Ora | 132345166   | 2001-01-14 | 2020-01-15      | 2024-06-15      | 4.20

This approach ensures that each table is self-contained, making queries straightforward and efficient for specific types.

However, it also introduces redundancy by duplicating common attributes like name, national_id, and birth_date across tables. This duplication can lead to increased storage requirements and potential inconsistencies if not managed carefully.

Additionally, querying across the entire hierarchy requires combining results from multiple tables using a UNION operation:

SELECT id, name, national_id, birth_date, 'Student' AS type
FROM Student_CTI
UNION ALL
SELECT id, name, national_id, birth_date, 'Faculty' AS type
FROM Faculty_CTI;

 1001 | John Liu     | 123345566   | 2001-04-05 | Student
 1003 | Rita Ora     | 132345166   | 2001-01-14 | Student
    1 | Anubha Gupta | 1018901231  |            | Faculty
    2 | Peter Pan    | 2130989011  |            | Faculty

Consequently, growing datasets or introducing new subclasses can make this approach less efficient and harder to maintain. For instance, adding a new subclass like Staff would require creating a new table with all common attributes duplicated, further increasing schema complexity.

4. Conclusion

In this article, we explored three strategies for representing inheritance in databases: Single Table Inheritance, Class Table Inheritance, and Concrete Table Inheritance. Each method has its strengths, respectively: the first simplifies queries, the second avoids NULL values, and the third ensures self-contained tables.

However, trade-offs like wasted space, complex joins, or redundant data must be considered. Choosing the right approach depends on our application’s needs, balancing simplicity, efficiency, and scalability.