Yes friendship is symmetrical. This could work for my problem right now, but I'm afraid it would just be postponing the problem slightly until something like big tournaments (which are coming) raises the same problem again.
On Wed, Jan 22, 2014 at 6:58 PM, Jon Ribbens < jon-cassan...@unequivocal.co.uk> wrote: > 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' ;-) >
