Modelling Disjoint Subtypes

The example given in the book (section 6.2.2 for those who have it) is of an "employee" entity with a subtype commissioned employee. The relational representation for this example is

Employee: EMP# Name HireDate Salary

            ====---------------------------
            1      Smith  1/1/2001  16150
            2      Jones  1/1/2002  14350


Commissioned_Employee: EMP#   Commission
                        ====-------------
                        1         5000

There is also the natural generalisation to allowing employees to be salary-only, commission-only, or both.

Employee:     EMP# Name HireDate


               ====


Commissioned: EMP#  Commission


               ====


Salaried:     EMP#  Salary


               ====

An exercise left to the reader (why is it always the one I'm interested in that's left to the reader!?) is to model the case whereby employees can be either salaried, commissioned, but _not_ both. That is, each of the subtypes is disjoint.

I can think of perhaps three ways to do this, but none seem to be as elegant as the above examples, and so I'm wondering if I'm missing something simple.

The first approach I can think of uses some sort of type-discriminator, in the employee table. This seems fraught with difficulties: how do you select the table to join on based on the type discriminator other than using multiple queries? How do you prevent nonsense data appearing, for example, in the 'commissiones' table for an employee whos type discriminator is is 'salaries'. So I'm guessing that a type discriminator does not work.

The second approach is to have only one sub-type, but have two columns "Type", "Amount" whereby type gives a clue as to how to interpret the ammount (i.e. is it salary or commission). This seems problematic, since , in some sense, the domain of commissions is different to the domain of salaries (at least, according to my understanding of chapter 1 of "Practical Issues..."

The other approach requires some sort of check constraint that simulates a kind of 'distributed key' (c.f. Darwen's 'Final Null In Th Coffin'). I can't really work out how this would be done relationally.

So I'm stuck. Any hints or clues?

Cheers,
Joe Received on Thu Mar 22 2007 - 01:07:29 CET