Should I store product lists to users with arrays in MySQL?

Question

Well, I have a big dilemma in my life about how to store product lists when I need to link that lists to an ID or something like that.
For this first example I'll gonna use the user cart.

I've always used this, even never liking it:

ID  |  PRODUCT
12  |  Ring
12  |  Necklace
12  |  Bracelet

But lately I've been thinking about store arrays in MySQL. While it sounds like a good idea (in first view), by using arrays I'd only be able to manage the carts through PHP, by creating some kind of control panel or something.

Unfortunately, there is a con in it. Although the whole system take less space than the other way, I wouldn't be able to handle things through MySQL itself. Like, if someone make an order, I wouldn't be able to SUM the prices*quantity to get the order value.

What is the best way? Is there another way?

I think you have explained why doing what you are proposing by using arrays instead of a database, is a very wrong idea. — TimBrownlaw, Oct 25 '16 at 07:45
Please see Junction, Intersect or association tables [here, Junction Tables](http://stackoverflow.com/a/32620163) and not CSV in columns. You will be terribly miserable if you store arrays as you call it. — Drew, Oct 25 '16 at 07:45
Anyone here will be able to show you how to sum your data even if you store it in a way that does not seem pleasant to the eye (ie: the way sql engines want it saved and indexed) — Drew, Oct 25 '16 at 07:47

score 0 · Answer 1 · answered Oct 25 '16 at 07:58

0

If you are going to need this operations over elements:

searching
sorting
filtering elements
aggregate functions

I would recomend you storing elements in separate rows. This way you are going to get full control over elements in "your arrays"

answered Oct 25 '16 at 07:58

2ehr

128
1
7

score 0 · Accepted Answer · answered Oct 25 '16 at 08:05

As soon as you store unstructured information in MySQL you lose most of the benefits of using a relational database and you only have an over-engineered file system (with, that's true, excellent multi-user capabilities). If you aren't familiar with SQL (which I suspect is the case) you'll initially think you're speeding development. But one day, if the shop hopefully grows, you'll realise that retrieving everything for further PHP postprocessing doesn't scale well.

While it can certainly make sense to outsource certain features (not the complete app) to a nosql database if you have a high concurrence site, if you use a relational database you'd better use it as it's meant to be used: with properly structured and normalised information.

While the 2ehr answer was really good, you did explain better. Thank you! — Malork, Oct 25 '16 at 08:18

score 0 · Answer 3 · answered Oct 25 '16 at 10:08

I wouldn't be able to handle things through MySQL itself. Like, if someone make an order, I wouldn't be able to SUM the prices*quantity to get the order value.

It seems you understand why your 'arrays' idea isn't a good one :)

Consider that XML (or JSON or similar) was invented because distributed systems get disconnected. It is OK to stored such 'complex' values in a database, even better if the DBMS has support for XML as a data type.

However, if you need to query the 'complex' value in the DBMS then you should be looking for first class support for such data types. For example, most SQL DBMSs have support for a timestamp/datetime temporal type: being able to declare a column of that type and compare two values of the type is one thing but what you really need are the temporal functions that will e.g. get time granules (years, days, etc), formats (weekday etc), support UTC and timezones, etc.

For your 'arrays' I'm guessing you will need to handle serialization and storage yourself, plus all that entails e.g. convert to a delimited string, escaping delimiting characters, and storing in a text column. Once you have done that work you will be left with no support on the DBMS side.

Should I store product lists to users with arrays in MySQL?

3 Answers3