Right now I am planning to add a filter system to my site.
Examples:
(ID=apple, COLOR=red, TASTE=sweet, ORIGIN=US)
(ID=mango, COLOR=yellow, TASTE=sweet, ORIGIN=MEXICO)
(ID=banana, COLOR=yellow, TASTE=bitter-sweet, ORIGIN=US)
so now I am interested in doing the following:
SELECT ID FROM thisTable WHERE COLOR='yellow' AND TASTE='SWEET'
But my problem is I am doing this for multiple categories in my site, and the columns are NOT consistent. (like if the table is for handphones, then it will be BRAND, 3G-ENABLED, PRICE, COLOR, WAVELENGTH, etc)
how could I design a general schema that allows this?
Right now I am planning on doing:
table(ID, KEY, VALUE)
This allows arbitary number of columns, but for the query, I am using
SELECT ID FROM table WHERE (KEY=X1 AND VALUE=V1) AND (KEY=X2 AND VALUE=V2), .. which returns an empty set.
Can someone recommend a good solution to this? Note that the number of columns WILL change regularly
