Postgres Multiple Arrays

Question

This im hoping is a simple one. but i have a table that is called product. One column is called Parts. Basically i need to store multiple arrays in there, with each array holding a reference to a part ID and the quantity required. I dont really know the best way to set it up. There could be up to about 25 parts for one product yet another have 3.

Any advice would be a great help!

Cheers Tom

Why don't you want to setup a link table, `product_part`? – Quassnoi Apr 03 '13 at 18:46 — Quassnoi, Apr 03 '13 at 18:46
Don't do it. Set it up relational. – Clodoaldo Neto Apr 03 '13 at 18:47 — Clodoaldo Neto, Apr 03 '13 at 18:47

score 3 · Accepted Answer · edited May 23 '17 at 12:21

3

It seems best to setup a Many-to-Many relationship with another table and link to that. If you insist on storing the array as a string - use JSON, you can always encode your array(s) to JSON and store it as such, then when you pull it out, you merely decode from JSON back to array.

edited May 23 '17 at 12:21

Community

1
1

answered Apr 03 '13 at 18:53

bubba

3,839
21
25

how will i store the foreign id and quantity together? as one product may have a different quantity requirement of a part to another product so i cant set quantity in the foreign table – Lacko Apr 03 '13 at 18:55
1

In a cross reference table, just keep |product_id|part_id|quantity| The link I provided demonstrates this with the bill_product table. – bubba Apr 03 '13 at 19:00

Postgres Multiple Arrays

1 Answers1