Jack/ Michael, Thanks for answering.
How big?: Less then one hundred contacts by user is the normal. Update requirements: The UPDATE requirements are all around each user “favoriting/unfavoriting” the contacts . Deleting is not very frequent. Does that mean that in C* 3.02 , for this use case to work, the contact name must be part of a composite partition key in order to allow sorting by contact_name like this ? : CREATE TABLE communication.user_contact_list ( user_id uuid, contact_name text, is_favorite boolean, contact_id uuid, created_at timeuuid, favorite_at timestamp, PRIMARY KEY ((user_id, contact_name), is_favorite) ) WITH CLUSTERING ORDER BY (contact_name ASC); Query: Select * from user_contact_list where user_id = :userid and is_favorite = true order by contact_name asc; Looks like each contact as a row/clustering key will be the way to go. Thanks IPVP From: Laing, Michael <michael.la...@nytimes.com><mailto:michael.la...@nytimes.com> Reply: user@cassandra.apache.org <user@cassandra.apache.org>><mailto:user@cassandra.apache.org> Date: January 9, 2016 at 11:51:27 AM To: user@cassandra.apache.org <user@cassandra.apache.org>><mailto:user@cassandra.apache.org> Subject: Re: Modeling contact list, plain table or List Note that in C* 3.02 the second query is invalid: cqlsh> Select * from communication.user_contact_list where user_id = 98f50f00-b6d5-11e5-afec-6003089bf572 and is_favorite = true order by contact_name asc; InvalidRequest: code=2200 [Invalid query] message="PRIMARY KEY column "is_favorite" cannot be restricted as preceding column "contact_name" is not restricted" On Fri, Jan 8, 2016 at 6:50 PM, Jack Krupansky <jack.krupan...@gmail.com<mailto:jack.krupan...@gmail.com>> wrote: How big is each contact list expected to be? Dozens? Hundreds? Thousands? If just dozens, a simple list column would seem sufficient. If thousands, the row (not partition) would get kind of bloated. What requirements do you have for updating? If updating contacts and lots of contacts, I think I'd prefer each contact as a row/clustering key. Nice to be able to do selective queries to return slices of the clustering key values, which is not so easy if they are all just a single list column. -- Jack Krupansky On Fri, Jan 8, 2016 at 6:31 PM, Isaac P. <i...@hotmail.com<mailto:i...@hotmail.com>> wrote: Hi everyone What would perform better while modeling a simple user contact list that will be used mainly to select the recipients for/from/to messages ? a) Individual rows to each (user, contact) pair so a select would fetch all the rows to retrieve all the contacts from a given user. or b) A single row for each user containing the List<Contact> UDT. Aside of the basic CRUD, the queries will be the following ones: Select * from user_contact_list where user_id = :userid order by contact_name asc Select * from user_contact_list where user_id = :userid and is_favorite = true order by contact_name asc After reading this https://docs.datastax.com/en/cql/3.0/cql/ddl/ddl_compound_keys_c.html the table is looking like this: CREATE TABLE communication.user_contact_list ( user_id uuid, contact_id uuid, contact_name text, created_at timeuuid, is_favorite boolean, favorite_at timestamp, PRIMARY KEY (user_id, contact_name, is_favorite) ); Any guidance will be appreciated. Thanks -- IPVP
