Google datastore modeling help

database-designgae-datastoregoogle-app-enginenosql

I have a very simple requirement. I have an application where users log in, add/remove/view friends, and can send/receive (also view the message you sent and received) a message (message is more like an email, not a real time chat) from a friend. When they receive the message, the application needs to mark the message as 'read'. The messages must also come ordered by newest to oldest.

My thoughts:

KINDS:

USER

username <String> (ID)
password <String>
friends <Comma Separated String of usernames> (will contain a max of 100 friends).

MESSAGE

msg_id <String concating [from+to+timestamp]> (ID)
from <String> (indexed)
to <String> (indexed)
timestamp (indexed)
msg <String>
hasRead <boolean>

1) User would login using their username and password

2) User could get friends by getting their USER entity based on their username

3) User could add/remove friends by getting the entity of user1 and user2 and either adding or removing friend via transaction to make sure they are consistent.

4) User could get all the message they have sent by using indexing the the 'from' attribute (limit of 10 message per request). The same could be done to view all the messages they have received by using the 'to' attribute. When the message has been seen for the first time I would go to the message (1 get) and update the entity (1 write + (4 writes x 3) = 13 writes to update the entity).

My major concern – If a user gets 10 messages, this will require 10 get requests plus if all 10 messages are new I will need to update each entity and if all of them are new that is (10 x 14) 140 writes. Then if I get another 10 message for this user the same process and this could all add up very quickly.

I then thought of creating an entity to store all the sent/received messages in a string for a user inside of a single entity:

MESSAGE
user_entitynum1 <String> (ID)
messages <String looking like this: msg1_fromuser2_unread_timestamp, msg2_touser8_read_timestamp, msg3_touser5_unread_timestamp, etc>

This way I could store all the message (under 1mb) inside of this one entity, but I would have to keep track of which entity each user is at (if the user exceeds the 1mb limit I will have to create a new entity). This proved to also not be too efficient because if I send a message to perform 2 gets to see which message entity I am currently at and which message entity they are currently at. From there I must now use another 2 reads to get those entities and another 4 writes to update them both (considering I do not need to create another entity if it is full).

I was looking for any ideas to accomplish what I need in a more efficient way. I have nothing implemented yet so I am open to ANY ideas. My main concern is for this to be efficient,

Cheers!

Best Answer

As per my understanding, your business rules (requirements) are as follows.

  • Each User can have one or many User Friends.

  • Each User Friend must belongs to one User.

  • Each User can send one or many Messages.

  • Each Message is sent by one User.

By following the above mentioned business rules, your database design may looks like as follows,

USER 
{ 
 user_id 
}

PASSWORD 
{
password_id, 
user_id
}

USER_FRIENDS 
{ 
userfriend_id, 
user_id, 
added_by, 
removed_by 
}

MESSAGE 
{ 
message_id, 
message,
sent_by,
recieved_by,
is_read
}

If you need to track the user history, add the required data fields with domains such as timestamp, date, etc...at the appropriate entities (message history, login history, friends login, add/remove history). For calculating storage space and setting limit for users, you can done that using programing. I hope this will give you an idea.

Related Question