Many To Many Using Google App Engine Lists

I ran across a case today where I needed a many to many relationship, but denormalizing the relationship to make it faster on Google App Engine would have meant duplicating almost all the data on the table.

Since I did not need any data specific to the relationship (i.e. the middle table only had the foreign keys), I could use Google App Engine’s list field to store a list of ids in the table rather than having a separate model for the relationship.

I have a Contest model, and need to keep track of multiple administrators for the contest. Administrators are members of the site, and have a record in the Member model. So I have a many to many relationship between Contest and Member. I don’t need to keep track of any data on the relationship, beyond that it exists, so into my Contest model I put the following field:

Field("admins","list:reference member")

On Google App Engine this uses the list type designed for this situation. On SQL backed databases, it uses a text type and stores a list of member ids. I’ve done no testing yet, so can’t say how efficient this is on SQL databases versus a more normal join. On GAE, though, where joins are prohibited, this is the way to do simple many to many relationships where you don’t store extra information on the relationship itself.

This entry was posted in Web2py. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *