List:General Discussion« Previous MessageNext Message »
From:Adam Clauss Date:September 21 2003 8:39pm
Subject:Design Suggestion
View as plain text  
I have several fields in which I will be strong text.  Various categories,
and for each category, its related subcategories.  Each subcategory then
contains various items.

My question is, for performance, would it be better to assign each
category/subcategory pair a unique ID number and then anytime I need to
lookup something in a subcategory, look up the ID number and search off of

Or, would it be about the same to just search the entire list of items on
the text of the category subcategory?


First way:
Two tables:

Categories - Category (text), Subcategory (text), CatID (unique integer)
Items - CatID (integer relating to id in categories table), .... Other item

I would do something like:
SELECT CatID FROM Categories WHERE Category="desired category" AND
Subcategory="desired subcategory"
Then for example to retrieve all items in that subcategory:
SELECT * FROM Items WHERE CatID=(the id found in previous select)

One table:
Items - Category (text), Subcategory (text), ... Other item info

And to find all items in a subcategory, do:
SELECT * FROM Items WHERE Category="desired category" AND
Subcategory="desired subcategory"

It seems to me the second way would be slower, because it has to do many
more string tests.  But does it make a difference?  Is testing integer
equality actually faster than string equality?

Adam Clauss

Design SuggestionAdam Clauss21 Sep