Recently I started to learn Cassandra. I needed to design the database for my web application. So, I prepared conceptual data model as well as application workflow, and currently I’m stuck on something…

Let me provide you with some details of the issue. Well, I want to show all friends of currently logged-in user WITH PROFILE PICTURES AND THEIR FULL NAME.

So I probably need two tables:

Fragment of Application Workflow

**users_by_id**
- user_id PARTITION KEY
- email
- password
- profile_image
- full_name

**friends_by_user_id**
- user_id PARTITION KEY (whose friend is it)
- friend_id (user id of the friend)
etc.

And now let’s say I want to display all friends in a list, but the problem is the user expects the app to show their profile pictures and their full name (not just the friend‘s user id), so the user can recognize who is who (pretty logical, right?). So, how do I do that? I mean I could get the users id and then query the users table to finally get the full name and profile picture individually. Although, I don’t think it would be very efficient (because what if the user have hundreds of friends?!).

What is the right way to solve this problem? Thanks in advance!

CodePudding user response：

The right way to solve this problem is to denormalize and not be afraid to duplicate data. You should have one query with one matching table per screen of your application.

Your table should look like: friends_by_user_id

• user_id PARTITION KEY
• friend_id
• profile_image
• full_name

I suggest you could use friend_id as a clustering column for this table allowing you to store in the same partition all friends information for a given user. You can then retrieve it using only the user_id. The clustering column will not be required to retrieve the friend list.

CodePudding user response：

It is a lot simpler than you think. Since you want to retrieve all of a specific user's friends, we know that:

1. We need to filter based on a user's attribute (user ID).
2. The query will return one or more rows of friends.
3. For each friend, you want to list their full name and profile picture.

Based on (1), you want the table to be partitioned by the user ID. And based on (2), you want a cluster/group of friends (clustering key).

So far, the table design looks like:

CREATE TABLE friends_by_userid (
    userid text,
    friendid text,
    ...
    PRIMARY KEY ((userid), friendid)
)

To flesh out the rest of the table, we want to store the friends full names and pictures from (3) above. Now the table looks like:

CREATE TABLE friends_by_userid (
    userid text,
    friendid text,
    fullname text,
    picture blob,
    PRIMARY KEY ((userid), friendid)
)

To query the table:

SELECT fullname, picture FROM friends_by_userid WHERE userid = ?

If the user has friends in the table, it will return multiple rows of names and profile pics. Cheers!