CODEWITHSUNDEEP
mysqlsqljsonxml
Emmett Janczak
Emmett Janczak

Reputation: 11

Best practice to store array-like data in MySQL or similar database?

I have two tables that I want to relate to each other. The issue is any product can have n-number of POs, so individual columns wouldn't work in a traditional DB.

I was thinking of using JSON fields to store an array, or using XML. I would need to insert additional POs, so I'm concerned with the lack of editing support for XML.

What is the standard way of handling n-number of attributes in a single field?

|id | Product | Work POs|
| - | ------- | ------- |
| 1 | bicycle | 002,003 |
| 2 | unicycle| 001,003 |

|PO |      Job         |
|-- | ---------------- |
|001|Install 1 wheel   |
|002|Install 2 wheels  |
|003|Install 2 seats   |

Upvotes: 0

Views: 4547

Answers (2)

Anton Ganichev
Anton Ganichev

Reputation: 2542

In some case you really need store array-like data in one field. In MySQL 5.7.8+ you can use JSON type datafield:

ALTER TABLE `some_table` ADD `po` JSON NOT NULL`;
UPDATE `some_table` SET `po` = '[002,003]' WHERE `some_table`.`id` = 1;

See examples here: https://sebhastian.com/mysql-array/

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562330

The standard way to store multi-valued attributes in a relational database is to create another table, so you can store one value per row. This makes it easy to add or remove one new value, or to search for a specific value, or to count PO's per product, and many other types of queries.

id Product
1 bicycle
2 unicycle
product_id PO
1 002
1 003
2 001
2 003
PO Job
001 Install 1 wheel
002 Install 2 wheels
003 Install seat

I also recommend reading my answer to Is storing a delimited list in a database column really that bad?

Upvotes: 2

Related Questions