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