Database design issue - multiple scores in different categories

Question

Say I have a database of clothing products. In addition to many other fields, the database needs to store ratings (marks out of 10) on different categories for each product.

For example, one category could be 'occasions'. The occasions could be as follows:

Wear to the office Wear to a wedding Wear on a date ... Wear to a funeral

Each product needs to be assigned a score out of 10 for each of these. So one product could have:

Office: 5 Wedding: 7 Date: 10 Funeral: 0

Which would indicate the item is something cheerful, not too understated, not too formal perhaps.

There are a number of categories like this, and these will be used as part of a search algorithm so speed could potentially be an issue. Also I don't want my product table to get huge. This means I'm uneasy about storing this in the product table as each response having it's own column.

Possibly store this way except in a different table with a join? Just looking for a reasonably flexible and elegant solution.

If I haven't been clear please let me know.

P.S Am using MySQL if that makes a difference...

EDIT: I don't think having a category table and a join table with product_id, category_id and score would work very well. You would either have to name each score column (e.g. wedding_score etc) in the join as aliases, or get multiple score columns returned (one for each category).

Answer 1

You're on the right track. Have a table for "category", with an intermediate table with two columns, one the PK for products, the other the PK for the product; plus a third for the score. Pretty standard pattern. Then typically the intermediate table will have a composite PK (e.g. productid + categoryid) and another non-unique index on categoryid.

Answer 2

Well, A relational database is optimised for storing relationships so a table for storing item descriptions, another for Scoring Categories (including Score Description) and a joining table (item to score) seems the perfect answer.

What are the drawbacks? Well, each item will have several score records, thats true. It also wouldn't be that readible in a query browser. But with the correct keys and indexes it would be fast enough to use the way you need it.

Answer 3

EDIT: I don't think having a category table and a join table with product_id, category_id and score would work very well. You would either have to name each score column (e.g. wedding_score etc) in the join as aliases, or get multiple score columns returned (one for each category).

I think this IS the way to go, because it lets you treat the different scores identically. Most of your code won't need to be adjusted when a score type gets added, renamed, removed, etc.

If you're worried about complicated joins for aggregating this info, maybe a view or two would help.