Should I use JSONField over ForeignKey to store data?

Question

I'm facing a dilemma, I'm creating a new product and I would not like to mess up the way I organise the informations in my database.

I have these two choices for my models, the first one would be to use foreign keys to link my them together.

Class Page(models.Model):
    data = JsonField()

Class Image(models.Model):
    page = models.ForeignKey(Page)
    data = JsonField()

Class Video(models.Model):
    page = models.ForeignKey(Page)
    data = JsonField()

etc...

The second is to keep everything in Page's JSONField:

Class Page(models.Model):
    data = JsonField() # videos and pictures, etc... are stored here

Is one better than the other and why? This would be a huge help on the way I would organize my databases in the futur.

I thought maybe the second option could be slower since everytime something changes all the json would be overridden, but does it make a huge difference or is what I am saying false?

Answer 1

A JSONField obfuscates the underlying data, making it difficult to write readable code and fully use Django's built-in ORM, validations and other niceties (ModelForms for example). While it gives flexibility to save anything you want to the db (e.g. no need to migrate the db when adding new fields), it takes away the clarity of explicit fields and makes it easy to introduce errors later on.

For example, if you start saving a new key in your data and then try to access that key in your code, older objects won't have it and you might find your app crashing depending on which object you're accessing. That can't happen if you use a separate field.

I would always try to avoid it unless there's no other way.

Typically I use a JSONField in two cases:

• To save a response from 3rd party APIs (e.g. as an audit trail)
• To save references to archived objects (e.g. when the live products in my db change but I still have orders referencing the product).

If you use PostgreSQL, as a relational database, it's optimised to be super-performant on JOINs so using ForeignKeys is actually a good thing. Use select_related and prefetch_related in your code to optimise the number of queries made, but the queries themselves will scale well even for millions of entries.