I want to be able to allow a user to choose one option from three drop downs and then be able to return all matching products.
At the moment I have a form which collects the input and a single table in a database which lists each product along with the value 1 or 0 for each option.
productname, producturl, colour_red, colour_green, colour_yellow, colour_blue, colour_all, size_small, size_medium, size_large, size_all
I set it up like this as it is possible for a product to be in more than one colour and more than one size and I couldnt think of a way of building a table which allowed me to have none, one or more colours, for example, in the table.
I considered using productname, producturl, colour_1, colour_2, colour_3 etc and then inserting red, green etc into the fields and leaving unused ones empty.
Neither of these seem to be logical or make any sense and both involve way too may nested selects etc which will be a killer for performance but I can't think how else to do it - I have used OSC derivatives a lot in the past and they have a seperate table called product attributes where they bring together the attribute with the product but I can't figure out how to implement that.
Any pointers? How would you approach this if you were doing it?