mysql insert multiple data into a single column or multiple row

Question

Hello everyone,

just want to ask for an opinion regarding mysql. which one is the better solution?

case1: store in 1 row:-

product_id:1

attribute_id:1,2,3

when I retreive out the data, I split the string by ','

I saw some database, the store the data in this way, the record is a product, the column is stored product attribute:

a:3:{s:4:"spec";a:2:{i:1;s:6:"black";i:3;s:2:"37";}s:21:"spec_private_value_id";a:2:{i:1;s:11:"12367591683";i:3;s:11:"12367591764";}s:13:"spec_value_id";a:2:{i:1;s:1:"5";i:3;s:2:"29";}}

or

case2: store in 3 row:-

product_id:1

attribute_id:1

---

product_id:1

attribute_id:2

---

product_id:1

attribute_id:3

---

this is the normal I do, to store 3 rows for the attribute for a record.

In term of performance and space, anyone can tell me which one is better. From what I see is case1 save space, but need to process the data in PHP (or other server side scripting). case2 is more straight forward, but use spaces.

Answer 1

Save space? Seriously? You're talking about saving bytes when a one terabyte disk goes for 70 dollars?

And maybe you're not even saving bytes. If you store attributes as "12234,23342,243234", that's like 30 bytes for 3 attributes. If you'd store them as smallint, they'd take up 6 bytes.

Answer 2

Depends on whether the attributes are important for searching later, for example.

It may be good if you keep attributes as serialized array in just one field in case you actually don't care about them and in case that you, for example, won't need to run a query to show all products that have one attribute.

However, finding all products that have one attribute would be at least "lousy" in case you have attributes as comma-separated (you need to use LIKE), and in case you store attributes as serialized arrays they are completely unusable for any kind of sorting or grouping using sql queries.

Using separate table for multiple relations between products and attributes is far better if they are of any importance for selecting/grouping/sorting other data.

Answer 3

In case 1, although you save space, there's time spent on splitting the string.

You also must take care of the size of your field: If you have 50 products with 2 attributes and one with 100 attributes, you must make the field ~ varchar(200)... You will not save space at all.

I think case 2 is the best and recommended solution.

Answer 4

You need to consider the SELECT statements that would be using these values. If you wish to search for records that have certain attributes, it is much more efficient to store them in separate columns and index them. Otherwise, you are doing "LIKE" statements which take much longer to process.