A Supertype-Subtype relationship is a logical concept, and can't be implemented in the physical model. When transforming it, you can either Roll Up to get a single table, or Roll Down to move the parent information into two child tables in the physical model. You can also use the Supertype-Subtype Identity transformation, which will produce three tables with an identifying relationship from the parent table to its two child tables.
The main reasons why you apply a Supertype-Subtype Identity transform to create an identifying relationship between a supertype entity and its subtype entities include:
- You want to simplify a model.
- The subtype relationship is not appropriate for the current design layer.
- You want to improve query performance.