Original MySQL ER-model:
There is no difficulties to implement account management and security case based on UI architecture – there is no need to join tables.
First problem appeared on attempt to implement friendship data or messages – we need to join users_link and users table for friendship data and we need to join messages and friendship data to filter messages to display here.
There are some shortcuts which will allow us to solve these problems.
There is no need to join link_users with messages cause ignored users will be filtered on the stage of selecting friend to contact.
All we need here to store – both sides of the conversation with specified sender.
From my point of view we need next updates:
- Delete link with users_link table
- Delete user_id, we will use id_user_sent, id_user_recieved and in our CQL all we need to filter pair of users like this: WHERE (id_user_sent = ‘User1’ and id_user_recieved = ‘User2’) OR (id_user_sent = ‘User2’ and id_user_recieved = ‘User1’). At the interface – System separates sent from received messages by user_id link (this is a sender user id), we will use user_sent column for that.
Friendship uses to store relationship between users and got it’s own livecycle:
- User1 sent friendship request to User2
- User2 accepted friendship
- User1 sent User2 to ignore
- User2 sent User 1 to ignore, so, both ignored
- User2 decided to send a message to User1, so their friendship status: User1 ignores User2
This friendship status uses at the send message page to display non-ignored friends to send them messages (as a result that covers next requirement: user should not receive messages from ignored user, because he/she will not find him/her in a list of friends).
To display list of friends we need to know relation with the friend and his full name, to avoid this join we should add user’s name to the users_link table, but there are some important problems:
What to do if the friend’s name changed
Looks like there is only one way if we would like to avoid joins (Spark, just in case, supports Join, the question is about the speed)
How to store both friends data
Cause there are 2 user rows there – we should know who created it and who is logged right now and we have all that data. At MySQL implementation it was just to store one link for both users and I think we will keep this solution but will add full name for both of them.
There is another one way how to handle it – create 1 row for each user, so it will be:
Let’s compare both solutions:
|2 rows for 1 friendship||1 row for 1 friendship||Note|
|x2 for database row quantity||x1 for database row quantity||We will have a huge messages table, so, even if we will have x2 size of the users_link DB – that’s not a big deal|
|CQL Statement is much simpler to read or update user data||CQL Statement is much complicated to read or update user data||We need a CASE (which is not supports in Cassandra) to use 1 row for 1 friend schema, like this:
SELECT.first_name,last_name FROM chat.users_link ul (CASE
WHEN id_user = ” + userid + and (ul.link_type=2 OR ul.link_type=4) THEN ul.related_user = users.id
WHEN ul.related_user = ” + userid + ” and (ul.link_type=2 OR ul.link_type=3) THEN ul.id_user = users.id
For the 2 row 1 friendship schema it will be:
SELECT.first_name,last_name FROM chat.users_link ul WHERE id_user = ” + userid + and (ul.link_type=2 OR ul.link_type=4)
|For2 row 1 friendship schema we should add new status – ‘not a friend’ with code 0, status ‘both ignored’ could be excluded from the DB|