Class Table Inheritance
Table per Type (TPT), another name for Class Table Inheritance, is the process of making a separate table for every entity type, including the superclass and all of its subclasses. Only the properties pertinent to that particular entity type are contained in each table, and foreign keys are used to form associations between the tables. By cutting down on redundancy, this method preserves data integrity while providing improved data organization. To extract data from many tables, however, more intricate queries requiring joins can be needed.
Example:
Let’s understand class table inheritance with the help of animal table.
Parent Table (animal): This table acts as the foundation, holding shared characteristics amongst all animals. It contains data such as the species, which denotes the kind of animal, and the id, which uniquely identifies every animal.
CREATE TABLE animals (
id SERIAL PRIMARY KEY,
species VARCHAR(50)
);
Child Tables (mammals and birds): Each child table depicts a certain kind of animal (mammal or bird). They might have extra properties unique to the subtype in addition to those inherited from the parent table (animals). Every child table has an id column that functions as both the child table’s primary key and a foreign key that references the id of the parent table.
CREATE TABLE mammals (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
num_legs INTEGER,
FOREIGN KEY (id) REFERENCES animals(id)
);
CREATE TABLE birds (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
wingspan DECIMAL,
FOREIGN KEY (id) REFERENCES animals(id)
);
Query for inserting values:
— Inserting a record for a mammal
INSERT INTO animals (species) VALUES (‘Mammal’);
INSERT INTO mammals (id, name, num_legs) VALUES (1, ‘Dog’, 4);
— Inserting a record for a bird
INSERT INTO animals (species) VALUES (‘Bird’);
INSERT INTO birds (id, name, wingspan) VALUES (2, ‘Eagle’, 2.5);
The output table for the above is:
|
id |
species |
name |
num_legs |
wingspan |
|---|---|---|---|---|
|
1 |
Mammal |
Dog |
4 |
NULL |
|
2 |
Bird |
Eagle |
NULL |
2.5 |
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.