MySQL 5-star rating datatype?

Question

Would ENUM('1','2','3','4','5') be a sensible datatype for a product rating which must be between 1 and 5?

Thanks!

score 11 · Accepted Answer · answered Jan 12 '10 at 14:03

11

Yes, that would be an appropriate data type since it enforces your domain.

If you are going to add (or do any other mathematical operation) them together, however, a numeric data type might be better.

answered Jan 12 '10 at 14:03

Daniel A. White

187,200
47
362
445

3

Indeed, or if you intended to average them. TINYINT is probably more appropriate, just make sure you constrain the values in your application. – Jordan Running Jan 12 '10 at 14:08
Ah good point thanks! So for example if I wanted to calculate the average rating for a product enum would be a bad choice, right? Shall I just use an int and programmatically constrain the input to between 1 & 5> – Danny King Jan 12 '10 at 14:09
@Jordan, my thoughts exactly. ENUM really shines if you have a fixed list of texts, and there is no numerical connotation – Roland Bouman Jan 12 '10 at 14:09
@Danny King - yes, definitely. The stars is a matter of presentation. but as far as data is concerned you are quite literally 'counting starts'. So an integer type is completely appropriate. – Roland Bouman Jan 12 '10 at 14:10

score 11 · Answer 2 · answered Jan 12 '10 at 14:08

I suggest using

TINYINT UNSIGNED NOT NULL

or, for better ANSI/SQL compatibility, use:

SMALLINT NOT NULL

With an integer type, it is much easier to do calculations. ENUM is not bad, but there is a potential to mess up because it's kind of a dual string/int type (beneath the covers, it's an int, but from the outside, it's a string). And indeed, suppose you do feel the need to go to 3 stars, or 10 stars or so, the migration will be much less painful.

princebillyGK · Answer 3 · 2022-02-04T20:57:36.603

If you are using Mysql 8+ then you use TINYINT with CHECK constraint

-- Product reviews
CREATE TABLE product_review (
  product_review_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  unique(user_id, product_id),
  rating TINYINT UNSIGNED NOT NULL CHECK (
    rating > 0
    AND rating <= 5
  ),
  review VARCHAR(2047) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 -- FOREIGN KEY (product_id) REFERENCES product (product_id) ON DELETE CASCADE ON UPDATE CASCADE.
   -- FOREIGN KEY (user_id) REFERENCES user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
);

It would not allow value other than 1, 2, 3, 4, 5 and also support any kind of mathematical operation, for example you can get average rating of the product with simple calculation.

MySQL 5-star rating datatype?

3 Answers3

Linked