1

Hi I am creating database for rating the items in my list but I want to ask what this if I have five stars rating how can I do it in database "rating" column should I use Int with check constraint or user define type ?

3

This is an example of using a check constraint for your ratings table

CREATE TABLE RatingsTable
(
Ratings decimal(2,1)
CONSTRAINT chk_Ratings CHECK (Ratings >= 0 AND Ratings <= 5)
)
4

You better use tinyint. It's int from 0 to 255. It's also takes only 1 byte to store value. It's enough for your 5-star rate.

If you need store values like 4.5 stars you may store stars like this:

1 star =10

2.5 stars=25

and so on

And, of course, you need check constraint on this field.

tinyint - using storage 1 byte

numeric(1)/decimal - using storage 5 bytes

  • I like this solution. Another option is to store from 0 to 10 where 0 is 0 stars, 1 is 0.5 stars, 2 is 1 star, ..., and 10 is 5 stars, but this makes it more clear that it's a 0 to 5 scale (or 1 to 5 if it's the "if I could give it a zero rating I would" rather than a "if I could give it a negative rating I would" scale) – Benjamin Atkin Jan 10 '19 at 6:50
1

Here is the test of Data Types:

--Create A test Table

CREATE TABLE DTTest(
 id TINYINT
,rate_Varchar varchar(4)
,rate_float FLOAT(1)
,rate_real REAL
,rate_numeric NUMERIC(2,1)
,rate_decimal DECIMAL(2,1)
)

--Insert Some Sample Rows

INSERT INTO [dbo].[DTTest]
VALUES(45,'4.5',4.5,4.5,4.5,4.5)
    ,(45,'4.75',4.75,4.75,4.75,4.75)

--Column Size

EXEC sp_help [DTTest]

So you should use float to save Decimal Ratings(4.75 or 4.5) etc.

0

You'll probably want to use numeric or decimal so that you can have 4.5 stars.

0

In my view it depends on the rating strategy your application provides, I mean if your application provides ratings only like 1,2,3,4,5 stars not any decimals, you can obviously go with tinyint, and if your application will accept the ratings like 1.5, 2.2, 4.5..... and in that case you can probably go with float / decimals...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.