Re: Newbie Master-Detail Updates

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 21 Feb 2002 13:09:43 -0800
Message-ID: <c0d87ec0.0202211309.307ac945@posting.google.com>

>> What is the best way to do a Master-Detail Update from code/GUI.
<<

What is the best automobile to buy? It depends....

In this case, we have no idea what your business rules are or anything about the application. You have also worded your vague, general request in non-relational terminology. Let's make it more concrete; orders and order details.

CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,  customer CHAR(30) NOT NULL,
  ...);

CREATE TABLE OrdersDetails
(order_nbr INTEGER NOT NULL

           REFERENCES Orders (order_nbr)
           ON DELETE CASCADE
           ON UPDATE CASCADE,

 detail_nbr INTEGER NOT NULL,
 upc DECIMAL(14) NOT NULL

           REFERENCES Inventory (upc)
           ON DELETE CASCADE
           ON UPDATE CASCADE,

 quantity INTEGER NOT NULL,
 price DECIMAL (12,4) NOT NULL);

1. Grab the submitted order form and split out the order header information -- usually customer name & address, ship-to address, etc. The order number was created by the application that caught this data; if you created the order number on the back end, you need to inform the customer about it via email or something.
2. Slice up the rest of the order form into details, this will be the UPC, quantity, price, etc. for each item. You see two common approaches here:
3. Blindly put a sequential number of on each detail line, even if they are for the same item.
4. Consolidate the duplicated items with something like this before the insertion.

     SELECT order_nbr, upc, SUM(quantity)
       FROM WorkingTable
      GROUP BY order_nbr, upc

3) Once the two tables are loaded, do you write some stored procedures to handle your options:

1. Update or delete an existing order, and the DRI will handle that for you.
2. Update, insert or delete a detail line. Update is straight forward. Insert uses MAX(detail_nbr) + 1. Dropping a detail will involve renumbering:

 UPDATE OrderDetails

    SET detail_nbr

• (SELECT COUNT(*) FROM OrderDetails AS D1 WHERE D1.order_nbr = :changed_order_nbr AND D1.detail <= OrderDetails.detail_nbr) WHERE order_nbr = :changed_order_nbr;

I think you have the wrong mental model of SQL. This is not a 1950's file system where you have to over-write the old data, or re-punch cards. Received on Thu Feb 21 2002 - 15:09:43 CST