Inserting data in multiple tables [message #662436] |
Tue, 02 May 2017 06:57 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
I have 4 Tables
Customer(cust_id (PK), cust_phone, name)
Order((order_id,cust_id) (PK), order_date, order_amt)
Order_line((order_id,Product_id)(PK), qty)
Product(product_id, Product_name, product_price)
The order table has a composite key saying an order only exists with a customer.
The Order_line table has a composite key as it is a bridge table between product and order table. the order_amt is a calculated column, its the total order value.
The question is how do i insert data into the order table and the Order_line table? using procedure, triggers or a temp table or any other way? I am really stuck at this. Help!!
Do I need to make any changes in the model?
|
|
|
|
|
|
Re: Inserting data in multiple tables [message #662441 is a reply to message #662439] |
Tue, 02 May 2017 08:28 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Having a pk of order_id and cust_id on order means you can have 2 orders for 2 different customers with the same order_id. That isn't generally a good idea.
All you need to make sure that an order belongs to a customer is to have the cust_id column on order, make it not null and add a foreign key that points to customer. No need to have it part of the key on order.
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Inserting data in multiple tables [message #662453 is a reply to message #662450] |
Tue, 02 May 2017 10:35 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I agree with the fact that the product price (PRODUCT_PRiCE) should be in ORDER_LINE as the price can changed between the order and the next time you will check the order.
Amount could be in ORDER_LINE or not, I don't see the value to have it, but if it is there then it must be a virtual column (as product of PRODUCT_PRICE and QTY, from ORDER_LINE table of course).
I disagree that the computed column ORDER_AMT should not be in the ORDER table. This allows to see the order summary without having to query ORDER_LINE.
And this is especially true if an order can't be modified afterwards. If it can then you must have process to prevent from inconsistencies.
Note: ORDER_AMT in ORDER table can't be a virtual column.
[Updated on: Tue, 02 May 2017 10:35] Report message to a moderator
|
|
|
Re: Inserting data in multiple tables [message #662454 is a reply to message #662452] |
Tue, 02 May 2017 10:35 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You have price as an actual column on order_line - that stores the price of the product at the time the order_line was created.
You then have a virtual column on order_line for line_amount - which is set to price * quantity
You don't store order amount, nor do you have it as a virtual column (because you can't), but whenever the application needs to display order amount you just select sum(line_amount).
|
|
|
|
|
|
|
Re: Inserting data in multiple tables [message #662488 is a reply to message #662459] |
Wed, 03 May 2017 11:44 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Its fairly easy you define your your foreign key like the following
ALTER TABLE EMP
ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
REFERENCES DEPT(DEPTNO) INITIALLY DEFERRED ;
The clause INITIALLY DEFERRED tells oracle to not check the constraint until a commit is issued. This will allow you do put your data in any order and then database integrity is checked at the commit;
|
|
|
|
Re: Inserting data in multiple tables [message #662499 is a reply to message #662497] |
Thu, 04 May 2017 03:03 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
And then? How is this a problem in what Bill posted?
He just said you can insert it after, he did not say you must insert it after, you can still insert it before if you want.
If your requirement is that you must insert it before then do not declare the constraint as DEFERRED.
[Updated on: Thu, 04 May 2017 03:05] Report message to a moderator
|
|
|
|
|
|
|