[Django]-Should I avoid multi-table (concrete) inheritance in Django by any means?

The world has changed.

The first thing to note is that the article titled Django gotcha: concrete inheritance was nearly four years old at the time this question was asked; in 2014. Both Django and RDBMs systems have come a long way since then (example mysql 5.0 or 5.1 were the widely used versions and 5.5 general availability was still one month away).

Joins to my left, joins to my right

It is true that multi table inheritance does result in extra joins behind the scenes most of the time. But joins are not evil. It’s worth noting that in a properly normalized database, you almost always have to join to fetch all the required data. When proper indexes are used, joins do not include any significant performance penalties.

INNER JOIN vs LEFT OUTER JOIN

This is indeed the case against multi table inheritance, with other approaches it’s possible to avoid a costly LEFT OUTER JOIN and do an INNER JOIN instead or perhaps a subquery. But with multi table inheritance you are denied that choice

From what I understand, you are using OneToOneField on the RelatedModel to the BaseModel because ultimately, you want a one-to-one link between RelatedModel and each Submodel1 to Submodel9. If so, there’s a more efficient way of doing that without multi-table inheritance nor generic relations.

Just get rid of the BaseModel and in each SubmodelX, have a OneToOneField to RelatedModel

class Submodel1(models.Model):
    related_model = models.OneToOneField(RelatedModel, null=True, blank=True, related_name='the_thing')
    some_field = models.TextField()

# ...

class Submodel9(models.Model):
    related_model = models.OneToOneField(RelatedModel, null=True, blank=True, related_name='the_thing')
    another_field = models.TextField()

This would allow you to access SubmodelX from an instance of RelatedModel using a field named the_thing, just as in the multi-table inheritance example you first gave.

Note that you may use abstract inheritance to factor out the related_model field and any other common fields between SubModel1 to Submodel9.

The reason using multi-table inheritance is inefficient is because it generates an extra table for the base model, and therefore extra JOINs to access those fields. Using generic relations would be more efficient if you later find that you instead need a ForeignKey field from RelatedModel to each SubmodelX. However, Django does not support generic relations in select_related() and you might have to end up building your own queries to do so efficiently. The tradeoff between performance and ease of coding is up to you depending on how much load you expect on the server and how much time you want to spend optimizing.

Whether the occurrence of LEFT OUTER JOIN is an issue in itself, I cannot say, but, in any case, it may be interesting to note in which cases these outer joins actually occur.

This is a naive attempt to illustrate the above, using some example queries.

Suppose we have some models using multi-table inheritance as follows:

from django.db import models

class Parent(models.Model):
    parent_field = models.CharField(max_length=10)


class ChildOne(Parent):
    child_one_field = models.CharField(max_length=10)


class ChildTwo(Parent):
    child_two_field = models.CharField(max_length=10)

By default, the child instances get a parent_ptr and parent instances can access child objects (if they exist) using childone or childtwo. Note that parent_ptr represents a one-to-one relation which is used as the primary key (the actual child tables have no id column).

Here’s a quick-and-dirty unit test with some naive Django query examples, showing the corresponding number of occurrences of INNER JOIN and OUTER JOIN in the SQL:

import re
from django.test import TestCase
from inheritance.models import (Parent, ChildOne, ChildTwo)

def count_joins(query, inner_outer):
    """ Count the occurrences of JOIN in the query """
    return len(re.findall('{} join'.format(inner_outer), str(query).lower()))


class TestMultiTableInheritance(TestCase):
    def test_queries(self):
        # get children (with parent info)
        query = ChildOne.objects.all().query
        self.assertEqual(1, count_joins(query, 'inner'))
        self.assertEqual(0, count_joins(query, 'outer'))
        # get parents
        query = Parent.objects.all().query
        self.assertEqual(0, count_joins(query, 'inner'))
        self.assertEqual(0, count_joins(query, 'outer'))
        # filter children by parent field
        query = ChildOne.objects.filter(parent_field=parent_value).query
        self.assertEqual(1, count_joins(query, 'inner'))
        self.assertEqual(0, count_joins(query, 'outer'))
        # filter parents by child field
        query = Parent.objects.filter(childone__child_one_field=child_value).query
        self.assertEqual(1, count_joins(query, 'inner'))
        self.assertEqual(0, count_joins(query, 'outer'))
        # get child field values via parent
        query = Parent.objects.values_list('childone__child_one_field').query
        self.assertEqual(0, count_joins(query, 'inner'))
        self.assertEqual(1, count_joins(query, 'outer'))
        # get multiple child field values via parent
        query = Parent.objects.values_list('childone__child_one_field',
                                           'childtwo__child_two_field').query
        self.assertEqual(0, count_joins(query, 'inner'))
        self.assertEqual(2, count_joins(query, 'outer'))
        # get child-two field value from child-one, through parent
        query = ChildOne.objects.values_list('parent_ptr__childtwo__child_two_field').query
        self.assertEqual(1, count_joins(query, 'inner'))
        self.assertEqual(1, count_joins(query, 'outer'))
        # get parent field value from parent, but through child
        query = Parent.objects.values_list('childone__parent_field').query
        self.assertEqual(0, count_joins(query, 'inner'))
        self.assertEqual(2, count_joins(query, 'outer'))
        # filter parents by parent field, but through child
        query = Parent.objects.filter(childone__parent_field=parent_value).query
        self.assertEqual(2, count_joins(query, 'inner'))
        self.assertEqual(0, count_joins(query, 'outer'))

Note, not all of these queries make sense: they are just for illustrative purposes.

Also note that this test code is not DRY, but that is on purpose.

Django implements multi-table inheritance via an automatically-created OneToOneField as its docs says.So either use abstract inheritance or I don’t think using an explicit OneToOneFields or ForeignKeys makes any differences.