|Inheritance Design Notes for Physical Databases|
1. A CUSTOMER can be either a COMMERCIAL CUSTOMER or a PERSONAL CUSTOMER, which both inherit characteristics from CUSTOMER.
2. A PERSONAL CUSTOMER is also a PERSON, and inherits characteristics from PERSON.
3. A COMMERCIAL CUSTOMER has a CONTACT, who is also a PERSON, and therefore inherits characteristics from both CUSTOMER and PERSON.
NOTE that the Physical Data Model can have be generated in different ways to reflect the ways in which these logical relationships can be implemented in a Relational Database.
There are four alternatives :- 1. One Consolidated Table. 2. One SubType Product Table 3. One SuperType Table, Many SubTypes each in its own Table, each containing all the shared Attributes. 4. One SuperType Table with an 'extensible' table defined to identify the Attributes specific to each Product Type. 5. A "products" table, an "attributes" table, and a "specs" table. The products table is like in 3. The attributes table simply lists all available attributes and the Product_Atttributes table maps an attribute to a product, and provides an attribute value. In the Logical Data Model we have a Circle symbol that shows an Inheritance Relationship where we have a Parent Customer with 3 Children Customers. Each of the Children inherits all the charactistics of the Parent and has some additional charactistics of its own.
Discussion Nr.3 Another option occurred to me recently (March, 2017) which consists of two steps :- Step 1. Copy all Attributes from the 'Children' to the 'Parent' Step 2. Create SQL Views for each 'Child' for only the Attributes for each View. This gives us flexibility and power.