Re: SQL for Modelling Generalization Hierarchy

Robert Brown wrote:
[snip]
> The only solution I found for this is a generalization hiearchy where
> you have the employee table with all generic attributes and then you
> add tables for each new employee subtype as necessary. The subtype
> tables share the primary key of the employee table. The employee table
> has a "discriminator" field that allows you to figure out which
> subtype table to load for a particular entity.

This "discriminator" field would also prevent an employee from having two roles at once. It would not be possible to have an employee being a doctor and a manager at the same time. This may sometimes be want you want. Most of the time it won't. Even if it *is* what you want you can use constraints. You don't need the field. The "discriminator" field approach suggests to me that you look at the problem from a strictly navigational angle. Try other approaches. For instance try looking at it from an existential point of view.

[snip]
> Is there a better approach to modelling these kind of heterogeneous
> entities that does not involve creating a table for each new employee
> type or having sparce tables (mostly filled with NULLS)

What don't you like about tables for each type?

> I guess another approach would be to use name/value pairs but that
> would make reporting really ugly.
>
> Seems like a very common problem. Any ideas? Is this a fundamental
> limitation of SQL?

Fundamental? Dunno. I've seen it many times, not only in SQL. I suspect it is inherent to administration. Received on Thu May 27 2004 - 20:22:42 CEST