0

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   |
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Emmett Janczak
  • 11
  • 3

2 Answers2

1

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • This is perfect, I was way over thinking this. Another quick question: if I needed to reflect multiple work orders for each product, would I just want to add a quantity column on the new table? – Emmett Janczak Aug 21 '21 at 05:19
  • Yes, you could add other attribute columns to the middle table. Like the date of the PO, or the payer, or the number of work orders. But if any of those is itself a multi-valued attribute (like each work order having attributes for example), you may have to create further tables. – Bill Karwin Aug 21 '21 at 07:16
0

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/

Anton Ganichev
  • 2,184
  • 1
  • 18
  • 17