I would try:

Comments CF:
row_key: (thing_type : thing_id ) where thing_type is "city" etc
column_name: (comment_id (reversed)) where comment_id is a timeuuid
column_value: the comment. 

You will need to be wary of very wide rows. 

It's a pretty simple model for CQL 3 as well:

CREATE TABLE comments (
thing_type  text, 
thing_id long, 
comment_id timeuuid,
body text
user text, 
PRIMARY KEY ( (thing_type, thing_id), comment_id)
)

or 

CREATE TABLE comments (
thing_type  text, 
thing_id long,
created_at  timestamp. 
user text, 
comment_id long,
body text

PRIMARY KEY ( (thing_type, thing_id), created_at, user)
)


Hope that helps. 

 
-----------------
Aaron Morton
New Zealand
@aaronmorton

Co-Founder & Principal Consultant
Apache Cassandra Consulting
http://www.thelastpickle.com

On 28/09/2013, at 1:24 AM, cbertu81@libero.it wrote:

Hi all, in my very old Cassandra schema (started with 0.6 -- so without
secondary indexes -- and now on 1.0.6) I have a rating&review platform with
about 1 million review. The core of the application is the review that a user
can leave about a company. At the time I created many CF: Comments,
UserComments, CompanyComments , CityComments -- and I used timeuuid to keep
data sorted in the way i needed (UserComments/CompanyComments/CityComments did
not keep real comments but just a "referece" [id] to the comment table)

Since I need comments to be sorted by date, what would be the best way to
write it again using cassandra 2.0?
Obviously all these CF will merge into one. What I would need is to perform
query likes ...

Get latest X comments in a specific city
Get latest X comments of a company
Get latest X comments of a user

I can't sort client side because, even if for user/company I can have up to
200 reviews, for a city I can have 50.000 and more comments.
I know that murmur3 is the suggested one but I wonder if this is not the case
to use the Order Preserving.

A row entry would be something like

CommentID (RowKey) -- companyId -- userId -- text - vote - city

Another idea is to use a composite key made by (city, commentid) so I would
have all comments sorted by city for free and could perform client-side sorting
for user/company comments. Am I missing something?

TIA,
Carlo