[thelist] SQL : performance - separate table vs. (datatype) SET

Hassan Schroeder hassan at webtuitive.com
Tue Nov 12 21:40:00 CST 2002


r937 at interlog.com wrote:

> sets aren't bad per se, but extremely limited in application

OK, that's what I'm looking for -- because?

> also, one must strive not only for efficiency but also flexibility

>>(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"
>
> this will require changing your table definition whenever
> new ingredients are added that you hadn't thought of

Uh, yeah, I figured that -- ALTER TABLE ADD COLUMN blah will do
that, but is trying to match multiple times in one column more
efficient than matching YES/NO against multiple columns?

>>(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%"
>
> not sure that's how you search a set

Seems to be, or at least it seems to work that way, which is of
course not to say it's "correct", portable, or most effective :-)
My testing hasn't returned any unexpected results so far, let's
just say.

> i've not used mysql sets myself, but i understand them to be a form
> of domain, in that the allowable values are predefined, but you can still
> fool around with their numbers...

Well, the numeric interface to adding/deleting aside -- which I
actually find kind of appealing -- do you see a specific problem
to using them? As far as I can tell, adding a value by redefining
the column of an existing table is OK, and it isn't something that
would be done frequently in any case. I'm not really dealing with
recipes here :-)

So, to recap, the three candidates are

1) separate table: one index column, one column of attributes,
                                        N possible values
2) separate table: one index column, N columns, one attribute each
                                        pseudo-boolean values
3) combined table: one column for a defined set of N attributes

You still recommend Door Number 1?

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