Re: Modelling Disjoint Subtypes

On 22 Mar, 00:07, Joe Thurbon <use..._at_thurbon.com> wrote:
> I have (another) question about examples in Pascal's "Practical Issues
> in Database Management" book. This time it is about modelling disjoint
> entity 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

http://blogs.conchango.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx http://blogs.conchango.com/davidportas/archive/2007/02/19/More-on-Disjoint-Subtypes.aspx

There was also some follow up on the Third Manifesto mailing list.

--
David Portas