Efficient 1:n Relation in Google App Engine Datastore

Question

In our system every user can write any other user a message. The first obvious idea is a data model like this:

User
 username
 email
 ... more properties

Message
 user_from_FK
 user_to_FK
 text
 creation-date
 ... more properties

So a message stores the User-Key as a FK in a tradition database. Like (for simplicity visualised as a table):

User-"Table"

KEY  username ...
-----------------
1    peter
2    paul

KEY

Message-"Table":

KEY   user_from_FK  user_to_FK  creation-date  text ...
-------------------------------------------------------
11    1             2           2342342342234  Hi Paul.
22    1             2           2342342356455  Hi Paul. You got my message?
33    2             1           2342342377544  Hi Peter. Yes, I did.

To query all messages for a given user is simple then:

SELECT __key__ FROM Message WHERE user_to_KF = :userKey ORDER BY creation-date

Our system should scale to millions of users and millions of messages. Maybe 500 messages will be send per second. Is this simple solution then a good data model? Can we do it better? (Every user is not allowed to have more then 1000 messages and this inbox. Messages should be sorted by date when returned. And we want to do paging.)

Answer 1

That should work fine for storing/retrieving data. The puts and fetches won't block each other, if that's what you're worried about.

You might want to store more data for display in the Message model, though, since you can't JOIN data with the datastore. For example, you could store the name of the sender in the Message model.

For paging, you could benefit from adding to the structure slightly. Have a look at the following article for information on how you would do it: Paging through large datasets

Also have a look on how to do paging without changing the structure: Efficient paging using key instead of a dedicated unique property

Answer 2

Sorry. One information in my message was wrong: It's not 500 messages per second but per minute being written! But nice to hear that the performance is fine and the data model works. Great GAE :)

Answer 3

You should consider denormalization to guarantee scalability. Take a look at this article at hishscalability.com and the related articles.

It is the price to pay when you want to scale efficiently across a large number of machines :)