0

I am trying to make a data model for orders where in one order, I can have many products. So far I came up with this model:

enter image description here

The idea is that one order can produce many rows in order_product table(I have the name and price attributes, since they might get updated later in time and I want to keep the values that were correct at the time of the order creation (and I am also missing PRODUCT_ID foreign key since I don't really need it.)). My questions are:

  1. Is this approach correct?
  2. How can I manage the inserts in this way if the approach is correct - I first make a record for an order, then I insert all the products in the order_product table using the ID of the order I first made, but how do I get the ORDER_ID required for the order product table?

I am really new to SQL so excuse me if the question is anyhow dumb. I just really struggle to find a proper way of doing this. Thanks in advance.

rollstapewz
  • 35
  • 5
  • Maybe [this](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) answers your question. – Stefanov.sm Apr 01 '21 at 12:05
  • Not really, because I am almost sure the approach is correct(one to many). But the question about retrieving the ID of the order and using it in the order products table is the one I cannot answer. – rollstapewz Apr 01 '21 at 12:24
  • https://stackoverflow.com/questions/55051487/ –  Apr 01 '21 at 13:21

1 Answers1

1

First you insert into order table with a returning clause

insert into order (date) values (current_date) returning id;

in order to store the returned value into a variable, ex. my_var_id. Then you use this value to do s/th like this

insert into order_product(order_id, name, price, quantity) values
(:my_var_id, 'apples', '2.0', 5.20),
(:my_var_id, 'pears', '2.5', 3.75),
(:my_var_id, 'fish', '20.0', 1.38);

There are various ways to do this incl. a CTE, a plpgsql block/function or database calls from an upper tier.

BTW your case is a textbook example for a many-to-many relationship. The thing is that order_product fails 2NF and better be normalized - istead of name and price have product_id, FK to a product table.

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • In real world, you often need (duplicate) name and price on order rows (in addition to product_id) - you may call them modified_product_name_for_current_order and special_price_at_order_creating_time or something similar :) – Arvo Apr 01 '21 at 14:01
  • @Arvo Fair enough and `order_product` still remains a bridge table. – Stefanov.sm Apr 01 '21 at 14:53
  • @Arvo yes this was my idea, thats why I duplicate them. – rollstapewz Apr 01 '21 at 17:17
  • @Stefanov.sm I will check your solution, didn't know there was returning option when you make an insert. Thank you! – rollstapewz Apr 01 '21 at 17:17