Google Datastore model User and Messages efficently

database-designgae-datastoregoogle-app-enginenosql

I have two kinds: USER and MESSAGES. A user can send/receive many messages to/from another user. Thinking in a relational way the relationship would suggest a user can have many messages, and a message can only be created by one user. So if user A sends a message to User B, both user A should be able to see the message they sent, and user B should be able to see the message they received. I was thinking first of storing all sent/received messages inside of each user object, but since there is a 1mb limit for entities this is not an option as some users can retrieve many more messages than this. Second I was thinking of creating two kinds, USER and MESSAGES.The messages kind would have a to and from property both which would be indexed so that I can get all the messages a user has sent and received. As a result each message would be its own entity and this is where it is problematic. Lets say a User is deleted, I know have to delete all the message sent and received by this user. If a user has sent though sands of messages, the cost of removing each message will be very expensive. I was wondering what would be an efficient way to model this. I am be to Google Datatore and would be open to any ideas as I have nothing implemented yet.

Best Answer

Your second approach seem to be the best. Having a Message entity with key relations to sender/reciever.

As for you deleting a user dilema, you should consider not removing the entity when a user is deleted as this would break your db consistency. You should probably just have a logic deletion (eg a boolean value changed when a user is deleted), that way, messages sent to/from that user will stll make sense for other users.