Creating a Rating System

Users who like this also liked...

[Musicarl]

Hello.

We have a MySQL database of over 5000 Widgets with great information about each one. I'd like to create a system where a user can rate any Widget. We could then harvest this data and create links like this:
"Users who like this Widget also liked Widgets #7, #812 and #1233 and hated Widgets #67, #743 and #2013."

I was thinking we could create a 1-5 rating system, and assign one point for a score of 4, two points for a score of 5, and negative points for scores of 1 and 2. If a user gives a score of 5 to both Widget #1 and #2, that would assign 2 points to that relationship. This probably means creating a new table and adding a new record every time a relationship is established, then updating the record when more points are added to the relationship.

All this and I don't want to put too much strain on the database. If anyone can help the left side of my brain catch up with the right, I'm open to any suggestions on how to make this work.

[physics]

So you're looking for a table design? If you're worried about performance you can always store the data in the appropriate tables and then run a script nightly to calculate everything and dump it into a table that just has a list of all products with their related products.

[Musicarl]

Thanks Physics. A script is probably a good solution, since it doesn't need to be updated instantly.