1

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)

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?!). EDIT: or what if the friend changes their profile picture or name?

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

1
  • If you want to learn Cassandra specifically, choose an appropriate use case -- yours is not. If you want to learn application design, choose an appropriate database platform for your use case -- Cassandra is not. Trying to wrestle the wrong tool to do something it's not designed to do is counterproductive. This blog might be of help.
    – mustaccio
    Nov 21, 2022 at 20:48

1 Answer 1

0

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 of friends (clustering key). To distinguish it from user ID, I'll call it "friend ID".

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!

3
  • What if the user (friend) changes their profile picture? Do I need to update every row or maybe there’s a better way to do that?
    – Jacobino
    Nov 23, 2022 at 10:47
  • Not every row, just the picture for that specific friend. For example: UPDATE friends_by_userid SET picture WHERE userid = ? AND friendid = ?. Cheers! Nov 23, 2022 at 23:29
  • Okay, but user->friend is a many-to-many relationship which means that a friend can be assigned to multiple users. And the solution you provided here can only work if I know the userID . I want to update profile picture globally and for users that have the specific friend added.
    – Jacobino
    Nov 24, 2022 at 6:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.