Model with many relationships

Question

I have a model like this:

class Property(models.Model):
    rsa = models.ForeignKey(settings.AUTH_USER_MODEL) #Real State Agency
    agents = models.ManyToManyField(Agent)
    title = models.CharField(max_length=250, blank=False, null=False, verbose_name=_('Title'))
    slug = AutoSlugField(populate_from='title', unique=True, db_index=True)
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)
    country = models.ForeignKey(PropertyCountry, null=False)
    state = models.ForeignKey(PropertyState, null=False)
    city = models.ForeignKey(PropertyCity, null=False)
    neighborhood = models.CharField(max_length=250, default='')
    neighborhood_alias = models.CharField(max_length=250, blank=True, default='')
    address = models.CharField(max_length=150, blank=True, default='')
    phone = models.CharField(max_length=20, default='')
    contact_email = models.EmailField(max_length=250, default='')
    price = models.IntegerField(default=0, null=False)
    price_admin = models.IntegerField(default=0, null=False)
    area = models.IntegerField(default=0, null=False)
    private_area = models.IntegerField(blank=True, default=0, null=False)
    bedrooms = models.IntegerField(blank=True, default=0, null=False)
    bathrooms = models.IntegerField(blank=True, default=0, null=False)
    property_type = models.ForeignKey(PropertyType, null=True)
    type_offer = models.ForeignKey(OfferType, null=True)
    status = models.ForeignKey(Status, blank=True, null=True)
    antiquity = models.ForeignKey(YearsOld, blank=True, null=True)
    number_floors = models.IntegerField(default=1, blank=False, null=False)
    state_property = models.ForeignKey(StateProperty, null=True)
    comment = models.TextField(blank=True, default='', verbose_name=_('Comment'))
    parking_spaces = models.PositiveSmallIntegerField(blank=True, default=0, null=False)
    weather = models.ForeignKey(WeatherType, blank=True, null=True)
    stratus = models.IntegerField(default=-1, blank=True, null=False)
    address_view = models.ForeignKey(AddressView, null=True)

Where most of those foreign keys are something like:

class PropertyType(models.Model):
    name = models.CharField(max_length=50, default='')
    id_something_a = models.IntegerField(default=-1, blank=False, null=False)
    id_something_b = models.IntegerField(default=-1, blank=False, null=False)

As you can see, a lot of the fields are either foreign key or many to many. If i want to retrieve all the data for my property, i would need a lot of joins for that, which i think could get slow eventually.

As an alternative to this, in this case, i can use choice fields and map the values in python (the values in those foreign keys don't change often).

My questions are:

• Is there another way to model this while reducing the performance penalties that can arise?
• Is there any need to be concerned about this? (penalty not big enough, not enough foreign keys/joins to cause something significant, etc)

Answer 1

I like to keep everything as normalized as possible until there are pains. Then I optimize for major use cases, de-normalizing as needed. Don't forget you can throw more hardware at the problem and see nice results. However, that's truly a band-aid.

If your joins are between massive tables, then there's a reason to be concerned. I would try to avoid having to fetch all of the data at once. Make your queries smaller to keep the query time lower. However, if you're doing a data dump, it's going to take time.

Basically my approach is keep things architecturally sound until they become a problem. Then optimize where it's needed, make things load in chunks, and increase RAM/CPU/Hardware. I've used this approach on datasets around the 10GB range where there are 6+ tables in the millions. I've never had a table with more than 50 million, so I can't speak to huge datasets.