Single Table Inheritance in SQL Design Patterns

4.4.1 Single Table Inheritance

Introduction

Single Table Inheritance (STI) is a design pattern used in relational databases to handle inheritance hierarchies. In this approach, all classes in the hierarchy are stored in a single table, with each row representing an instance of a class. This pattern is particularly useful when you want to simplify your database schema and avoid complex joins, but it comes with its own set of challenges, such as handling NULL values and ensuring data consistency.

Design Pattern Name

Single Table Inheritance (STI)

Category

Inheritance Mapping Patterns

Intent

The intent of Single Table Inheritance is to map an entire class hierarchy to a single database table. This pattern is used to simplify the database schema by avoiding multiple tables for each class in the hierarchy, thus making queries straightforward and efficient.

Key Participants

• Base Class: The parent class in the hierarchy, which contains common attributes.
• Derived Classes: The child classes that inherit from the base class and may have additional attributes.
• Discriminator Column: A special column used to identify the type of each row, indicating which class in the hierarchy the row represents.

Applicability

Use Single Table Inheritance when:

• You have a simple class hierarchy with shared attributes.
• You want to minimize the number of tables in your database.
• You prefer simple and efficient queries without complex joins.
• You can tolerate the presence of NULL values for attributes not applicable to all classes.

Sample Code Snippet

Let’s consider an example of a class hierarchy representing different types of vehicles: Car, Truck, and Motorcycle. We’ll use Single Table Inheritance to store all these types in a single table called vehicles.

 1CREATE TABLE vehicles (
 2    id INT PRIMARY KEY,
 3    vehicle_type VARCHAR(50),
 4    make VARCHAR(50),
 5    model VARCHAR(50),
 6    year INT,
 7    num_doors INT,          -- Specific to Car
 8    payload_capacity INT,   -- Specific to Truck
 9    has_sidecar BOOLEAN     -- Specific to Motorcycle
10);
11
12-- Insert data for different vehicle types
13INSERT INTO vehicles (id, vehicle_type, make, model, year, num_doors)
14VALUES (1, 'Car', 'Toyota', 'Camry', 2020, 4);
15
16INSERT INTO vehicles (id, vehicle_type, make, model, year, payload_capacity)
17VALUES (2, 'Truck', 'Ford', 'F-150', 2019, 1000);
18
19INSERT INTO vehicles (id, vehicle_type, make, model, year, has_sidecar)
20VALUES (3, 'Motorcycle', 'Harley-Davidson', 'Sportster', 2021, FALSE);

Diagrams

To better understand the structure of Single Table Inheritance, let’s visualize the table schema using a class diagram.

    classDiagram
	    class Vehicle {
	        +int id
	        +string vehicle_type
	        +string make
	        +string model
	        +int year
	        +int num_doors
	        +int payload_capacity
	        +bool has_sidecar
	    }
	    class Car {
	        +int num_doors
	    }
	    class Truck {
	        +int payload_capacity
	    }
	    class Motorcycle {
	        +bool has_sidecar
	    }
	    Vehicle <|-- Car
	    Vehicle <|-- Truck
	    Vehicle <|-- Motorcycle

Design Considerations

Pros

• Simplicity: With all data in a single table, queries are straightforward and do not require joins across multiple tables.
• Performance: Single table queries can be more efficient, especially for read-heavy applications.
• Flexibility: Adding new types to the hierarchy is as simple as adding new columns, provided the changes are backward compatible.

Cons

• NULL Values: Columns that are not applicable to all classes will contain NULLs, potentially leading to wasted space.
• Data Integrity: Ensuring data consistency can be challenging, as constraints must be managed at the application level.
• Scalability: As the hierarchy grows, the table can become unwieldy with many columns, leading to maintenance challenges.

Differences and Similarities

Single Table Inheritance is often compared with other inheritance mapping patterns like Class Table Inheritance and Concrete Table Inheritance. Here are some key differences:

• Class Table Inheritance: Each class in the hierarchy has its own table, which can lead to complex joins but avoids NULL values.
• Concrete Table Inheritance: Each concrete class has its own table, which can lead to data duplication but simplifies queries for specific types.

Try It Yourself

To get hands-on experience with Single Table Inheritance, try modifying the example above:

• Add a new vehicle type, such as Bicycle, and include attributes specific to bicycles.
• Experiment with querying the vehicles table to retrieve only cars or only trucks.
• Consider how you might handle additional attributes that are shared by some, but not all, vehicle types.

Knowledge Check

• Explain the main advantage of using Single Table Inheritance.
• What are the potential downsides of using this pattern?
• How does the discriminator column function in Single Table Inheritance?

Embrace the Journey

Remember, mastering SQL design patterns is a journey. As you explore Single Table Inheritance, consider how it fits into your overall database design strategy. Keep experimenting, stay curious, and enjoy the process of learning and applying these powerful patterns.

References and Links

• Single Table Inheritance on Wikipedia
• SQL Design Patterns
• MDN Web Docs on SQL

Quiz Time!

By understanding and applying the Single Table Inheritance pattern, you can effectively manage class hierarchies in your database design, balancing simplicity and performance with the challenges of data integrity and scalability.