Re: Related Tables: Performance vs Elegance

In article <01bc3457$6acf4960$c65988c1_at_jmiguel.cet.pt>, miguel <jmiguel_at_cet.pt> wrote:
>We have a table that holds information on Buildings (quite a large table
>and with lots of fields). Normally each Building has one single street
>number associated with it, but in some situations it can have more than
>one.
>
>Whe have a second table (Street_Number) wich holds one entry for each
>street number.
>
>OUR QUESTION:
>
>Since say, 90% of the Buildings only have one Street number, would it be
>more reasonable to include an extra field in the table Buildings, to hold
>the first street number, and another field to indicate if the number is/is
>not unique. If the street number is not unique, then we would search the
>Street_Number table to obtain the remaining values. This way the
>Street_Number table would include entries only for street numbers of 10% of
>the buildings.

Databases were built to handle these situations. Don't try to "make it better".

The way you describe below should be just fine. I would bet that after spending many hours trying to set up the secondary-address idea as you describe above, it won't save your server much time. In fact, you may even lose some server time in trying to decide when to go look for additional addresses. And in addition, you will have to write special code everywhere to handle the convoluted street-address design.

>The alternative would be to store all the street numbers for all buildings
>in the Street_Number table. When querying building information (including
>street number), searching the Street_Number table is therefore always
>mandatory...

Steve Cosner Received on Fri Mar 21 1997 - 00:00:00 CST