0

I am creating a a table that holds orders that we receive. ONe column will hold the product ID(s) and the quantity(s) as they could order 3 different items then next order 15 different items. So i have created it so the column order_products will be text[] that will hold arrays like so: 

{{123234,3},{987765,3},{456678,65}}

The first part relating to another table with the product details then the quantity of that ordered.

My question is, is there a way to link the first part of each array (the part number) to the product table via PK or FK to create the relationship?

Improve this question

1 Answer 1

Reset to default
4

Not formally.

Foreign keys to arrays were considered, but even then it was only planned for simple 1-dimensional arrays.

You can implement your own triggers to check and enforce the relationship.

Frankly, I strongly recommend normalizing your database unless you have an extremely good reason to use arrays for this purpose.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

many thanks for the reply. How would you normalise a table when the number of products relating to an order can change dynamically?
Introduce a 1:n relationship via a child table.
oh yeah of course! ive just seen how i should do it. 1 order can have many products. would it not be many to many though? as there could be 3 orders all with the same product being ordered?
Yes, that'd be m:n. Same deal, just need a join-table with the usual primary key(order_id, product_id). Or if you're using some braindead ORM like Rails that can't cope with composite primary keys, unique(order_id, product_id) and a synthetic primary key.
ah yes thank you! i see how it works i completly forgot about the whole join table aspect! its PHP/postgreSQL im creating it in. Many thanks

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.