Single Table Inheritance
Single Table Inheritance, also known as Table per Hierarchy (TPH), stores all entities—superclass and subclass—in a single table. Every record in the table has a unique marker, known as a discriminator, that designates the particular kind of entity it represents. Because fewer tables and intricate connections are required, this method makes data storage and retrieval easier. But it could lead to sparse tables with a lot of null values for characteristics that don’t apply to some kinds of entities.
Example
Let us take a situation when we have two categories of workers: full-time and part-time.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
type VARCHAR(20), — Discriminator column
salary DECIMAL,
hourly_rate DECIMAL
)
Let’s insert some sample data
— Full-time employee
INSERT INTO employees (name, type, salary) VALUES (‘Ravi Kumar’, ‘full-time’, 50000.00);
— Part-time employee
INSERT INTO employees (name, type, hourly_rate) VALUES (‘Rishi Patel’, ‘part-time’, 20.00);
Now, let’s query the employees table
SELECT * FROM employees;
The above query will return the following table as output:
|
id |
name |
type |
salary |
hourly_rate |
|---|---|---|---|---|
|
1 |
Ravi Kumar |
full-time |
50000.00 |
NULL |
|
2 |
Rishi Patel |
part-time |
NULL |
20.00 |
In this example, full-time and part-time employees are stored in a single table called employees. The employee type is ascertained by using the type field as a discriminator. This method makes it simpler to manage and maintain the database schema and queries. However, maintaining data integrity and appropriately handling NULL values are crucial.
Inheritance Hierarchies in DBMS
Inheritance Hierarchies are crucial to building a structured and well-organized database. It is comparable to the idea of inheritance found in object-oriented programming languages. The main ideas of inheritance hierarchies in database management systems (DBMS) will be covered in this article, along with definitions, procedures, and in-depth examples to aid in understanding.