On Wed, Jan 22, 2014 at 06:44:20PM +0100, Kasper Middelboe Petersen wrote: > I'm a little worried about the data model I have come up with for handling > highscores. > I have a lot of users. Each user has a number of friends. I need a > highscore list pr friend list. > I would like to have it optimized for reading the highscores as opposed to > setting a new highscore as the use case would suggest I would need to read > the list a lot more than I would need write new highscores. > Currently I have the following tables: > CREATE TABLE user (userId uuid, name varchar, highscore int, bestcombo > int, PRIMARY KEY(userId)) > CREATE TABLE highscore (userId uuid, score int, name varchar, PRIMARY > KEY(userId, score, name)) WITH CLUSTERING ORDER BY (score DESC); > ... and a tables for friends - for the purpose of this mail assume > everyone is friends with everyone else > Reading the highscore list for a given user is easy. SELECT * FROM > highscores WHERE userId = <id>. > Problem is setting a new highscore. > 1. I need to read-before-write to get the old score > 2. I'm screwed if something goes wrong and the old score gets overwritten > before all the friends highscore lists gets updated - and it is an highly > visible error due to the same user is on the highscore multiple times. > I would very much appreciate some feedback and/or alternatives to how to > solve this with Cassandra.
Is friendship symmetrical? Why not just store the scores in the friend list like so: CREATE TABLE friends ( userID uuid, friendID uuid, name varchar, score int, PRIMARY KEY (userID, friendID) ); and then simply sort the friends by score in your application code? When you update a user's score, you just do something like: UPDATE friends SET score=x WHERE userID IN (all,my,friends) AND friendID=myID; It should be quite efficient unless you have people with truly ludicrous numbers of 'friends' ;-)
