[thelist] SQL : performance - separate table vs. (datatype) SET
Hassan Schroeder
hassan at webtuitive.com
Tue Nov 12 11:07:00 CST 2002
Let's say I have a DB with recipes, and I need to be able to find
items with certain ingredients.
So it seems like I could have
(1) a separate table of ingredients, with the recipe id as a key
and pseudo-boolean values for each ingredient column -- like
milk ENUM("false","true") -- and search with something like
WHERE milk = "true" AND eggs = "true"
(2) an ingredients column using the SET datatype to include all
ingredients -- ingredients SET("milk","butter","eggs") -- and
search them via something like
WHERE ingredients LIKE "%milk%eggs%"
Anyone have any thoughts on why one or the other would be preferable?
I'm in the process of learning how much I don't know about SQL :-)
I am aware of the 64-member limitation to SET and that's not an
issue in this instance.
Specifics: MySQL on Linux (for deployment)
TIA!
--
Hassan Schroeder ----------------------------- hassan at webtuitive.com
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
dream. code.
More information about the thelist
mailing list