Supertypes and Subtypes

Subtypes are also referred to as specialisations, subclasses, or "special cases". In a car rental organisation, imagine that 'city agency', 'airport branch' and 'hotel desk' are all subtypes of supertype 'branch':

The 3 subtypes share common characteristics of the supertype, such as the fact that they have an address and a branch manager, but they also have characteristics of their own: an airport branch has a airport terminal where it is located, but non-airport branches don't.

Supertypes may be, in turn, subtypes of a supertype at a higher level. This way, in theory, you can build entire tree structures called taxonomies:

CAUTION: In USoft Definer, it is not necessarily practical to build extensive subtype tree structures, even if (conceptually) such subtype trees can be distinguished.

In theory, a subtype can have multiple supertypes. However, USoft Definer supports a maximum of 1 supertype for each subtype (as in the tree structure shown).

You can model subtypes simply by defining extra columns for the extra characteristics of the subtype. These subtype columns must be defined with Mandatory = No since the supertypes are stored in the same table but do not have the subtype characteristics:

In this setup, you can add a BRANCH_TYPE column that you allow (through allowed values at domain level) to be set to either 'CITY_AGENCY', 'AIRPORT_BRANCH', or 'HOTEL_DESK'. You can then write constraints such as the following constraint, which enforces that users supply values for Airport Code and Terminal Number for each airport branch:

SELECT         ''

FROM           branch

WHERE          branch_type = 'AIRPORT_BRANCH'

AND

(

               airport_code IS NULL

     OR        terminal_nr  IS NULL

)

You can model subtypes by creating relationships where subtype information is in the child table. The primary key of the relationship is the primary key of the supertype table. The foreign key of the relationship is the primary key of the subtype table. If the concept is a "true" subtype, then each Branch is allowed to be an Airport Branch only once, so the relationship must have the Maximum Children = 1 setting. This time, the supertype characteristics Airport Code and Terminal Number are allowed to be Mandatory columns:

Airport Branches are not a particularly happy example of this modelling option - the example is given for easy comparison with the other 2 options. Modelling a subtype using a relationship can be attractive especially if the information in the subtype columns is added at a later stage in a business process, as when the supertype is RENTAL_REQUEST, and the subtype is RENTAL_CONTRACT: the process starts with a rental request, and at some later point, this request "turns into" a contract, at which time extra information must be added.

To model a subtype using a relationship is to misrepresent a subtype for a child entity, but an advantage if you choose this option consistently is that you need to deal only with a single data structure (relationships) and not with 2 (relationships and subtypes). This can be practical if your application is subject to complex data management operations.

You can model a subtype by setting a subtype table's Supertype Table attribute. In this case, USoft Definer will implement (and subsequently enforce) that both tables have identical primary keys: