I was planning on using MySQL for this, but Google Datastore looks like it could be a possible solution and I wouldn't have to worry about managing the server which is a big plus. I need to store one thing, messages. I was planning on having one MySQL messaging table which will contain messages from users to other users.
I am hoping I can translate somehow my MySQL message table effective into Google Datastore. My requirements are and solution using MySQL is:
A Message can only be sent by one user to one other user. This message when read for the first time will need to be updated stating it has been read so it will require one update in it's life if it has been read. A User must be able to view all their sent messages ordered by newest and all the messages they received ordered by newest.
My initial design looks like this:
Messages Table:
Message_ID (BIGINT) PK auto_increment
FromUser (INT 16)
ToUser (INT 16)
DateCreated (Timestamp)
MessageText (Varchar (500) )
HasRead (TINYINT (0=false, 1=true) )
The queries which will be run will include:
SELECT * FROM messages WHERE FromUser = '10000000' ORDER BY DateCreared DESC LIMIT [starting pagination value], 10 ; //get all users sent messages - newest first, get 10 at a time
SELECT * FROM messages WHERE ToUser= '10000000' ORDER BY DateCreared DESC LIMIT [starting pagination value], 10 ; //get all user's received messages - newest first, get 10 at a time
UPDATE messages SET HasRead = 1 WHERE Message_ID = '123456789'; // When a message has been read update it to show it has been read.
As a result I would have the following indexes:
-Message_ID (primary index) -(FromUser,DateCreated) BTREE DESC -(ToUser,DateCreated) BTREE DESC Additional "would like to have feature, but can live without it requirement if it effects performance considerably or is too complicated to implement" would be to have a user view their messages with another user:
SELECT * FROM messages WHERE (FromUser = '1000000 and ToUser = '2000000') OR (FromUser = '2000000' and ToUser = '1000000') ORDER BY DateCreated DESC [starting pagination value], 10 ; // Get all the messages sent between user 1000000 and user 2000000 - newest first, fetch 10 at a time. I was thinking of creating a new column which would be be a concatenation of [smaller_userid]-[larger_userid] and search on this field instead. If this was the case I would have an additional composite index on this new column + DateCreated. SELECT * FROM messages WHERE concateduser = '1000000-2000000' ORDER BY DateCreated DESC [starting pagination value], 10 ;
We believe this will work for, but we currently have an active community of users and have estimated when we roll out the feature it will be heavily used. Thus we would also like to plan for the future to be able to scale (premature now, but we think this is a very simple feature and are hoping to design it well now to save us time in the future).
So this is what I was planning on doing with MySQL. Could this be translated into Google Datastore in which it would be effective?
I looked into some solutions, but they would be quite expensive. My first thought was to make every message an entity. This entity then would have an index on the To, From, hash(user1 + user2) attributes. This would allow me to get to, from, and combination of user messages. The problem is the writes would be expensive and the updates if a message as read would be expensive. The query is also expensive, if I load 10 messages initially it will take 1+10 read units. I tried another solution of storing N messages inside of one entity. For each user I would need to store at which entity they are currently at which becomes expensive too because for each message I will need to write the same data twice (once for each user), updates have to happen to both sets, plus there is additional reads to get which current entity the users are at when inserting or reading messages. I am looking for a better solution to this simple requirement.
