define rating column in table?

Question

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 ?

Answer 1

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)
)

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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...