Database table with many rows - correct use

Question

This is more of a theoretical question and i think the best way would be to start from an example.

Let's say that I have a rating system. Every user can rate any item once and every item can be rated by any user only one time. I want to present some history of every user's ratings (all the items he rated, the rating, time etc.)

My way of solving this would be to have a table like this:

rating_id | user_id | item_id | rating | date

This table store every rating and some other needed data. In this case, if there are 10k users and 10k items the table would be a very long one and in addition to that I would be needing to use some JOINs to find out the name of the user and the name of the items he rated. So I'm guessing that would take a lot of time.

Am I on the right path or is there a better solution to my problem?

Answer 1

Hello TGM,

Basically there are many ways to model a database, and none of them can be declared as the best one generally. The optimal design for any database depends on the context in which it is to be used. Nevertheless, some principles seem to be agreed upon more than others. Basically the best way to learn about good database design is to examine the design used in popular applications. More about this below.

Preliminary example

Before you lose your interest, let me present one common model:

Image

SQL

CREATE TABLE items (
            item_id INTEGER NOT NULL,
            name VARCHAR NOT NULL,
            description VARCHAR NOT NULL,
            CONSTRAINT items_pk PRIMARY KEY (item_id)
);

CREATE TABLE users (
            user_id INTEGER NOT NULL,
            name VARCHAR NOT NULL,
            username VARCHAR NOT NULL,
            password VARCHAR NOT NULL,
            email VARCHAR NOT NULL,
            CONSTRAINT users_pk PRIMARY KEY (user_id)
);

CREATE TABLE ratings (
            item_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            rating_id INTEGER NOT NULL,
            timestamp TIMESTAMP NOT NULL,
            CONSTRAINT ratings_pk PRIMARY KEY (item_id, user_id)
);

ALTER TABLE ratings ADD CONSTRAINT items_ratings_fk
    FOREIGN KEY (item_id)
    REFERENCES items (item_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

ALTER TABLE ratings ADD CONSTRAINT users_ratings_fk
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

Remark

In this model I have used three tables. The important element is that in the ratings table, the two foreign keys are the primary keys of the table, as it is this pair of keys that ensures that a user can only rate an item once. You can of course add the ratings_id to the set of primary keys, although it will not have much effect upon the table constraint you mention in your question.

Enough about my design. Let’s look at what you really should do.

A better solution

Instead of relying blindly on others opinions, you should invest some time and find out for yourself which database designs are commonly used in rating systems.

Here is what you should do:

1. Install a Database Modelling Tool. Personally I prefer SQL Power Architect, which is an open source solution that enables you to reverse engineer and forward engineer database models to/from the most commonly used databases. The tool is also perfect for playing around with database models. The schema in the example above is made using SQL Power Architect.

   If you prefer other solutions, you can find a long list of alternative tools at databaseanswers.

2. If you haven’t got one already, install a server development package such as XAMPP or LAMP. Personally I prefer to use NginX and setting up database engines and programming languages myself.

3. Search the web for open source rating software, and install these on your development server. If you are too lazy to do that, take a look at these alternatives: Rating System, Open Rating, or PHP Stars.

4. Connect SQL Power Architect to the different databases, and use reverse engineer to examine and compare the different solutions.

If you follow these steps, you will soon get some ideas about how to setup/model a database for your own rating application.

Best of luck with your project.

Answer 2

The table will only have as many rows as the number of items each user rates. How many will that be? Personally, I wouldn't rate 10,000 items. I might rate 20 or 30 or 100. Old ratings might not be useful, so you might be able to delete some of them. (A three-year-old rating of shampoo isn't usually useful; shampoo formulas change all the time.)

If you need to show the user's name or the item's name, you'll need to join on the user table and the item table. Using id numbers always requires joins to get useful data.

Something along these lines should be fine to start with.

create table ratings (
  user_id integer not null, -- references users (user_id), not shown
  item_id integer not null, -- references items (item_id), not shown
  primary key (user_id, item_id),
  rating integer not null check (rating between 1 and 5) -- ?
  date_rated date not null default current_date
);

create index on ratings (date_rated);

Your next step would probably be partitioning.