Datastore Design Inquiry

Question

I'm creating a Trivia app, and need some help designing my model relationships. This question may get fairly complicated, but I'll try to be concise.

Trivia questions will all be part of a particular category. Categories may be a category within another category. If a trivia question is created/removed, I need to make sure that I also update a counter. In this way, I'll be able to see how many questions are in each category, and display that back to users. If a category has 'child' categories, I will need a way of displaying a cumulative counter of all sub-categories. Accurate tallies are fairly important, but not mission critical. I do not mind using sharded counters. My question is, how should I design this so that it will adopt GAE denormalization, and maintain optimization?

I was thinking of having a Category class, with a ListProperty in each, which will represent the ancestor tree. It will contain a key to each parent entity in the tree, in order. But, should I also specify a parent when constructing the entities, or is that not needed in this case? I'm thinking that I may have to run my counter updates in transaction, which is why I am considering a parent-child relationship.

Or perhaps there is more optimized way of designing my relationships that will still allow me to keep fairly accurate counters of all questions in each category. Thanks in advance for any help.

Answer 1

I'm not that familiar with Google App Engine, but here are some thoughts. First is to consider if "tags" are more appropriate than category & sub categories. Will their be a rigid 2 level category scheme? Will all items have a main and subcategory assignment?

Rather than having a class for each category, have you considered a CategoryList class that would have a incrementCategoryByName(str name) method? The class contain a dictionary of classes without having to have the overhead of a class for each category.

Answer 2

This isn't as complicated as you might think. Here's a Category class:

class Category(db.Model):
    title = db.StringProperty()
    subcategories = db.ListProperty(db.Key)
    quizzes = db.ListProperty(db.Key)

    def add_sub_category(self, title):
        new_category = Category(title)
        new_category.put()
        self.subcategories.append(new_category)
        self.put()

        return new_category

By keeping both the subcategories and quizzes that are assocaited with this Category in a ListProperty, getting a count of them is as simple as using the len() operator.

You could use it something like this:

main_category = Category("Main")
main_category.put()

sports_category = main_category.add_sub_category("Sports")
baseball_category = sports_category.add_sub_category("Baseball")
football_category = sports_category.add_sub_category("Football")
hockey_category = sports_category.add_sub_category("Hockey")

tv_category = main_category.add_sub_category("TV")

...etc...