In this article, we will learn about the concept of table inheritance, and its use in Azure Database for PostgreSQL.
Introduction
A database ecosystem is formed of various types of database objects like tables, views, procedures, schemas, etc. Often database objects like tables and views have interdependencies where the definition of a view depends on the existence of attributes in the base tables, which are generally bound by schema binding. Inter-related parent and child tables are bound by primary and foreign keys to maintain data integrity and facilitate data normalization. Though various forms of such bindings exist between different database objects, the concept of object-relational mapping where one object is inherited from another is often considered to be related to object-oriented programming languages but not with the database development paradigm. PostgreSQL database offers a unique way of compartmentalizing data as well as accessing this data in different ways to facilitate certain types of use-cases. Azure Database for PostgreSQL is Azure cloud’s offering of PostgreSQL.
What is table inheritance?
In my previous article, Using tables as templates in Azure Database for PostgreSQL, we learned about using tables as templates in Azure Database for PostgreSQL. This concept should not be confused with inheritance as that is only related to referencing tables as templates for the creation of new tables. In object-oriented theory, the concept of inheritance means that the attributes and properties of a parent class would be available in any class that inherits the parent class. If we map the same concept here in database terminology, it will mean that any table inherited from the parent table would derive its attributes apart from its own unique and independent attributes. Though the parent and child table share common attributes, the storage and management of data are completely kept separate.
Consider a use-case where we have a base products table which is derived by-products of different categories like automotive products, financial products, manufacturing products, etc., where each of it is a derived table. All these derived tables would have the attributes from the base class and specific attributes to the type of product data they host. While querying the base table, the query engine will intelligently append data from all the derived tables along with the base table as one unified data for the common attributes. Depending on the query filter criteria, the query will access only the derived tables that are required to service the query. In a way, the derived tables act as partitions of the base table. The benefit of this approach is that the data is compartmentalized separately in separate tables where each table can be queried as well as managed independently and still can be accessed as part of the base table. The indexes built of each derived table would be comparatively smaller compared to all the data hosted in one single table. This concept of table inheritance can be considered as a different way of implementing data partitioning.
Table inheritance in Azure Database for PostgreSQL
To implement this exercise, we need an instance of Azure Database for PostgreSQL in an Azure account. It is assumed that one such account with an instance of Azure Database for PostgreSQL Single Server edition already exists. We would also need an editor of the likes of pgAdmin which can connect to the instance of Azure Database for PostgreSQL using which we will execute queries on the database instance to create a base and inherited table. It is assumed that pgAdmin or a similar editor tool is already set up and connected to the Azure Database for the PostgreSQL instance.
The first task is to create a base table with some fields. For the sake of simplicity, let’s say that the base table is A and the derived table is B. Let’s create the parent table first. We can create any table with at least a couple of fields as shown below using the CREATE TABLE command.
Let’s say that we intend to create a child table B that would have the same fields as its parent table A, and an additional field that would be specific to table B. We can easily create this table using the CREATE TABLE command. To create an inherited table, we need to specify INHERITS keyword and specify the name of the table along with the schema name. This command would look as shown below.
|
1 |
create table b (year date) inherits (public.a) |
Execute this command and it would result in the creation of a new inherited table B. Right-click on the table, select Script’s menu item, and select CREATE SCRIPT. This would result in the generation of the script for the inherited table B as shown below. The key thing to observe is the different icons for tables A and B in the left-hand pane, and the fields detected to be inherited from table A as shown in the auto-generated script of table B.
Let’s now insert a record in the parent table A using the INSERT command as shown below.
|
1 |
insert into a values (1, 'Gauri', '01-01-2020') |
This record would be visible in table A, but when you would query table B, this record won’t be visible. Now let’s add a new record in table B using the INSERT command as shown below.
|
1 |
insert into b values (2, 'Sid', '01-01-2020', '01-01-2020') |
Now if we query table A again, we would be able to access both the records from table A as well as table B. But only the common attributes that the tables share would be accessible from the parent table A as shown below.
We have the option to query the records that are hosted in table A only. The way to execute a query that meets this criterion is by using the ONLY keyword before the table name in the SELECT query as shown below.
To understand how a query works and how objects are interrelated to each other, one of the common ways of analyzing is by analyzing the query plan. We can extract the plan by using the explain command or using the explain button or using the F7 key in case you are using pgAdmin. Shown below is the graphical view of the explained command which creates the query plan. It is evident from the query plan that when table A is queried, the query analyzer appends data from all the tables that inherit table A.
We looked at the scenarios of inserting records into the base and inherited tables in the Azure Database for PostgreSQL, and we saw how accessing these tables would result in retrieving the records. The records from derived tables are accessible from base tables. Let’s say that if we delete the record from the base table that is hosted in the derived table. To test this scenario, we can execute a DELETE command with the filter criteria as shown below. Here the record having the value of ID as 2 exists in table B, but we are executing the DELETE command on table A. If we look at the graphical view of the query plan, it will look as shown below. It shows that the delete command works on the aggregated result set of all the tables.
Query table B now and you would find that the record has got successfully deleted from table B. This clearly establishes that the two tables hosted in Azure Database for PostgreSQL are linked by inheritance and there is a cascading effect between the base and inherited tables.
Conclusion
We learned the concept of inheritance in this article. We learned how to create inherited tables in Azure Database for PostgreSQL and tested the effects of inheritance on data access and data manipulation.
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023
- Overview of the SQL Median function - January 4, 2023