[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