Re: Related Tables: Performance vs Elegance

miguel 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.
>
> Example: Building XPTO, 1 - 3 - 5 (street numbers 1, 3 and 5)
>
> Whe have a second table (Street_Number) wich holds one entry for each
> street number. In the above example the table would include 3 entries:
>
> XPTO 1
> XPTO 3
> XPTO 5
>
> 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.
>
> Is this the correct way of doing it ?
>
> 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...
>
> Thank you in advance for any comments or tips,
>
> Miguel

Another (I believe the faster) way is

create table Buildings (

			Buiding not null ...,
			Street1 not null ...,
			Street2 null,
			...
			StreetN null);

You won't lose space, but the disadvantage is that you have to know N - the maximum number of streets per building. (or you can add more Street... fields as you need them)

That's theoretically very bad solution, so don't use such denormalyzed schema
until you are really concerned in perfomance/storage problems.

-- 
Regards. Alexey Neckrasow. Leaves Inc. Russia.
e-mail: nec_at_leaves.spb.su