Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    2

    Question Unanswered: Ratings & Reviews - 1 or 2 tables?

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Posts
    2
    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mhuggins
    You're saying a LEFT OUTER JOIN will take care of this? Thanks for replying!
    yes! you're welcome!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •