Optimal way(performance wise) to store array like data in MySQLWhat would be the best way to design a table...
Do the speed limit reductions due to pollution also apply to electric cars in France?
How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?
How can I prevent an oracle who can see into the past from knowing everything that has happened?
How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?
How would an EMP effect spacesuits (and small-arms weapons)?
Can you say "leftside right"?
Isn't a semicolon (';') needed after a function declaration in C++?
Is it really OK to use "because of"?
How unreachable are Jupiter's moons from Mars with the technology developed for going to Mars?
Renting a 2CV in France
Was Opportunity's last message to Earth "My battery is low and it's getting dark"?
If I tried and failed to start my own business, how do I apply for a job without job experience?
Coworker asking me to not bring cakes due to self control issue. What should I do?
How do I draw a function along with a particular tangent line at a specific point?
Is "accuse people to be racist" grammatical?
How can guns be countered by melee combat without raw-ability or exceptional explanations?
Would water spill from a bowl in a Bag of Holding?
What's the reason that we have different quantities of days each month?
Did ancient Germans take pride in leaving the land untouched?
Using Ansible, how can I take actions on each file in a specific location?
Can I legally make a website about boycotting a certain company?
How can I handle players killing my NPC outside of combat?
Disk space full during insert, what happens?
Is layered encryption more secure than long passwords?
Optimal way(performance wise) to store array like data in MySQL
What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?MySQL Performance Gains By Splitting Tables?Dealing with data stored as arrays in a MySQL DBManaging constants changes to databaseHow to improve this product rental schema?Mysql query performanceMysql pagination without offsetDatabase schema design for individual configurationsQuotation and invoice mysql tableStoring Arbitrarily Ordered Heirarchal Data in MySQL
I am working on a website(PHP), and one of the features that the site should have is ordering items from the website, and the order will be like an array with an architecture similar to this:
order(
item1(item id, quantity)
item2(item id, quantity))
And i want to know what is the optimal way to store the order, i came up with two ways to do that,
The first way which seems the easiest and simplest to me is by Using json_encode() or serialize() and storing the values in a single cell in the same table as the rest of the order information,
The second way is to make a new table with three columns, one for the order id, the second for the item id and the third for the quantity, so this way if the user submits an order with * number of items i will have * number of rows for the order in the order items table.
When searching about the best way to do that i discovered the the first way is basically blasphemy in an SQL database and that the second way is the correct way, but is it really applicable for all cases, i mean in my case i don't foresee a situation where i wont request all of the order or need queries to be done on an individual item in an order, I am leaning to chose the first way and blame it on Occam's razor, since for my website it seems like the simplest answer, is there something that i am not seeing(I mean i admittedly have little experience in coding let alone databases) which explains all the hate for this way? performance wise i mean.
mysql php array
asked Aug 18 '16 at 7:56
Ahmed AlHallakAhmed AlHallak
1
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I am working on a website(PHP), and one of the features that the site should have is ordering items from the website, and the order will be like an array with an architecture similar to this:
order(
item1(item id, quantity)
item2(item id, quantity))
And i want to know what is the optimal way to store the order, i came up with two ways to do that,
The first way which seems the easiest and simplest to me is by Using json_encode() or serialize() and storing the values in a single cell in the same table as the rest of the order information,
The second way is to make a new table with three columns, one for the order id, the second for the item id and the third for the quantity, so this way if the user submits an order with * number of items i will have * number of rows for the order in the order items table.
When searching about the best way to do that i discovered the the first way is basically blasphemy in an SQL database and that the second way is the correct way, but is it really applicable for all cases, i mean in my case i don't foresee a situation where i wont request all of the order or need queries to be done on an individual item in an order, I am leaning to chose the first way and blame it on Occam's razor, since for my website it seems like the simplest answer, is there something that i am not seeing(I mean i admittedly have little experience in coding let alone databases) which explains all the hate for this way? performance wise i mean.
mysql php array
asked Aug 18 '16 at 7:56
Ahmed AlHallakAhmed AlHallak
1
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I am working on a website(PHP), and one of the features that the site should have is ordering items from the website, and the order will be like an array with an architecture similar to this:
order(
item1(item id, quantity)
item2(item id, quantity))
And i want to know what is the optimal way to store the order, i came up with two ways to do that,
The first way which seems the easiest and simplest to me is by Using json_encode() or serialize() and storing the values in a single cell in the same table as the rest of the order information,
The second way is to make a new table with three columns, one for the order id, the second for the item id and the third for the quantity, so this way if the user submits an order with * number of items i will have * number of rows for the order in the order items table.
When searching about the best way to do that i discovered the the first way is basically blasphemy in an SQL database and that the second way is the correct way, but is it really applicable for all cases, i mean in my case i don't foresee a situation where i wont request all of the order or need queries to be done on an individual item in an order, I am leaning to chose the first way and blame it on Occam's razor, since for my website it seems like the simplest answer, is there something that i am not seeing(I mean i admittedly have little experience in coding let alone databases) which explains all the hate for this way? performance wise i mean.
mysql php array
asked Aug 18 '16 at 7:56
Ahmed AlHallakAhmed AlHallak
1
I am working on a website(PHP), and one of the features that the site should have is ordering items from the website, and the order will be like an array with an architecture similar to this:
order(
item1(item id, quantity)
item2(item id, quantity))
And i want to know what is the optimal way to store the order, i came up with two ways to do that,
The first way which seems the easiest and simplest to me is by Using json_encode() or serialize() and storing the values in a single cell in the same table as the rest of the order information,
The second way is to make a new table with three columns, one for the order id, the second for the item id and the third for the quantity, so this way if the user submits an order with * number of items i will have * number of rows for the order in the order items table.
When searching about the best way to do that i discovered the the first way is basically blasphemy in an SQL database and that the second way is the correct way, but is it really applicable for all cases, i mean in my case i don't foresee a situation where i wont request all of the order or need queries to be done on an individual item in an order, I am leaning to chose the first way and blame it on Occam's razor, since for my website it seems like the simplest answer, is there something that i am not seeing(I mean i admittedly have little experience in coding let alone databases) which explains all the hate for this way? performance wise i mean.
mysql php array
mysql php array
asked Aug 18 '16 at 7:56
Ahmed AlHallakAhmed AlHallak
1
asked Aug 18 '16 at 7:56
Ahmed AlHallakAhmed AlHallak
1
asked Aug 18 '16 at 7:56
Ahmed AlHallakAhmed AlHallak
1
asked Aug 18 '16 at 7:56
Ahmed AlHallakAhmed AlHallak
1
asked Aug 18 '16 at 7:56
Ahmed AlHallakAhmed AlHallak
1
1
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
If you just want somewhere to dump JSON then you're wasting your time with MySQL. Use a simple docustore. You will lose the ability to query the dataset in any meaningful way, but if storage and retrieval is your only requirement, there's no need for a relational database.
If you have more complex requirements, then you will be far better off using a relational database. e.g. You need to know how many items of type X are needed to fulfill all open orders; You need to know how many open orders currently exist for a specific customer, and what the total value of those orders is, etc etc. Using a docustore and JSON will severely limit your ability to answer questions about your dataset.
Finally, you're describing a many-to-many relationship between orders and items, right? One order can consist of multiple items, and one item can be a part of multiple orders, yes? In this case you need three tables - orders, items and an intermediate table to join them: orderitems. This design is what we call "normalised" - it will allow you easily query the data while minimising the amount of data stored.
answered Aug 18 '16 at 15:05
Andrew BrennanAndrew Brennan
1,926610
I am not going to use this method everywhere, or anywhere except for this specific place, storage and retrieval is my only requirement for this information only not the rest of the site's data.
– Ahmed AlHallak
Aug 19 '16 at 3:20
1
Your point about why i should use RDBMS is why i did, And you make a good point inYou need to know how many items of type X are needed to fulfill all open ordersbut i won't be using this feature, but i will need RDBMS's features in other cases. And yes i am describing a many-to-many relationship, and making three tables and joining them is what i meant to describe in the second way. So with this in mind is there a reason in your opinion why not to use the first way, especially since it's simpler and easier to use(for me at least) than the second "correct" way?
– Ahmed AlHallak
Aug 19 '16 at 3:38
You will create technical debt for yourself en.wikipedia.org/wiki/Technical_debt
– Andrew Brennan
Aug 19 '16 at 8:24
1
I don't foresee a situation where i will have technical debt because of this, because as i said previously the website isn't going to have any features that will incur me to reverse my code in this specific situation, but thanks anyway for the opinion and info, for now i think i will follow first method and decide later if need to change it.
– Ahmed AlHallak
Aug 23 '16 at 2:54
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f147166%2foptimal-wayperformance-wise-to-store-array-like-data-in-mysql%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
If you just want somewhere to dump JSON then you're wasting your time with MySQL. Use a simple docustore. You will lose the ability to query the dataset in any meaningful way, but if storage and retrieval is your only requirement, there's no need for a relational database.
If you have more complex requirements, then you will be far better off using a relational database. e.g. You need to know how many items of type X are needed to fulfill all open orders; You need to know how many open orders currently exist for a specific customer, and what the total value of those orders is, etc etc. Using a docustore and JSON will severely limit your ability to answer questions about your dataset.
Finally, you're describing a many-to-many relationship between orders and items, right? One order can consist of multiple items, and one item can be a part of multiple orders, yes? In this case you need three tables - orders, items and an intermediate table to join them: orderitems. This design is what we call "normalised" - it will allow you easily query the data while minimising the amount of data stored.
answered Aug 18 '16 at 15:05
Andrew BrennanAndrew Brennan
1,926610
I am not going to use this method everywhere, or anywhere except for this specific place, storage and retrieval is my only requirement for this information only not the rest of the site's data.
– Ahmed AlHallak
Aug 19 '16 at 3:20
1
Your point about why i should use RDBMS is why i did, And you make a good point inYou need to know how many items of type X are needed to fulfill all open ordersbut i won't be using this feature, but i will need RDBMS's features in other cases. And yes i am describing a many-to-many relationship, and making three tables and joining them is what i meant to describe in the second way. So with this in mind is there a reason in your opinion why not to use the first way, especially since it's simpler and easier to use(for me at least) than the second "correct" way?
– Ahmed AlHallak
Aug 19 '16 at 3:38
You will create technical debt for yourself en.wikipedia.org/wiki/Technical_debt
– Andrew Brennan
Aug 19 '16 at 8:24
1
I don't foresee a situation where i will have technical debt because of this, because as i said previously the website isn't going to have any features that will incur me to reverse my code in this specific situation, but thanks anyway for the opinion and info, for now i think i will follow first method and decide later if need to change it.
– Ahmed AlHallak
Aug 23 '16 at 2:54
add a comment |
If you just want somewhere to dump JSON then you're wasting your time with MySQL. Use a simple docustore. You will lose the ability to query the dataset in any meaningful way, but if storage and retrieval is your only requirement, there's no need for a relational database.
If you have more complex requirements, then you will be far better off using a relational database. e.g. You need to know how many items of type X are needed to fulfill all open orders; You need to know how many open orders currently exist for a specific customer, and what the total value of those orders is, etc etc. Using a docustore and JSON will severely limit your ability to answer questions about your dataset.
Finally, you're describing a many-to-many relationship between orders and items, right? One order can consist of multiple items, and one item can be a part of multiple orders, yes? In this case you need three tables - orders, items and an intermediate table to join them: orderitems. This design is what we call "normalised" - it will allow you easily query the data while minimising the amount of data stored.
answered Aug 18 '16 at 15:05
Andrew BrennanAndrew Brennan
1,926610
I am not going to use this method everywhere, or anywhere except for this specific place, storage and retrieval is my only requirement for this information only not the rest of the site's data.
– Ahmed AlHallak
Aug 19 '16 at 3:20
1
Your point about why i should use RDBMS is why i did, And you make a good point inYou need to know how many items of type X are needed to fulfill all open ordersbut i won't be using this feature, but i will need RDBMS's features in other cases. And yes i am describing a many-to-many relationship, and making three tables and joining them is what i meant to describe in the second way. So with this in mind is there a reason in your opinion why not to use the first way, especially since it's simpler and easier to use(for me at least) than the second "correct" way?
– Ahmed AlHallak
Aug 19 '16 at 3:38
You will create technical debt for yourself en.wikipedia.org/wiki/Technical_debt
– Andrew Brennan
Aug 19 '16 at 8:24
1
I don't foresee a situation where i will have technical debt because of this, because as i said previously the website isn't going to have any features that will incur me to reverse my code in this specific situation, but thanks anyway for the opinion and info, for now i think i will follow first method and decide later if need to change it.
– Ahmed AlHallak
Aug 23 '16 at 2:54
add a comment |
If you just want somewhere to dump JSON then you're wasting your time with MySQL. Use a simple docustore. You will lose the ability to query the dataset in any meaningful way, but if storage and retrieval is your only requirement, there's no need for a relational database.
If you have more complex requirements, then you will be far better off using a relational database. e.g. You need to know how many items of type X are needed to fulfill all open orders; You need to know how many open orders currently exist for a specific customer, and what the total value of those orders is, etc etc. Using a docustore and JSON will severely limit your ability to answer questions about your dataset.
Finally, you're describing a many-to-many relationship between orders and items, right? One order can consist of multiple items, and one item can be a part of multiple orders, yes? In this case you need three tables - orders, items and an intermediate table to join them: orderitems. This design is what we call "normalised" - it will allow you easily query the data while minimising the amount of data stored.
answered Aug 18 '16 at 15:05
Andrew BrennanAndrew Brennan
1,926610
If you just want somewhere to dump JSON then you're wasting your time with MySQL. Use a simple docustore. You will lose the ability to query the dataset in any meaningful way, but if storage and retrieval is your only requirement, there's no need for a relational database.
If you have more complex requirements, then you will be far better off using a relational database. e.g. You need to know how many items of type X are needed to fulfill all open orders; You need to know how many open orders currently exist for a specific customer, and what the total value of those orders is, etc etc. Using a docustore and JSON will severely limit your ability to answer questions about your dataset.
Finally, you're describing a many-to-many relationship between orders and items, right? One order can consist of multiple items, and one item can be a part of multiple orders, yes? In this case you need three tables - orders, items and an intermediate table to join them: orderitems. This design is what we call "normalised" - it will allow you easily query the data while minimising the amount of data stored.
answered Aug 18 '16 at 15:05
Andrew BrennanAndrew Brennan
1,926610
answered Aug 18 '16 at 15:05
Andrew BrennanAndrew Brennan
1,926610
answered Aug 18 '16 at 15:05
Andrew BrennanAndrew Brennan
1,926610
answered Aug 18 '16 at 15:05
Andrew BrennanAndrew Brennan
1,926610
1,926610
I am not going to use this method everywhere, or anywhere except for this specific place, storage and retrieval is my only requirement for this information only not the rest of the site's data.
– Ahmed AlHallak
Aug 19 '16 at 3:20
1
Your point about why i should use RDBMS is why i did, And you make a good point inYou need to know how many items of type X are needed to fulfill all open ordersbut i won't be using this feature, but i will need RDBMS's features in other cases. And yes i am describing a many-to-many relationship, and making three tables and joining them is what i meant to describe in the second way. So with this in mind is there a reason in your opinion why not to use the first way, especially since it's simpler and easier to use(for me at least) than the second "correct" way?
– Ahmed AlHallak
Aug 19 '16 at 3:38
You will create technical debt for yourself en.wikipedia.org/wiki/Technical_debt
– Andrew Brennan
Aug 19 '16 at 8:24
1
I don't foresee a situation where i will have technical debt because of this, because as i said previously the website isn't going to have any features that will incur me to reverse my code in this specific situation, but thanks anyway for the opinion and info, for now i think i will follow first method and decide later if need to change it.
– Ahmed AlHallak
Aug 23 '16 at 2:54
add a comment |
I am not going to use this method everywhere, or anywhere except for this specific place, storage and retrieval is my only requirement for this information only not the rest of the site's data.
– Ahmed AlHallak
Aug 19 '16 at 3:20
1
Your point about why i should use RDBMS is why i did, And you make a good point inYou need to know how many items of type X are needed to fulfill all open ordersbut i won't be using this feature, but i will need RDBMS's features in other cases. And yes i am describing a many-to-many relationship, and making three tables and joining them is what i meant to describe in the second way. So with this in mind is there a reason in your opinion why not to use the first way, especially since it's simpler and easier to use(for me at least) than the second "correct" way?
– Ahmed AlHallak
Aug 19 '16 at 3:38
You will create technical debt for yourself en.wikipedia.org/wiki/Technical_debt
– Andrew Brennan
Aug 19 '16 at 8:24
1
I don't foresee a situation where i will have technical debt because of this, because as i said previously the website isn't going to have any features that will incur me to reverse my code in this specific situation, but thanks anyway for the opinion and info, for now i think i will follow first method and decide later if need to change it.
– Ahmed AlHallak
Aug 23 '16 at 2:54
I am not going to use this method everywhere, or anywhere except for this specific place, storage and retrieval is my only requirement for this information only not the rest of the site's data.
– Ahmed AlHallak
Aug 19 '16 at 3:20
I am not going to use this method everywhere, or anywhere except for this specific place, storage and retrieval is my only requirement for this information only not the rest of the site's data.
– Ahmed AlHallak
Aug 19 '16 at 3:20
1
1
Your point about why i should use RDBMS is why i did, And you make a good point in
You need to know how many items of type X are needed to fulfill all open orders but i won't be using this feature, but i will need RDBMS's features in other cases. And yes i am describing a many-to-many relationship, and making three tables and joining them is what i meant to describe in the second way. So with this in mind is there a reason in your opinion why not to use the first way, especially since it's simpler and easier to use(for me at least) than the second "correct" way?– Ahmed AlHallak
Aug 19 '16 at 3:38
Your point about why i should use RDBMS is why i did, And you make a good point in
You need to know how many items of type X are needed to fulfill all open orders but i won't be using this feature, but i will need RDBMS's features in other cases. And yes i am describing a many-to-many relationship, and making three tables and joining them is what i meant to describe in the second way. So with this in mind is there a reason in your opinion why not to use the first way, especially since it's simpler and easier to use(for me at least) than the second "correct" way?– Ahmed AlHallak
Aug 19 '16 at 3:38
You will create technical debt for yourself en.wikipedia.org/wiki/Technical_debt
– Andrew Brennan
Aug 19 '16 at 8:24
You will create technical debt for yourself en.wikipedia.org/wiki/Technical_debt
– Andrew Brennan
Aug 19 '16 at 8:24
1
1
I don't foresee a situation where i will have technical debt because of this, because as i said previously the website isn't going to have any features that will incur me to reverse my code in this specific situation, but thanks anyway for the opinion and info, for now i think i will follow first method and decide later if need to change it.
– Ahmed AlHallak
Aug 23 '16 at 2:54
I don't foresee a situation where i will have technical debt because of this, because as i said previously the website isn't going to have any features that will incur me to reverse my code in this specific situation, but thanks anyway for the opinion and info, for now i think i will follow first method and decide later if need to change it.
– Ahmed AlHallak
Aug 23 '16 at 2:54
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f147166%2foptimal-wayperformance-wise-to-store-array-like-data-in-mysql%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
