I am developing a django application to help keep track of macro- and micronutrients in a diet. So far, I am super happy with the django documentation and was able to resolve most of my issues. But right now I am stuck on a more conceptual question related to databases and model relationships.
My current model for an Ingredient and the associated NutrientProfile (that stores the nutrients) looks like this:
# models.py
class Ingredient(models.Model):
ingredient_id = models.UUIDField(
verbose_name="Ingredient",
primary_key=True,
default=uuid.uuid4,
editable=False,
)
name = models.CharField(max_length=300, unique=False)
synonyms = models.CharField(max_length=300, blank=True, null=True)
category = models.CharField(max_length=300, blank=True, null=True)
class NutrientProfile(models.Model):
nutrientProfileID = models.UUIDField(
primary_key=True, default=uuid.uuid4, editable=False,
)
# Ingredient the NutrientProfile is associated to
ingredient = models.ForeignKey(
to="Ingredient",
related_name='nutrientProfile',
on_delete=models.CASCADE,
blank=True,
null=True,
)
fat_total = models.DecimalField(max_digits=7, decimal_places=4, blank=True, null=True)
protein = models.DecimalField(max_digits=7, decimal_places=4, blank=True, null=True)
cholesterol = models.DecimalField(max_digits=7, decimal_places=4, blank=True, null=True)
... more nutrients
For my DRF API-ViewSet I use prefetch_related() to reduce the DB-queries like this:
# views.py
class IngredientViewSet(viewsets.ReadOnlyModelViewSet):
# Activate the paginator
serializer_class = IngredientSerializer
queryset = Ingredient.objects.all().prefetch_related('nutrientProfile')
search_fields = ["name", "synonyms"]
filter_backends = [filters.SearchFilter]
So currently I am able to create Ingredients, and associate them with one or more NutrientProfiles. I did not define this relation as One-to-One, because there may be multiple NutrientProfiles for a single ingredient (multiple data sources).
Now my question:
I realized, that I will need some more information on each nutrient, such as the unit, a comment, date added, etc., so I came up with the following Nutrient model to store more details.
class NutrientProfile(models.Model):
NutrientProfileID = ... ID
ingredient = ... FK
fat_total = models.OneToOneField("Nutrient", on_delete=models.CASCADE)
protein = models.OneToOneField("Nutrient", on_delete=models.CASCADE)
cholesterol = models.OneToOneField("Nutrient", on_delete=models.CASCADE)
... more nutrients
class Nutrient(models.Model):
amount = models.DecimalField(max_digits=7, decimal_places=4, blank=True, null=True)
unit = models.CharField(max_length=5, choices=allowed_units)
comment = models.CharField(max_length=200)
...
So each field in NutrientProfile can have all necessary details stored in the Nutrient model. Does this make sense, or am I going in the wrong direction here? Would it be better to store all the details in the NutrientProfile itself? Like so?
class NutrientProfile(models.Model):
NutrientProfileID = ... ID
ingredient = ... FK
fat_total = models.OneToOneField("Nutrient", on_delete=models.CASCADE)
fat_total_amount = ...
fat_total_comment = ...
fat_total_unit = ...
protein = models.OneToOneField("Nutrient", on_delete=models.CASCADE)
protein_amount = ...
... more nutrients
Defining all details together, seems very ugly to me. But putting them in another table will increase the âworkâ for the database, right? For 10.000 NutrientProfiles with each 30 nutrients, the Nutrient table would already have 300.000 rows. Wouldnât that be far more inefficient than simply storing more columns in the NutrientProfile table? I am using PostgreSQL as my Database right now.
A, in my opinion, more flexible way of dealing with the details would be to define the NutrientProfile and Nutrient models like this:
class NutrientProfile(models.Model):
NutrientProfileID = ... ID
ingredient = ... FK
# No defined nutrient fields
class Nutrient(models.Model):
nutrient_profile = models.ForeignKey('NutrientProfile', on_delete=models.CASCADE, null=False)
# Nutrient type. For example: fat, protein, cholesterol,...
# Maybe limited with predefined choices?
nutrient_type = models.CharField(max_length=50, choices=??)
amount = models.DecimalField(max_digits=7, decimal_places=4, blank=True, null=True)
unit = models.CharField(max_length=5, choices=allowed_units)
comment = models.CharField(max_length=200)
... more details
class Meta:
unique_together = ['nutrient_profile', 'nutrient_type']
However, my question remains the same. Is it preferrable to store more fields on a single model, or should I split up my models, even tough the resulting detail table will have more rows? How does that impact the SQL performance? Is nesting models like this considered bad practice?
And where do you draw the line in making more detailed models? For example in the Nutrient model, I could make a Unit and Comment model etcâŚ
I would also be interested how you would approach such a problem in general! For example a Car model that is a little more detailed than the one in the Django documentation:
-
Car => a model for a single car (not type of car)
- some detail fields like âdate_manufacturedâ, âis_automaticâ,âŚ
- Manufacturer FK
- has many Component groups
-
Component Group => e.g.: âTiresâ, âEngineâ, âACâ,âŚ
- Limited to 200 different Component Groups via choices
- some detail fields like âdate_manufactureâ, âdate_installedâ, âŚ
- has many Included Parts
-
Included Parts => e.g.: âScrewsâ, âNutsâ, âGasketâ, âŚ
- some detail fields
- has many Manufacturers
-
Manufacturer => e.g.: some companies that produce screws / gaskets, etc.
- some detail fields
- has many employees
-
Employee
- detail fields about employee
- Name
So if I have an instance of a Car model and want to know the names of all employees that worked on it, I would have to specify my queryset like this:
queryset = Car.objects.all().prefetch_related('ComponentGroup').prefetch_related('IncludedParts').prefetch_related('manufacturer').prefetch_related('Employee')
Isnât that a very expensive query? How would you go about modeling this? What are your thoughts about storing very detailes models (lots of fields) vs having many models (lot of tables)? Pro/Cons? Should you store details in the through model?
I am sorry if this has become a bit long, so,
TLDR: Whatâs your approach for storing large/detailed models? Split up into multiple models, or keep in one model?
Thank you in advance 