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!

Cowboy

Answer 1

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.

Tailrace

Indeed, or if you intended to average them. TINYINT is probably more appropriate, just make sure you constrain the values in your application. – Zed

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> – Cowboy

@Jordan, my thoughts exactly. ENUM really shines if you have a fixed list of texts, and there is no numerical connotation – Tankersley

@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. – Tankersley

Answer 2

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.

Tankersley

Answer 3

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.

Connie