Implementation of Object Inheritance relationships in databases and PowerDesigner Design

In object-oriented programming, object inheritance is a common practice. However, in Relational Database Management System (RDBMS), foreign keys are used to represent entities (tables) the relationship between them. How can we represent the inherited relationship in RDBMS? Generally, there are three implementation methods:

• Concrete Table Inheritance (specific Table Inheritance)
• Single Table Inheritance (Single Table Inheritance)
• Class Table Inheritance (Class Table Inheritance)

For example, in an educational administration system, there are two teachers and students, both of which are sub-classes of the "Person" object. Therefore, we can create a Person table with the public attributes of this table: name, gender, and so on. It is also the unique identifier of the data, an ID. Teachers have special attributes of teachers, such as titles and students, such as student IDs. Therefore, we can create three tables: Person, Teacher, and student. the relationship is in PowerDesigner:

1. Specific table inheritance.

If no parent object is created, all attributes of the parent object are transferred to the sub-object to create a table for each sub-object. If this method is used, you only need to create a Teacher table and a Student table without the Person table. In PowerDesigner, double-click the inherited node to open the Properties window and cancel the "Generate Parent" option, select "Generate children" and select "Inherit all attributes ",:

The generated database table will:

2. Single Table inheritance.

The attribute of all parent objects and sub-objects is listed in a wide table, and an ID column is used to indicate the sub-classes of data stored in the row. In PowerDesigner, modify the attributes of the inherited nodes, cancel "Generate children", select "Generate parent", and add an ID column named "PersonType" below ,:

The generated database table is represented in a wide table:

We can see that the column in Person integrates all the columns in the three tables "Person", "Teacher", and "Student", and an additional column "PersonType, this column is used to identify whether a student or a teacher represents this row of data.

3. Class table inheritance.

Create a corresponding table for the parent object and each sub-object, and set the primary key of the sub-table in the sub-table as the foreign key associated with the parent table. In PowerDesigner, you can generate Parent and Child tables for the inherited node attributes. In addition, select "Inherit only primary attributes. :

Generated database table:

Here we can see that the primary key of the Teacher and the primary key of the Student are at the same time the foreign key of the table, connected to the Person table.

PS: If nhib.pdf is used for programming, you can refer to this article to introduce the three inherited configuration methods of nhib.pdf.

If you use Entity Framework for programming, you can refer to this article.