Ratings & Reviews - 1 or 2 tables?

mhuggins · #1 (**permalink**) 10-09-07, 20:48

In my current project, I'll be allowing users to submit ratings (TINYINT 1-5 stars) and reviews (TEXT comments) on products in the database. Users can submit ratings without reviews, but they can't submit a review without a rating. I can't decide if I should combine the reviews and ratings into a single table, or if I should keep them separate.

Here's how the tables are currently laid out:

Code:

CREATE TABLE `ratings` (
	`user` INT(11) NOT NULL,
	`item` INT(11) NOT NULL,
	`rating` TINYINT NOT NULL,
	`submit_dtm` DATETIME NOT NULL,
	PRIMARY KEY (`user`, `item`)
);

CREATE TABLE `reviews` (
	`user` INT(11) NOT NULL,
	`item` INT(11) NOT NULL,
	`review` TEXT NOT NULL,
	`submit_dtm` DATETIME NOT NULL,
	PRIMARY KEY (`user`, `item`)
);

Here is what I am considering changing to:

Code:

CREATE TABLE `ratings` (
	`user` INT(11) NOT NULL,
	`item` INT(11) NOT NULL,
	`rating` TINYINT NOT NULL,
	`review` TEXT,
	`submit_dtm` DATETIME NOT NULL,
	PRIMARY KEY (`user`, `item`)
);

I'm trying to weigh the benefits and drawbacks in combining the tables, such as:

if combining the two tables into one, will having a nullable TEXT column be awful considering there will be many columns with a null value here?
if leaving the two tables as they already are, how hard will it be to join the data (returning NULL for ratings or review if the primary key's value only exists in one of the two tables)?
any other speed/space considerations I may not be aware of...

Thanks for the help, and sorry for my naivety when it comes to technical info about how a database runs and organizes data...that's my biggest fault when it comes to db design.

r937 · #2 (**permalink**) 10-09-07, 21:06

Quote:

Originally Posted by mhuggins

if combining the two tables into one, will having a nullable TEXT column be awful considering there will be many columns with a null value here?

no, it will not be awful, and those nulls won't take up any space, either

Quote:

Originally Posted by mhuggins

if leaving the two tables as they already are, how hard will it be to join the data (returning NULL for ratings or review if the primary key's value only exists in one of the two tables)?

it will be as easy as a LEFT OUTER JOIN

mhuggins · #3 (**permalink**) 10-09-07, 21:18

So I haven't tested this, and I'm just about to, but basically let's say I have my two tables with data like this:

ratings table

Code:

user | item | rating | submit_dtm
-----+------+--------+------------
1    | 1    | 5      | 0000-00-00
1    | 2    | 4      | 0000-00-00
1    | 3    | 4      | 0000-00-00
2    | 1    | 5      | 0000-00-00
2    | 3    | 1      | 0000-00-00
3    | 1    | 5      | 0000-00-00
3    | 2    | 3      | 0000-00-00

reviews table

Code:

user | item | review | submit_dtm
-----+------+--------+------------
1    | 1    | nice   | 0000-00-00
2    | 1    | cool!  | 0000-00-00
2    | 3    | sucks  | 0000-00-00
3    | 1    | A++    | 0000-00-00
3    | 2    | etc.   | 0000-00-00

Then I want the result of the join to be like this:

Code:

user | item | rating | review
-----+------+--------+--------
1    | 1    | 5      | nice
1    | 2    | 4      | NULL
1    | 3    | 4      | NULL
2    | 1    | 5      | cool!
2    | 3    | 1      | sucks
3    | 1    | 5      | A++
3    | 2    | 3      | etc.

You're saying a LEFT OUTER JOIN will take care of this? Thanks for replying!

r937 · #4 (**permalink**) 10-10-07, 07:43

Quote:

Originally Posted by mhuggins

You're saying a LEFT OUTER JOIN will take care of this? Thanks for replying!

yes! you're welcome!!