Re: Performance gain by avoiding lookup table join?
From: Frank <fvanbortel_at_netscape.net>
Date: Fri, 14 Mar 2003 23:35:40 +0100
Message-ID: <3E72593C.30707_at_netscape.net>
>
>
> 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.
Date: Fri, 14 Mar 2003 23:35:40 +0100
Message-ID: <3E72593C.30707_at_netscape.net>
Robert Fazio wrote:
> Frank <fvanbortel_at_netscape.net> wrote in
> news:3E7222E4.3080302_at_netscape.net:
>
>
>>Robert Fazio wrote: >> >>>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.
I meant:
"The denormalized schema proposed (in Designer, that would be
the Discriminator Column approach) would make it less flexible
to maintain." That is less flexible not just as flexible.
And yes - but Designer has a (default) option: Create Surrogate
Primary Keys for all Tables.
My guess is it was switched on. There would be an accompanying
sequence, too.
But you are correct: the code could have been the one pk column.
And a description could have been added without problems.
-- Regards, Frank van BortelReceived on Fri Mar 14 2003 - 23:35:40 CET