question

victor_188679 avatar image
victor_188679 asked Erick Ramirez edited

How do I model a table of users where a column is optional and needs to be updated independent of the user ID?

I have a users table with an optional column: avatar, I want to be able to search and update this column.

CREATE TABLE users (
    id      uuid,
    email   text,
    avatar  text,  -- optional field, searchable & updateable
    PRIMARY KEY (id, email)
);

Since it's optional I cannot include it in users' primary key. in order to be able to search, I create another table. every time an avatar is given, I insert into users_avatars and update users table.

CREATE TABLE users_avatars (
    id      uui
    avatar  text,
    PRIMARY KEY (id, avatar)
);

However, I also want to be able to update the avatar, kind of stuck, not sure what to do.

[EDIT] In the relational database, i can do 

SELECT * FROM users WHERE avatar = 'niceavatar';

or

UPDATE users SET avatar = 'newavatar' WHERE id = 10; 

so I can search avatar column or update it.

data modeling
4 comments
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Erick Ramirez avatar image Erick Ramirez ♦♦ commented ·

I need a bit more info in order to respond.

in order to be able to search, I create another table.

What do you mean by "be able to search"? Do you mean your app needs to be able to query the table based on the avatar name/description/what ?

every time an avatar is given

What do you mean by "given"? It's not clear to me how this happens. Does "given" mean the app queries based on avatar?

0 Likes 0 ·
victor_188679 avatar image victor_188679 Erick Ramirez ♦♦ commented ·

In the relational database, i can do SELECT * FROM users WHERE avatar = 'niceavatar'; or UPDATE users SET avatar = 'newavatar' WHERE id = 10; so I can search avatar column or update it.

0 Likes 0 ·
Erick Ramirez avatar image Erick Ramirez ♦♦ victor_188679 commented ·

That wasn't quite what I meant. Let me rephrase...

How does the avatar get updated? Does the user provide the avatar themselves? If so from an app perspective, would you already know which user ID is updating their profile on some "page" in your app UI?

0 Likes 0 ·
Show more comments

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered Erick Ramirez edited

Suggestion

In order to run a query like this, you can index the avatar column:

SELECT * FROM users WHERE avatar = 'niceavatar';

Add a secondary index on the column with CREATE INDEX:

CREATE INDEX user_avatar ON users (avatar);

It's not necessary to create a separate table for it once you've got it indexed.

And since you know the user ID, you can just directly update the same as in your example:

UPDATE users SET avatar = 'newavatar' WHERE id = 10;

Primary key

As a side comment, I noted the primary key on the users table:

    PRIMARY KEY (id, email)

The partition key is id and email is a clustering key. It means that each user have multiple emails and avatars. Just noting it here in case it wasn't your intention. Cheers!

2 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

victor_188679 avatar image victor_188679 commented ·

Thank you! I read somewhere that secondary index is not recommended, so haven't looked into it much, will read more about it.


Regarding the primary key, I don't want multiple emails and avatars. Each user can have only one email and one avatar. What should I change?

0 Likes 0 ·
Erick Ramirez avatar image Erick Ramirez ♦♦ victor_188679 commented ·

Then it that case, you should just use:

    PRIMARY KEY (id)

And here's some information on When & when not to use an index. Cheers!

0 Likes 0 ·