Which schema design for best SELECT performance?

Hi,

Any help on clarifying the following would be much appreciated.

Given an unlimited number of categories with each category containing
the data {ID, NAME, DETAILS} which of the following schemas is the
best performing in terms of SQL SELECT access given that each category
contains an average of 100 records. The really important factor is the
speed of the SELECT clause - the performance of the UPDATE, INSERT,
DELETE clauses as well as storage size efficiency are not important.

**SCHEMA A**
Each category uses it's own table such that SQL access may be
"SELECT ID, NAME, DETAILS FROM Category1"
"SELECT ID, NAME, DETAILS FROM Category2"
"SELECT ID, NAME, DETAILS FROM Category3"
etc..

**SCHEMA B**
A single table is used with an additional CATEGORY field
"SELECT ID, NAME, DETAILS FROM TABLE WHERE CATEGORY = 1"
"SELECT ID, NAME, DETAILS FROM TABLE WHERE CATEGORY = 2"
"SELECT ID, NAME, DETAILS FROM TABLE WHERE CATEGORY = 3"
etc...

Regards,

Steve.

If there is never any need to query across more than 1 category, then a
separate table for each category will be more efficient for simple selects.
But a single table for all categories provides much more flexibility for
other queries of the data as users' requirements change and grow.
--
Ted

It would be a much better design to have 1 categories table. I don't think
the performance issue should be too much of a problem with a simple SELECT
query, but having multiple category tables could cause all sorts of other
difficulties in the future.

Andrew


"TedMi" wrote:

If there is never any need to query across more than 1 category, then a
separate table for each category will be more efficient for simple selects.
But a single table for all categories provides much more flexibility for
other queries of the data as users' requirements change and grow.
--
Ted

On Sat, 10 Dec 2005 02:38:03 -0800, andrew
wrotd:

It would be a much better design to have 1 categories table. I don't think
the performance issue should be too much of a problem with a simple SELECT
query, but having multiple category tables could cause all sorts of other
difficulties in the future.

Andrew

It would be a much better design to have 1 categories table
Agreed as far as design goes, but given that the *only* important
operation is a simple SELECT (with no joins) then if CATEGORY 1
contains 100 rows is it quicker to select all rows from the CATEGORY_1
table or select the 100 rows from amongst 10,000 rows in the CATEGORY
table (ie. "..WHERE CATEGORY=1")?

Steve.

I doubt you could even measure the difference, and certainly would not
perceive it. Since requirements and uses of systems keeps forever changing
and evolving, I would strongly recommend the more flexible approach of a
single table.
--
Ted