Re: SQL for Modeling Generalization Hierarchies

robertbrown1971_at_yahoo.com (Robert Brown) wrote:
> > >
> > > This solution does not seem to scale since for each value of
> > > "discriminator" I need to perform a join with a different table. What
> > > if I need to retrieve 1,000 employees at once?
> >
> > Why would you ever need the attributes of more than one subtype? If
> > your query is about doctors, why would you need to join the table
> > about janitors into the query?
>
> Because I need to display users and their heterogenious attributes in
> the UI as one list (e.g. in response to a search).

Uh, that isn't really an answer.

> > If your query is about employees, why would you need to know any of the
> > subtype attributes?
>
> Same reason, our customer's HR department does not see the employees
> as separate entities. To them an employee is an employee and they want
> to see all the associated data while browsing employees. I guess you
> could show the generic attributes on the first pass and then require
> the end user to click to drill down and see the subtype attributes -
> but that's not what they want.

It sounds to me like the real solution is to fire your HR department and replace them with people who just do their jobs, rather than browsing around on the other employees information out of idle curiousity.

If the main goal is to provide custom interfaces, one per job-type, that support job-specific functions--and satisfying nosy Nellies is only secondary--then I think I would go with the main-table / multiple sub-table set up. When people want to see data mixed aggregations of job-types for no apparent reason, they can damn well wait the 5.2 seconds it will take to assemble it.

If the main or only goal is to satisfy nosy Nellies, I would either go with a auxilliary table of name-value pairs (and another auxilliary table with listing allowed or suggested attributes for each job type, or just do away with all those tables and make a "free text" clob column on the employee table (or call it XML rather than free text, if that type of things turns you on).

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB