ListProperty of keys vs Many-to-Many in App Engine

Question

As a hypothetical example, I have a model TodoItem and a model TodoList. An TodoList has an ordered list of TodoItems, and any one TodoItem can belong to any number of TodoLists (Many-to-Many). No other information needs to be stored about their relationship other than the order of a TodoItem in a TodoList. What is the best way to represent this in the data store?

There are two ways to implement this - give the TodoList class a ListProperty of db.Key's which will refer to TodoItem's:

class TodoList(db.Model):
  items = db.ListProperty(db.Key)

or make a ListItem model that also contains ordering information:

class TodoListItem(db.Model):
  item = db.ReferenceProperty(TodoItem)
  list = db.ReferenceProperty(TodoList)
  order = db.IntegerProperty()

I will definitely be optimizing this later by denormalizing the models, but pre-optimization, does any one representation have an advantage over the other?

Answer 1

This depends on a few factors:

• Do you need to store information about the relation itself, other than its order? For example, a many:many between orders and products would need to store the quantity of each product.
• Do you need to associate more than a thousand or so items on the side of the relation with 'smaller' cardinality (eg, >1000 todo items, or >1000 lists for an item)?
• Do you typically want to retrieve all associated items at once, or do you want to be more selective?

If you need extra information, or have many elements in your association, or only need to retrieve a few of them, the relation entity is probably a better choice. In other situations, the list can be both easier and faster. In the case of a todo list, I would say that a list of keys is most definitely the best way to go.