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.