Re: Performance gain by avoiding lookup table join?
From: Robert Fazio <dbabob_at_hotmail.nospam.com>
Date: Fri, 14 Mar 2003 21:59:50 GMT
Message-ID: <Xns933EACE7E3861dbabobhotmailcom_at_204.127.199.17>
> Hmmm, don't quite agree.
>
> It's not really just an attribute. I would say this is a
> typical implementation of a Designer Super/Sub entity construct.
>
> The denormalized schema proposed (in Designer, that would be
> the Discriminator Column approach) would not make it less flexible
> to maintain.
> To give one example: you can now use the types table to dynamically
> fill a drop-down box in the front end GUI. Adding an extra type
> is easy, because in a (small) table, and would mean no coding in
> the front end.
Date: Fri, 14 Mar 2003 21:59:50 GMT
Message-ID: <Xns933EACE7E3861dbabobhotmailcom_at_204.127.199.17>
Frank <fvanbortel_at_netscape.net> wrote in news:3E7222E4.3080302_at_netscape.net:
>> charlie3101_at_hotmail.com (Charlie Edwards) wrote in >> news:217ac5a8.0302270403.6a1f4b61_at_posting.google.com: >> >> >>>Consider the following set-up (8.1.7): >>> >>> >>> -------------- >>>|PERSONS | 3 million rows >>>| pers_id (PK) | >>>| Forename_1 | >>>| Surname | >>>| etc | >>> -------------- >>> | >>> | >>> /|\ 5 million >>> -------------- rows ---------------- >>>|PERS_ADDR_ROLE| |ADDR_ROLE_TYPES | approx 5 rows >>>| pers_id | | addr_type_id | e.g. 1 'HOME' >>>| addr_id |\ | addr_code | 2 'WORK' >>>| addr_type_id |->-------| | 3 'POSTAL' >>>| start_date |/ | | >>>| end_date | | | >>> -------------- ---------------- >>> \|/ >>> | >>> | >>> -------------- >>>|ADDRESSES | 4 million rows >>>| addr_id | >>>| Addr_Line_1 | >>>| Addr_Line_2 | >>>| etc | >>> -------------- >>>
> Hmmm, don't quite agree.
>
> It's not really just an attribute. I would say this is a
> typical implementation of a Designer Super/Sub entity construct.
>
> The denormalized schema proposed (in Designer, that would be
> the Discriminator Column approach) would not make it less flexible
> to maintain.
> To give one example: you can now use the types table to dynamically
> fill a drop-down box in the front end GUI. Adding an extra type
> is easy, because in a (small) table, and would mean no coding in
> the front end.
In that event, would it not be more accurate then to just create the table, and make the primary key the addr_code? My primary point was that there is no need for the numeric addr_type_id field. My feeling is that would result in the best of both worlds. No one writes queries unless they have to based upon a generated field except when that field needs to be generated as in possibly the addr_id or the pers_id (both are quite valid), but in the case of the addr_role_types there is a perfectly valid (and small) natural key.
And in that case, you get the best of both worlds. Received on Fri Mar 14 2003 - 22:59:50 CET