CODEWITHSUNDEEP
sqlpostgresql
rollstapewz
rollstapewz

Reputation: 45

sql data model for an order with many products

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.

Upvotes: 1

Views: 859

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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.

Upvotes: 1

Related Questions