In the process of designing our entity relationship diagram for a database, we may find that attributes of two or more entities overlap, meaning that these entities seem very similar but still have a few differences. In this case, we may create a subtype of the parent entity that contains distinct attributes. A parent entity becomes a supertype that has a relationship with one or more subtypes.
First, let’s take a closer look at a simple class diagram.
The UML symbol for a subclass association is an open arrowhead that points to the parent class.
The subclass association line is labeled with specialization constraints. Constraints are described along two dimensions:
- In an incomplete specialization only some instances of the parent class are specialized (have unique attributes). Other instances of the parent class have only the common attributes.
- In a complete specialization, every instance of the parent class has one or more unique attributes that are not common to the parent class.
- In a disjoint specialization, an object could be a member of only one specialized subclass.
- In an overlapping specialization, an object could be a member of more than one specialized subclass.
The following diagram presents class Client.
In class Client we distinguish two subtypes: Individual and Company. This specialization is disjoint (client can be an individual or a company) and complete (these are all possible subtypes for supertype).
Let’s model this situation and discuss the results (I will use Vertabelo, our online database modeling tool).
One table implementation
In a one table implementation, table
client has attributes of both types.
The diagram below shows the table
client and two views:
In this implementation:
- Access to supertype rows is optimal (it is simple to have a list of all clients, it’s not necessary to make costly joins)
- Effectiveness problem with access to subtype rows. Some rows have to be discarded, because one table contains rows for all (in this case two) subtypes.
- There has to be an additional attribute to specify the subtype (attribute ‘type’ may have value ‘i’ for ‘individual’ and ‘c’ for ‘company’ and no other value).
- It's easy to change the object’s subtype (we have to change ‘type’ from ‘i’ to ‘c’ or the other way around).
- Many attributes are subtype-specific, and these columns have null values on rows that apply to other subtype’s attributes.
CREATE VIEW individual AS select id, address, name, surname from client where status = 'i';
CREATE VIEW company AS select id, address, name, surname from client where status = 'c';
In a two-table implementation, we create a table for each of the subtypes. Each table gets a column for all attributes of the supertype and also a column for each attribute belonging to the subtype. Access to information in this situation is limited, that’s why it is important to create a view that is the union of the tables. We can add an additional attribute called ‘type’ that describes the subtype.
The diagram below presents two tables,
company, and a view (the blue one) called
The view’s script is as follows:
CREATE VIEW client (id, address, name, surname, company_name, industry) AS select id, address,name, surname,null, null, type from individual union all select id, address, null, null, company_name, industry, type from company;
However, the example above generates some problems:
- problem with implementation UID for supertype. There is no common ID.
- if we want to change object’s type, for example, company to client, we need to do many INSERT, DELETE operations
In a third solution we create a single table
client_t for the parent table, containing common attributes for all subtypes, and tables for each subtype (
company_t) where the primary key in
client_t (base table) determines foreign keys in dependent tables. There are three views:
CREATE VIEW client AS select client_t.id, client_t.address, company_t.company_name, company_t.industry, individual_t.name, individual_t.surname from client_t, individual_t, company_t where client_t.id = individual_t.client_id and client_t.id = company_t.client_id;
CREATE VIEW individual AS select client_t.id, client_t.address, individual_t.name, individual_t.surname from client_t, individual_t where client_t.id = individual_t.client_id;
CREATE VIEW company AS select client_t.id, client_t.address, company_t.company_name, company_t.industry from client_t, company_t where client_t.id = company_t.client_id;
In this situation:
- it’s necessary to make (too) many (too) costly joins,
- changing the subtype is not effective,
- there is no problem with UIDs, because UIDs for subtypes are determined by the supertype’s UID.
- It’s very easy to search against all subtypes (dependent table’s foreign key is also a primary key)