Re: Datamodel for a highscore list
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, friendIDuuid, namevarchar, 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' ;-)
Re: Data modeling users table with CQL
On Tue, Jan 21, 2014 at 10:40:39AM -0800, Drew Kutcharian wrote: Thanks, I was actually thinking of doing that. Something along the lines of CREATE TABLE user ( idtimeuuid PRIMARY KEY, emailtext, nametext, ... ); CREATE TABLE user_email_index ( email text, id timeuuid, PRIMARY KEY (email, id) ); And during registration, I would just use LWT on the user_email_index table first and insert the record and then insert the actual user record into user table w/o LWT. Does that sound right to you? Yes, although unless I'm confused you don't need id in the primary key on user_email_index, just PRIMARY KEY (email).
Re: CQL and undefined columns
On Wed, Jul 31, 2013 at 03:10:54PM -0700, Jonathan Haddad wrote: It's advised you do not use compact storage, as it's primarily for backwards compatibility. Yes indeed, I understand what it does and why now, but only because I was pointed to the thrift-to-cql document. The CQL documentation itself doesn't make it at all clear, I was originally under the impression that the way 'COMPACT STORAGE' works was the way CQL works by default, because that's the natural assumption until it's explained why it doesn't work that way. I was pointing out that either the thrift-to-cql document must be wrong, or the CQL document must be wrong, because they contradict each other.
CQL and undefined columns
I thought that part of the point of Cassandra was that, unlike a standard relational database, each row does not have to have the same set of columns. I don't understand how this squares with CQL. If I want to have a table (column family?) with a few fixed columns that are relevant to every row, I can create that with CQL's CREATE TABLE, but if I then want to set extra columns with arbitrary names on various rows, how do I tell CQL what type those columns are? Or is this feature of Cassandra now deprecated?
Re: CQL and undefined columns
On Wed, Jul 31, 2013 at 02:21:52PM +0200, Alain RODRIGUEZ wrote: I like to point to this article from Sylvain, which is really well written. http://www.datastax.com/dev/blog/thrift-to-cql3 Ah, thankyou, it looks like a combination of multi-column PRIMARY KEY and use of collections may well suffice for what I want. I must admit that I did not find any of this particularly obvious from the CQL documentation. By the way, http://cassandra.apache.org/doc/cql3/CQL.html#createTableStmt says A table with COMPACT STORAGE must also define at least one clustering key, which seems to contradict definition 2 in the thrift-to-cql3 document you pointed me to.
