CODEWITHSUNDEEP
pythonsqldjango
gskoljarev
gskoljarev

Reputation: 71

Optimal way to store a type of a Django model and efficient read from a SQL database

I have a model design question concerning storing a type of a model. Here is an example:

class Building(models.Model)
    id = models.AutoField(primary_key=True)
    address = models.CharField()

class Bar(models.Model)
    id = models.AutoField(primary_key=True)
    building = models.ForeignKey(Building)
    seats = models.Integer()
    waiters = models.Integer()

class Bakery(models.Model)
    id = models.AutoField(primary_key=True)
    building = models.ForeignKey(Building)
    sells_beverages = models.Boolean(default=False)

class Toilet(models.Model)
    id = models.AutoField(primary_key=True)
    building = models.ForeignKey(Building)
    has_air_dryer = models.Boolean(default=False)

For example, a Building can have a Bar & a Toilet, but doesn't have a Bakery. Depending on what is in the Building, I would like to store a type of Building, which would describe what it has. The idea is also to limit the number of lookups in the database, so if Building only has a Toilet (which we can see from its type), we don't have to have another go at the database to check if Bar or Bakery exist in the Building.

This would define the types explicitly, but the issue may be with adding columns later.

class Building(models.Model)
    id = models.AutoField(primary_key=True)
    address = models.CharField()
    bar = models.Boolean(default=False)
    bakery = models.Boolean(default=False)
    toilet = models.Boolean(default=False)

This would define explicitly whether the establishment exists in the Building:

class Bar(models.Model)
    ...
    bar = models.Boolean(default=False)

class Bakery(models.Model)
    ...
    bakery = models.Boolean(default=False)

class Toilet(models.Model)
    ...
    bakery = models.Boolean(default=False)

This would add a type code, which would have to be defined somewhere else in the code or another table. For instance, type code 3 means a Building has a Bar and a Bakery, but no Toilet.

class Building(models.Model)
    id = models.AutoField(primary_key=True)
    address = models.CharField()
    type = models.Integer()

These are the approaches I thought about, but I am not sure which one is optimal. Also, avoiding having to make 4 queries per building is a must, as that would slow things down when getting a big list of buildings.

I looked into select_related & prefetch_related, but select_related doesn't do reverse foreign key relationships, only followups. And prefetch_related, from what I gather, would make separate query for each possible establishment.

I hope you can point me in the right direction about the issue.

Thank you.

Upvotes: 1

Views: 132

Answers (1)

Oded Har-Tal
Oded Har-Tal

Reputation: 163

Options 1 is the most expensive one, since it requires 3 different SQL join searches (i.e join of Building table with each of the establishments). It also implies that a single building can have more than one establishment from each type.

Option 2 is the cheapest one - after retrieving the building objects, no more queries are required. This option is best if you only want to look for the existence of the establishment.

The last option is a little cumbersome, since it requires to have a different type of each establishment permutation (in your example it's 9 options).

Another option to consider is to have an Establishment model with a type field stating what it is:

class Establishment(models.Model)
    id = models.AutoField(primary_key=True)
    building = models.ForeignKey(Building)
    type = models.CharField(max_length=64)

This will allow a single query to get a building together with it's establishments (join operation), however you cannot have additional per-establishment properties.

Upvotes: 1

Related Questions