Designing a Tag table that tells how many times it's used

Question

Hi, all.

I am trying to design a tagging system with a model like this:

Tag:
   content = CharField
   creator = ForeignKey
   used = IntergerField

It is a many-to-many relationship between tags and what's been tagged.

Everytime I insert a record into the assotication table, Tag.used is incremented by one, and decremented by one in case of deletion.

Tag.used is maintained because I want to speed up answering the question 'How many times this tag is used?'.

However, this seems to slow insertion down obviously.

Please tell me how to improve this design.

Thanks in advance.

Answer 1

I don't think it's a good idea to denormalize your data like that.

I think a more elegant solution is to use django aggregation to track how many times the tag has been used http://docs.djangoproject.com/en/dev/topics/db/aggregation/

You could attach the used count to your tag object by calling something like this:

my_tag = Tag.objects.annotate(used=Count('post'))[0]

and then accessing it like this:

my_tag.used

assuming that you have a Post model class that has a ManyToMany field to your Tag class

You can order the Tags by the named annotated field if needed:

Tag.objects.annotate(used=Count('post')).order_by('-used')

Answer 2

If your database support materialized indexed views then you might want to create one for this. You can get a large performance boost for frequently run queries that aggregate data, which I think you have here.

your view would be on a query like:

SELECT
    TagID,COUNT(*)
    FROM YourTable
    GROUP BY TagID

The aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.