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

Except in a relational context where one is pushing for normalization (quite advisable in the relational case, of course!) the separate TodoListItem "relation class" seems a bit of an overkill to me, and somewhat "stilted" in terms of the way one reasons about the problem versus the way one codes it. Optimization-wise it would certainly make it easier to find all lists on which an item is, however.

Answer 2

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.

Answer 3

Given that the TodoListItem can belong to multiple TodoLists, I would be concerned that it would be valid to have a single order property that would work for each list to which the item belongs. I would think the Item would need an Order for each list to which it belongs.