incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Edward Capriolo <edlinuxg...@gmail.com>
Subject Re: Correct way to design a cassandra database
Date Sat, 22 Dec 2012 00:51:27 GMT
You could store the order as the first part of a composite string say first
picture as A and second as B. To insert one between call it AA. If you
shuffle alot the strings could get really long.

Might be better to store the order in a separate column.

Neither solution mentioned deals with concurrent access well.

On Friday, December 21, 2012, Adam Venturella <aventurella@gmail.com> wrote:
> One more link that might be helpful. It's a similar system to photo's but
instead of Photos/Albums it's Songs/Playlists:
> http://www.datastax.com/dev/blog/cql3-for-cassandra-experts.
>
> It's not exactly 1:1 but it covers related concepts in making it work.
>
>
> On Fri, Dec 21, 2012 at 8:02 AM, Adam Venturella <aventurella@gmail.com>
wrote:
>
> Ok.. So here is my latest thinking... Including that index:
> CREATE TABLE Users (
>     user_name text,
>     password text,
>     PRIMARY KEY (user_name)
> );
> ^ Same as before
> CREATE TABLE Photos(
>     user_name text,
>     photo_id uuid,
>     created_time timestamp,
>     data text,
>     PRIMARY KEY (user_name, photo_id, created_time)
> ) WITH CLUSTERING ORDER BY (created_time DESC);
> ^ Note the addition of a photo id and using that in the PK def with the
created_time
> Data is a JSON like this:
> {
>     "thumbnail": <url>,
>     "standard_resolution":<url>
> }
>
> CREATE TABLE PhotosAlbums (
>     user_name text,
>     album_name text,
>     poster_image_url text,
>     data text
>     PRIMARY KEY (user_name, album_name)
> );
> ^ Same as before, data represents a JSON array of the photos:
> [{"photo_id":..., "thumbnail":<url>, "standard_resolution":<url>},
> {"photo_id":..., "thumbnail":<url>, "standard_resolution":<url>},
> {"photo_id":..., "thumbnail":<url>, "standard_resolution":<url>},
> {"photo_id":..., "thumbnail":<url>, "standard_resolution":<url>}]
>
> CREATE TABLE PhotosAlbumsIndex (
>     user_name text,
>     photo_id uuid,
>     album_name text,
>     created_time timestamp
>     PRIMARY KEY (user_name, photo_id, album_name)
> );
> The create_time column here is because you need to have at least 1 column
that is not part of the PK. Or that's what it looks like in my quick test.
> ^ Each photo added to an album needs to be added to this index row
>
> As before, your application will need to keep the order of the array in
tact as your users modify the order of things. Now however if they delete a
photo you need to fetch the PhotoAlbums the photo existed in and update
them accordingly:
> SELECT * FROM PhotosAlbumsIndex WHERE user_name='the_user' AND
photo_id="<uuid>"
> This should return to you all of the albums that the photo was a part of.
Now you need to:
> SELECT * FROM PhotosAlbums where user_name = "the_user" and album_name IN

Mime
View raw message