sql - how to store a mutually exclusive value in a table -

based on social networking design, need store whether person has liked or disliked something. there other actions can take such share well.

what need make sure when person likes something, value stored true. if change mind , dislike same thing, like'd value set null , dislike value set true.

initial approach table this:

userid (int) | itemid (int) | (bit) | dislike (bit) | share (bit) | ... 1              1              1            null            null 1              2              null         1               null 1              3              1            null            1 

the problem here if more actions can taken on item e.g. 'favourited' have add these columns table. breaking normalisation rules work. important business rule cannot like , dislike same item @ same time. 1 or other. guess have logical processing on application end rather db end if there's better way please let me know!

the other approach have table stores user actions this:

actionid | actionname 1          2          dislike 3          share 

and have linking table this:

userid | itemid | actionid 1        1        1 1        1        2 1        1        3 

the problem second approach think hard make actions mutually exclusive. user has liked item, , disliked it, , shared it. in application have work out actionid relates or dislike , depending on 1 chosen, delete other. here have delete row actionid value of '1' because overwritten dislike action value of '2'

could please advise on best way might be? mutual exclusivity between actions causing me bother.

alternatively, re-implement integer column , allow (-1, 0, 1) represent (dislike, neutral, like), in turn may simplify ranking item's popularity. example, list top 10 liked items:

  select top (10)          itemid, sum(tolikeornotlike)     items group itemid order sum(tolikeornotlike) desc; 

this takes cares of mutual exclucivity well.

alternative

on suggestion spevy (see comments), 0 (for no or dislike) replaced null, it's easier determine overall activity on item. example:

  select top (10)          itemid, count(tolikeornotlike)     items group itemid order count(tolikeornotlike) desc; 

would return how many likes or dislikes item has received. can useful distinguish between item 2 likes , 1 dislikes, , item 10,000 likes , 9,999 dislikes, both of otherwise indistinguishable when summed.