Bruno A Posokhow wrote:
> Hi folks
> I am facing a structural dilemma for a database I would like to build and I am sure
> that your experience has the answer. I would like to build a table product. Each product
> can have many options. What is best:
> 1: Build a table option with all the options available and associate a product with
> one option with a table has_option ( productid , optionid )
> 2: have one character field in the table product wich would describe all options for
> that product through some mapping.
> I am pretty much concerned with query speed that is why I figured out the second
> solution that needs only one table product lookup to determine all products with a
> specified option. But I think the first solution is more normal.
> What do you think?
> Bruno Posokhow
If there are less than 64 'options', then you can use SET type instead of a character
field. Searching on that will be faster.
You also can get multiple SET columns in your 'product' table.
But SET columns are only good, if the 'options' woun't change over time.
So if there are frequently coming new 'options', then you should use your first structure
with two tables linked by a third one.
PS: Sorry for the late answer, I was on vacation.