I'm new to web development and database design, and I'm kind of stumped as how to best accomplish a simple review system for items.
In the current database schema I have a table, call it tbl_item, that has columns for different properties of items. I want users to be able to review items and associate each review in the tbl_reviews to a particular item.
Of course I have a foreign key set up referencing an id column in tbl_item but I do not know where to go from here. Basically my question is: What should calculate the review average?
Should the application make a SQL call every time a review score is requested for a particular item, where the DB would have to then search through all the tbl_reviews rows to find those with a particular item_id?
(That seems wrong.) Should the DB get involved and have some type of calculated field or view or stored procedure that does the same?
Should I have a new column in tbl_item that has the average score in it and is updated whenever any new review corresponding to a particular item is CRUD'ded?
If it matters, I'm using Yii (PHP) and MySQL.
Basically you're asking about efficiency and math.
Here's what I would do:
Your DB is relational. Good, you got that. Each review has a numerical value? Like 1 - 10? Say it does for this example.
I would say that upon each review, the review itself is set in the DB as well as a queue in an action table. Something that has the item id and a type of action. In this case review. You then have a cron running in the background every minute or so checking that action queue and in the event of a new review or set of reviews, you run an algorithm for each applicable item that collects all of the data available on the review and returns an educated number based on the standard deviation of the collective data.
This way the math is not run in realtime by the user or when a review is sent. For all we know you have tons of items and tons of reviews, so real time would be bad if your intelligence script is heavy.
As for standard deviation, I check a large variety of things for anti-spam. I store all userdata, IP, datetime, and anything else I can to make sure it's not just one guy logging in with different accounts reviewing his own things with a 10 rating each time. Can't fall for that. Plus, if you get 100 10 reviews that look legit and 1 review with a score of 1 you can discount it as a hater and just ignore it in the results.
You have to understand your request is enormous, so code snippets are out of the question here. What I just explained was like 4 months of work for a huge client and a serious anti-spam calculator.
good luck though