- Identifying the Goals
- Defining the Database Tables
- Adding Reviews
- Retrieving and Aggregating Reviews
- Promoting and Perfecting the System
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Defining the Database Tables
Once you’ve determined how the system will function, you can begin defining the database tables.
The reviews table might be defined like so:
CREATE TABLE `reviews` (
`review_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` mediumint(8) unsigned NOT NULL,
`product_type` enum('page','pdf') NOT NULL,
`rating` tinyint(1) unsigned NOT NULL,
`review` mediumtext NOT NULL,
`reviewer_name` varchar(60) NOT NULL,
`reviewer_email` varchar(60) NOT NULL,
`review_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `review_date` (`review_date`),
KEY `product` (`product_id`,`product_type`)
)
The exact definition of such a table will depend upon how the site’s products are represented and whether the reviewer needs to be registered or not. If the reviewer must be registered, then instead of storing the reviewer’s name and email address, a foreign key to the customers/users/reviewers (whatever you call it) table would go here. As for the products, in my Effortless E-Commerce with PHP and MySQL book, both example sites represent all the available products in two separate tables. In the first example, the products are articles (technically, pages) or PDFs. In the second example, the products are coffee and coffee-related goodies. In either case, to create a review system, I would store the product type—pages/pdfs or coffee/other—and the specific product ID in this table.
You may also consider adding a status column to the table that stores a tiny integer indicating that a review is pending approval (maybe represented by 0), approved (1), or removed as inappropriate (-1).
If you wanted to implement a “Is this review helpful?” feature, you’d also need a table created using:
CREATE TABLE `review_ratings` ( `review_rating_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `review_id` int(10) unsigned NOT NULL, `helpful` tinyint(1) unsigned NOT NULL, `date_entered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `review_id` (`review_id`), KEY `date_entered` (`date_entered`) )
The critical column—review_id—is the foreign key association to a specific review. The helpful column will be a tiny integer, literally a 1 (helpful) or a 0 (not). The date_entered column isn’t obligatory, but I generally like to timestamp almost every record in a database. There’s one thing you may want to add to this table: a representation of the person that indicated the review was or was not helpful (I’ll call this person a “review rater,” as opposed to the original reviewer). If the site requires registration for reviewing, commenting upon reviews, and so forth, the review_ratings table would store the review rater’s user ID value. If anyone can indicate the helpfulness of a review, then there are several solutions to choose from.
The first option is not to reflect the review rater at all, trusting that the same person won’t make rate a review multiple times (or, if they do, it’s not a big deal). The second option is to store the review rater’s IP address, with the acknowledgment that most people’s IP addresses change. This would mean that at some point in time, that person could rate the review again. And, in theory, some other person might be blocked from rating that review when they get assigned that IP address (although the odds of that happening are small).
A third, and probably the best, solution would be to store a cookie in the user’s browser with a unique ID. You could then store the ID in the review_ratings table and add a UNIQUE index on the combination of the ID and the review_id, thereby preventing the same person from rating the same review more than once (you’d want to add PHP code to the page reflecting the rating reviewer’s previous selection anyway).
If you wanted to add the ability to comment on reviews, a review_comments table would be defined similar to review_ratings, although with a text column for the comment instead of the helpful TINYINT. Again, how the review commenter is represented would need to be determined, using one of the same approaches just discussed.
The review_ratings table could also have a status column, as the reviews table might. If comments could be flagged as helpful or not, a comments_feedback table would be defined, just like reviews_feedback.