Modeling contact list, plain table or List

["Isaac P." <ip...@hotmail.com>]

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

Re: Modeling contact list, plain table or List

["Laing, Michael" <mi...@nytimes.com>]

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 <ja...@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. <ip...@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
>>
>>
>

Re: Modeling contact list, plain table or List

[Jack Krupansky <ja...@gmail.com>]

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. <ip...@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
>
>