I'm modeling a system which help keep track of equipment's failures. There are two type of equipments to be managed, one would have all properties of the other and some extra fields (E.g. TypeA(A,B,C), TypeB(A,B,C,D,E)). However, failures of any equipments should be stored in one Failure table. Let say A is the key of TypeA and TypeB, then A should appear in Failure table. I also wish to have Cascade update in the relation between TypeA and Failure, TypeB and failure. Could any of you show me a way to resovle the problem.
The easiest way would be to have one Equipment table for both types, and just let those 5 columns be NULL for one of the subtypes. You can use a discriminator column (equipment_type) and a check constraint to ensure that the appropriate columns are used for each subtype.