Consumer Rankings

On-line booksellers have changed the way I decide which books to buy. Not only have they made it possible for me to get most books within two to three days, but they have also given me a means of comparison shopping previously unavailable to me. If I am thinking of buying a book, I immediately look to see what others have written about it. In most cases, these evaluations clinch the deal, convincing me to buy a certain book instead of its competition.

I have been thinking more about such systems since my recent move to Modi'in, a new Israeli city halfway between Jerusalem and Tel Aviv. Just before I moved to Modi'in, I was asked to take over a small mailing list for residents of Modi'in. The list gives people a chance to share local announcements and ideas related to life in Modi'in.

Soon after becoming the list administrator, I realized that subscribers often asked for recommendations, from doctors to lawn services to after-school activities. Normally, an e-mail list administrator who sees such constant repetitions will prepare a FAQ, a list of common questions and their answers. But recommendations are extremely subjective, and one person's favorite barber may be someone else's nightmare.

This month, we will look at a set of CGI programs I wrote to allow list subscribers to enter and rank their favorite products and services in town. Because my web space provider does not offer mod_perl, I had to use the CGI standard for writing my programs.

This “ranking” system, as I call it, consists of three CGI programs written in Perl, which use a relational database for back-end storage. (These listings are not printed here due to space considerations, but can be downloaded from the LJ FTP site. See Resources.) I have used MySQL, but there is no reason another relational database, such as PostgreSQL or Oracle, could not be substituted for it. Some of the SQL syntax might have to be changed in order to fit another database server, but the majority should remain the same.

While the ranking system presented this month is not as sophisticated as the one used by Amazon.com nor as flexible as that used by Epinions.com, it does serve a simple purpose. Moreover, it demonstrates how to produce a simple ranking system, which could easily be extended to produce a rough version of Epinions.com.

As always, the first step in creating a database/web application is to consider how we want to store the information, and then to create the tables in our relational database. In this particular case, we will keep things simple, dividing ranked items into categories, but without any hierarchy representing the categories. We will thus be able to keep all restaurants in the same category, but without any distinction between Italian restaurants and French restaurants. Alternatively, we can create two separate categories for Italian and French restaurants, but then the system will see them as unrelated as barbers and vacuum-cleaner salesmen.

We will also associate a name and e-mail address with each ranking. It might be more elegant to place user names in a separate table and refer to them with a numeric key. However, we are less interested in tracking users than in making it possible to find useful consumer information.

Given all this, I decided to implement the ranking system with three tables: RankCategories, RankItems and Rankings. RankCategories, as its name implies, contains the categories into which the items are sorted, and can be defined as follows:

CREATE TABLE RankCategories (
category_id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(25) NOT NULL,
category_description TEXT NULL,
UNIQUE(category_name)
);

Notice how each category will have a unique category_id, allowing us to refer to it by number alone. By using the AUTO_INCREMENT feature, MySQL can automatically set this number for us, ensuring that it is unique. For a small web site, a MEDIUMINT is large enough, ranging from 0 to 16,777,215. A large site might eventually have more than 17 million rankings, in which case a larger size, such as INT or BIGINT, might be a good idea.

We also want to ensure that no two categories can have the same name, and thus add a unique constraint to the category_name column as well. Each category can then have some descriptive text associated with it, which is placed in category_description.

The items to be ranked are placed in a similar table, RankItems:

CREATE TABLE RankItems (
item_id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_id MEDIUMINT UNSIGNED NOT NULL,
item_name VARCHAR(25) NOT NULL,
item_description TEXT NULL,
UNIQUE(item_name)
);

Once again, each item has a unique value in item_id, and the item name is guaranteed to be unique, thanks to the UNIQUE constraint. However, RankItems adds a category_id column, identifying the category in which this item sits.

RankItems defines the items available to be ranked, but does not store the grades. That role is assigned to the Rankings table, defined as follows:

CREATE TABLE Rankings (
ranking_id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
item_id MEDIUMINT UNSIGNED NOT NULL,
ranker_name VARCHAR(30) NOT NULL,
email VARCHAR(40) NOT NULL,
entry_datetime TIMESTAMP(8) NOT NULL,
comments TEXT NULL,
rank TINYINT UNSIGNED NOT NULL,
UNIQUE(item_id, email)
);

Like the other tables, Rankings gives each ranking a unique primary key, ranking_id. None of our applications use ranking_id, and it is possible that its inclusion in the table definition wastes some space on disk and in memory. However, such a primary key will make it easier to refer to items in Rankings if and when we build additional applications for the ranking system.

Rankings then contains an item_id column, which refers back to the primary key of the same name in RankItems. Because each item is in a single category and RankItems contains a category_id column, there is no need to name the category in Rankings as well.

We ask each ranking user to provide his or her full name and e-mail address. This information is displayed next to a ranking, in order to give it a slightly more human touch. In addition, some people's opinions will carry more weight than others, especially in a small community of users, so it is worthwhile to identify opinions by name.

The ranking consists of a numeric rank between 0 and 10, stored in a TINYINT. It is accompanied by optional (NULL) comments, stored in a TEXT column, where the user can elaborate on his or her ideas.

To ensure each user can rank each product only once, we have MySQL require the combination of item ID and e-mail address to be unique in the Rankings table. By combining the two in this way, the database itself will reject any attempt to enter two rows in which the combination of e-mail address and item_id are identical.