2 Questions:What is the best way to implement a Ratting System? and Using Collation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

2 Questions:What is the best way to implement a Ratting System? and Using Collation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-12 : 09:49:27

SJ writes "Dear SqlTeam;
What is the best way to implement a Ratting System?
here is the challange...
I have a table that holds info. like articles, posts, comments
I want to give the users the ablity to vote or rate that content ?
what is the best way to implement this ?
Ways I know:
1) adding 2 columns , Ratting and RattingUserCount
Ratting : holds over all ratting,the application gives a Scale of 1 to 10 or N
when the rate is submitted this column is incremented by that rate.
e.x: a user choose a rate of 5 to rate the content, the Ratting Column is updated to
Ratting Old Value+5
and RattingUserCount is incemented by 1
to get the ratting percent:
Ratting/RattingUserCount
if is the Rate Scale (N) is 10, this expression means
Ratting/RattingUserCount Out Of 10 Or N
2) this way is the same as #1 but instead of adding the columns to the content table, a new table is created called
Content_Ratting
Columns:
ID:Identity
RattingType: tinyinteger ( Ratting Type)
ForgenKey:INT ( a refrence to the Content ID in any table )
Ratting:INT
RattingUserCount :INT
the idea is the same , but with design any content in any table can be ratted by the users..
e.x:if you have these tables
articles,products, books, progams
to implement ratting to these content you have to give each table a RattingType let
articles,products, books, progams
0,1,2,3
and every table must have an Identity column to be refrenced by Content_Ratting using ForgenKey
--------------------------------------
All these Ideas are based on two columns, I have seen a ratting system that is based on 2 maby
but i works differntly,the users can rate using a scale of one ?
my methods will not work in a scale of one, How does it work?
if you did't get it, look at the tek-tips forums (www.tek-tips.com), and see how the ratting is done on the forum topics.
the Questions:
1) a scale of one Ratting system , How does it work?
2) what is the best way to implement a ratting system?
using two columns in a table or using a seperated table ?
--------------------------------------
Anthor Question:
Using Collation:
I have a DB with Collation=XXX_XX
XXX_XX= Any Collation but not english
do I have to make all the datebase objects( tables,columns,etc) Collation to XXX_XX or the datebase collation is used..
Let say I have a char column in Table1,
Do I have to Use leading N to insert in or update the column with XXX_XX. Text?
I can change the char column to nchar to accept any text and store it as unicode, but I want to take advantage of using collation,
or collation is just for sorting and comparing date?
--------------------------------------

Thanks"

nlocklin
Yak Posting Veteran

69 Posts

Posted - 2002-03-12 : 10:15:13

I'd take a different twist on option 2. How to people vote for an article? Do your users log in? If they do, I would implement a table to join the users table and the articles table, like UserArticlesRating. The table would store foreign keys for the article and user, as well as the rating that user is giving that article. Then all you need to do is a simple select statement on that table for that ArticleID to get the average rating. On top of that, you can enforce users only voting for the article once (and if you want to be nebby* you can see who is giving an article low votes).

* nebby: Pittsburghese for "nosy".
http://www.pittsburghese.com/

--
"I'm always doing that. I'm always messing up some mundane detail."

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-12 : 10:23:57

I'd use the second option - a seperate table. But maybe add a GUID column to each of your tables containing content you want to rate, that way you'd know which table your foreign key was from - i.e. you may have an article with an id of 101 and a news item with an id of 101 but if you put 101 in the rating table you don't know which one you want if it is related to both tables. with a GUID its unique across space and time (but hey, aren't we all!)

SJ
Starting Member

2 Posts

Posted - 2002-03-13 : 10:21:43

Thanks Guys for your input;
Regarding the Seperated Table Votting System Approch:
The table Structure:
ID int IDENTITY
RType varChar(15) [with index]: Vote Type
RPoints int:Total Points
UCounter int:Number Of users Votted
ForgKey int [with index]: a foreign to a content Table
the usage:
if votting for forum topics,RType can have 'thread' or 'forum_topic' as a value
this way, we can have duplicate ForgKey Values with diffrent Rtype
for e.x
you have a web application that have Articles,Forum,Users,Programs ,links,....,etc.
you can setup the votting system this way:
RType values:
articles,forum,users,programs,links
or if you need more:
if you need more votting for the articles content
like Useful ,Innovative,Informative
you can add these values for Rtype
article_use,article_Innov,article_info
This System can be expanded to include users comments or any other input..
jackstow as you can see ForgKey and RType replace the need for GUID ..
For Forcing the Users to Vote Once Only another table is needed to
holds user ID and Votting Table ID , a date column if you need to clean the table after say a month or so.
Scale One Votting System, How Does it work?

What about Collation Guys?
My problem is with the unicode column types (nchar,nvarchar,ntext), there size is 2X the non-unicode types,
My App. will use Only One Language(Not English), so Collation shoul solve the problem right?
Also, when inserting fields(non-unicode column types) with Collation XXX_XXX
Should I use leading N to insert in or update or Not ?

Thanks;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources