Re: SQL for Modeling Generalization Hierarchies

From: Nick Landsberg <hukolau_at_NOSPAM.att.net>
Date: Wed, 02 Jun 2004 19:11:54 GMT
Message-ID: <_Dpvc.22999$_k3.570530@bgtnsc05-news.ops.worldnet.att.net>

ctcgag_at_hotmail.com wrote:

My 2 cents thrown in at the bottom:

> 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
>

When I was dealing with large database systems, we tried to make a distinction between "transaction" and "report" and tried to get the response time requirements ignored for "reports".

Anything which returns 1,000 rows is no longer a "transaction" in my book. As Xho said, "... they can damn well wait ..."

If this were a print job, it would print on roughly 20 pages, give or take. It would take whoever about 2 minutes to even give it a cursory scan. About the same amount of time to scroll down through a screenful of 1000 entries too, probably more.

Questions I used to ask when confronted with this:

- What is the business need for this information?
- Can you usually wait for it overnight?
- In a pinch (when overnight isn't good enough), would

10 minutes be OK?
- If you really need it in 1-2 seconds, how much is your hardware budget? (Because I'm going to need more/faster hardware to satisfy this request in just a second or two.)

What actually used to worry me about having such "reports" as on-line "transactions" was how they would impact the response times for the other users who were doing stuff which actually did require rapid response (e.g. pull up an individual's medical history in an emergency).

(I think that was actully 3 cents :)

NPL

-- 
"It is impossible to make anything foolproof
because fools are so ingenious"
  - A. Bloch

Received on Wed Jun 02 2004 - 14:11:54 CDT