Should I use JSONField over ForeignKey to store data?
up vote
2
down vote
favorite
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?
django django-models
asked Nov 20 at 16:35
Nuri Katsuki
1,91562553
|
show 4 more comments
up vote
2
down vote
favorite
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?
django django-models
asked Nov 20 at 16:35
Nuri Katsuki
1,91562553
Putting everything in a JSONField obfuscates a lot of the functionality and isn't recommended. It makes it more difficult to validate your data since anything can be stored in the JSON. And it makes it more difficult to extend your models with custom methods (maybe a Video objects wants to be able to display its duration, in which case you'd want to define aduration()method onVideo).
– dirkgroten
Nov 20 at 16:44
And by the way, where are you planning to store the actual binary blob of yourImageandVideo? I hope not in thedatafield, databases aren't good at that!
– dirkgroten
Nov 20 at 16:45
@dirkgroten This is just an example, but it doesn't have to be videos and image could be pizza and toppings, etc... mostly it's only texts with restriction of 155 characters
– Nuri Katsuki
Nov 20 at 16:45
Django has aURLFieldtype which could allow you to store thelinkas an explicit field. Again, this makes validation much easier, otherwise you have to validate it all yourself inside the JSON.
– dirkgroten
Nov 20 at 16:47
1
Relational databases are optimised for JOIN between tables so in terms of performance and scalability you're better off using ForeignKeys. PostgreSQL's JSONField isn't that performant actually, don't use it to mimic a real non-relational db like mongo or cassandra. It's really meant for cases where you have some undetermined data to store, like serialised responses from external APIs or a serialised reference to a historical python object.
– dirkgroten
Nov 20 at 16:54
|
show 4 more comments
up vote
2
down vote
favorite
up vote
2
down vote
favorite
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?
django django-models
asked Nov 20 at 16:35
Nuri Katsuki
1,91562553
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?
django django-models
django django-models
asked Nov 20 at 16:35
Nuri Katsuki
1,91562553
asked Nov 20 at 16:35
Nuri Katsuki
1,91562553
edited Nov 20 at 16:51
asked Nov 20 at 16:35
Nuri Katsuki
1,91562553
asked Nov 20 at 16:35
Nuri Katsuki
1,91562553
asked Nov 20 at 16:35
Nuri Katsuki
1,91562553
1,91562553
Putting everything in a JSONField obfuscates a lot of the functionality and isn't recommended. It makes it more difficult to validate your data since anything can be stored in the JSON. And it makes it more difficult to extend your models with custom methods (maybe a Video objects wants to be able to display its duration, in which case you'd want to define aduration()method onVideo).
– dirkgroten
Nov 20 at 16:44
And by the way, where are you planning to store the actual binary blob of yourImageandVideo? I hope not in thedatafield, databases aren't good at that!
– dirkgroten
Nov 20 at 16:45
@dirkgroten This is just an example, but it doesn't have to be videos and image could be pizza and toppings, etc... mostly it's only texts with restriction of 155 characters
– Nuri Katsuki
Nov 20 at 16:45
Django has aURLFieldtype which could allow you to store thelinkas an explicit field. Again, this makes validation much easier, otherwise you have to validate it all yourself inside the JSON.
– dirkgroten
Nov 20 at 16:47
1
Relational databases are optimised for JOIN between tables so in terms of performance and scalability you're better off using ForeignKeys. PostgreSQL's JSONField isn't that performant actually, don't use it to mimic a real non-relational db like mongo or cassandra. It's really meant for cases where you have some undetermined data to store, like serialised responses from external APIs or a serialised reference to a historical python object.
– dirkgroten
Nov 20 at 16:54
|
show 4 more comments
Putting everything in a JSONField obfuscates a lot of the functionality and isn't recommended. It makes it more difficult to validate your data since anything can be stored in the JSON. And it makes it more difficult to extend your models with custom methods (maybe a Video objects wants to be able to display its duration, in which case you'd want to define aduration()method onVideo).
– dirkgroten
Nov 20 at 16:44
And by the way, where are you planning to store the actual binary blob of yourImageandVideo? I hope not in thedatafield, databases aren't good at that!
– dirkgroten
Nov 20 at 16:45
@dirkgroten This is just an example, but it doesn't have to be videos and image could be pizza and toppings, etc... mostly it's only texts with restriction of 155 characters
– Nuri Katsuki
Nov 20 at 16:45
Django has aURLFieldtype which could allow you to store thelinkas an explicit field. Again, this makes validation much easier, otherwise you have to validate it all yourself inside the JSON.
– dirkgroten
Nov 20 at 16:47
1
Relational databases are optimised for JOIN between tables so in terms of performance and scalability you're better off using ForeignKeys. PostgreSQL's JSONField isn't that performant actually, don't use it to mimic a real non-relational db like mongo or cassandra. It's really meant for cases where you have some undetermined data to store, like serialised responses from external APIs or a serialised reference to a historical python object.
– dirkgroten
Nov 20 at 16:54
Putting everything in a JSONField obfuscates a lot of the functionality and isn't recommended. It makes it more difficult to validate your data since anything can be stored in the JSON. And it makes it more difficult to extend your models with custom methods (maybe a Video objects wants to be able to display its duration, in which case you'd want to define a
duration() method on Video).– dirkgroten
Nov 20 at 16:44
Putting everything in a JSONField obfuscates a lot of the functionality and isn't recommended. It makes it more difficult to validate your data since anything can be stored in the JSON. And it makes it more difficult to extend your models with custom methods (maybe a Video objects wants to be able to display its duration, in which case you'd want to define a
duration() method on Video).– dirkgroten
Nov 20 at 16:44
And by the way, where are you planning to store the actual binary blob of your
Image and Video? I hope not in the data field, databases aren't good at that!– dirkgroten
Nov 20 at 16:45
And by the way, where are you planning to store the actual binary blob of your
Image and Video? I hope not in the data field, databases aren't good at that!– dirkgroten
Nov 20 at 16:45
@dirkgroten This is just an example, but it doesn't have to be videos and image could be pizza and toppings, etc... mostly it's only texts with restriction of 155 characters
– Nuri Katsuki
Nov 20 at 16:45
@dirkgroten This is just an example, but it doesn't have to be videos and image could be pizza and toppings, etc... mostly it's only texts with restriction of 155 characters
– Nuri Katsuki
Nov 20 at 16:45
Django has a
URLField type which could allow you to store the link as an explicit field. Again, this makes validation much easier, otherwise you have to validate it all yourself inside the JSON.– dirkgroten
Nov 20 at 16:47
Django has a
URLField type which could allow you to store the link as an explicit field. Again, this makes validation much easier, otherwise you have to validate it all yourself inside the JSON.– dirkgroten
Nov 20 at 16:47
1
1
Relational databases are optimised for JOIN between tables so in terms of performance and scalability you're better off using ForeignKeys. PostgreSQL's JSONField isn't that performant actually, don't use it to mimic a real non-relational db like mongo or cassandra. It's really meant for cases where you have some undetermined data to store, like serialised responses from external APIs or a serialised reference to a historical python object.
– dirkgroten
Nov 20 at 16:54
Relational databases are optimised for JOIN between tables so in terms of performance and scalability you're better off using ForeignKeys. PostgreSQL's JSONField isn't that performant actually, don't use it to mimic a real non-relational db like mongo or cassandra. It's really meant for cases where you have some undetermined data to store, like serialised responses from external APIs or a serialised reference to a historical python object.
– dirkgroten
Nov 20 at 16:54
|
show 4 more comments
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
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 your 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.
answered Nov 20 at 17:08
dirkgroten
3,91511121
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53397508%2fshould-i-use-jsonfield-over-foreignkey-to-store-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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 your 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.
answered Nov 20 at 17:08
dirkgroten
3,91511121
add a comment |
up vote
1
down vote
accepted
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 your 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.
answered Nov 20 at 17:08
dirkgroten
3,91511121
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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 your 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.
answered Nov 20 at 17:08
dirkgroten
3,91511121
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 your 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.
answered Nov 20 at 17:08
dirkgroten
3,91511121
answered Nov 20 at 17:08
dirkgroten
3,91511121
answered Nov 20 at 17:08
dirkgroten
3,91511121
answered Nov 20 at 17:08
dirkgroten
3,91511121
3,91511121
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53397508%2fshould-i-use-jsonfield-over-foreignkey-to-store-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Putting everything in a JSONField obfuscates a lot of the functionality and isn't recommended. It makes it more difficult to validate your data since anything can be stored in the JSON. And it makes it more difficult to extend your models with custom methods (maybe a Video objects wants to be able to display its duration, in which case you'd want to define a
duration()method onVideo).– dirkgroten
Nov 20 at 16:44
And by the way, where are you planning to store the actual binary blob of your
ImageandVideo? I hope not in thedatafield, databases aren't good at that!– dirkgroten
Nov 20 at 16:45
@dirkgroten This is just an example, but it doesn't have to be videos and image could be pizza and toppings, etc... mostly it's only texts with restriction of 155 characters
– Nuri Katsuki
Nov 20 at 16:45
Django has a
URLFieldtype which could allow you to store thelinkas an explicit field. Again, this makes validation much easier, otherwise you have to validate it all yourself inside the JSON.– dirkgroten
Nov 20 at 16:47
1
Relational databases are optimised for JOIN between tables so in terms of performance and scalability you're better off using ForeignKeys. PostgreSQL's JSONField isn't that performant actually, don't use it to mimic a real non-relational db like mongo or cassandra. It's really meant for cases where you have some undetermined data to store, like serialised responses from external APIs or a serialised reference to a historical python object.
– dirkgroten
Nov 20 at 16:54