Opinion Requested: Multi-Table Design

I have about a 35000 record table. There are about 14 entries in this table that relate to "sections". Each of these sections can have up to 20 values. This lends itself to a design like:

big_table [one-to-one] big_table_has_sections [many-to-many] sections

or, more concretely:

BuildingConstructionType
 relates to
ConstructionHasTypes
 relates to
Types

Where BuildingConstructionType is one of 14 fields in the 35000 record "big" table, ConstructionHasTypes is the one-to-one intermediary relation that relates many-to-many with Types (the sections).

Unfortunately, with 35000 records, this big_table_has_sections seems like it might bloat. Is this a good solution?In order to facilitate what I think you are trying to accomplish, I don't know of any better way. Presumably the cross reference table is smallish in nature (i.e. composed of 2-3 key fields) and therefore should be indexed for quicked retrieval.

The obvious difficulty lies in how to retrieve the data. Your concerns for bloat are well founded as you will be using joins to return the appropriate dataset. If you user the query analyzer you will see that even simple joins between 2 tables using indexed keys are typically the most expensive (time and resource) operation in getting the data back. The reason for this is that sql does a table scan each and every time!

For this reason when dealing with tables of any sgnificant size I always use a stored procedure that employs one or more temporary tables which are populated using the appropriate where clause(s). Then I perfrom any joins using the temporary tables previously generated. That way the table scan will only go through the 100 or whatever number of BuildingConstructionType records in the temporary table instead of all 35000.

Granted, doing this takes a little more time and effort but my clients have tables containing 2.5 -3 million records with joins up the kazoo and I can still kick out result set in under a second.

Hope this is helpful and feel free to ask for any additional assistance if required.

Cheers

Gary|||Thanks... that's what I feared. It would be great if I could represent a vector effectively, but that doesn't appear to be consistent with the declarative nature of SQL.