Java, NoSQL, SQL, REST API and other scary words

Modification of the SQL ER model to NoSQL Cassandra

Original MySQL ER-model:

mysql

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.

Messages

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

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:

architecture

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

END);

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

Result

cassandra

Advertisements
Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s