Re: Parent/Child Table Design

On Feb 5, 10:26 pm, Digital Logic <mrodd..._at_hotmail.com> wrote:
> On Feb 5, 6:52 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Feb 5, 4:54 pm, Digital Logic <mrodd..._at_hotmail.com> wrote:
>
> > > On Feb 5, 4:17 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > On Feb 5, 3:10 pm, Digital Logic <mrodd..._at_hotmail.com> wrote:
>
> > > > > A bit a newbie to Oracle so I apologize if this is a simple question.
> > > > > I need to implement two tables similar to the classic order and order
> > > > > details example.  I've place a rough description of the two tables
> > > > > below.  How would I autogenerated values for the OrderLineNumber
> > > > > column given that that should be unique for each order and not for
> > > > > each order line.  I experimented with using triggers to generated the
> > > > > numbers based on the records already present in the table.  I put
> > > > > together a trigger that works as long as you're only insert a single
> > > > > row at a time, but does not work for multiple rows.  It would
> > > > > appreciated if anyone has a solution.
>
> > > > > Orders(
> > > > >   OrderID integer primary key,
> > > > >   ......
> > > > > );
>
> > > > > OrderLines(
> > > > >   OrderID integer not null,
> > > > >   OrderLineNumber integer not null,
> > > > >   ....
> > > > >   Constraint OrderLines_PK
> > > > >     Primary Key (OrderID, OrderLineID),
> > > > >   Constraint OrderLines_FK
> > > > >     Foreign Key (OrderID)
> > > > >     References Orders(OrderID)
> > > > > );
>
> > > > Post your trigger code and someone may provide some additional
> > > > insight.
>
> > > > David Fitzjarrell
>
> > > CREATE OR REPLACE TRIGGER BI_OrderLines
> > > BEFORE INSERT ON OrderLines
> > >     FOR EACH ROW
> > >     WHEN (NEW.OrderLineNumber IS NULL)
> > >     DECLARE
> > >         PRAGMA AUTONOMOUS_TRANSACTION;
> > >     BEGIN
> > >         SELECT
> > >             Coalesce(Max(OrderLines.OrderLineNumber), 0) + 1
> > >             INTO :NEW.OrderLineNumber
> > >         FROM OrderLines
> > >         WHERE OrderLines.OrderID = :NEW.OrderID;
> > >         COMMIT;
> > >     END;
>
> > > The problem when inserting mutlitple lines seems to be that the
> > > OrderLineNumber calculated is the same for each record since the
> > > preceeding records are not committed to the table yet.  I thought that
> > > if I could access the rownum  pseudocolumn I could determine how many
> > > records were inserted prior to the record being processed and base the
> > > new OrderLineNumber on this as well, but apparently the column is not
> > > accessable in this context.- Hide quoted text -
>
> > > - Show quoted text -
>
> > I suggest you consider using a second sequence for the line numbers.
> > Who cares if the line numbers are not sequential or in the thousands?
> > Most order entry systems include a validation step at the end of the
> > order entry process; you can order and renumber the line items for
> > storage at this point if desired.  While data is being entered you can
> > number the details on the screen and have the logic take care of
> > updating the real line items.
>
> > HTH -- Mark D Powell --
>
> I have a current implementation that uses a sequence to generate that
> column, but this is creating several problems due to the following
> issues:
> 1) I am working in a different domain then order management.  I merely
> used that as an example as it expresses the problem pretty clearly
> without bogging down with details of my problem domain.
> 2) Reports generated based on these tables need to display sequential
> line numbers.  I have queries that generate the sequential line
> numbers based on the id of the order line, but it is creating a bottle
> neck in running these reports.  I could batch create the line numbers
> at a certain point, but creates a seperate process that needs to be
> supported and its failure would be harder to deal with as the data
> could end up in an inconsistant state.
> 3) The data is not being manually entered via a presentation layer.
> Think of it more of a logging application where an event is the order
> and order lines are items associated with the event.
> 4) The line numbers in the near future will be much greater then in
> the thousands.  I did some use cases and determined that there will be
> in the order thousands of order lines for each order and tens of
> thousands of orders within a three month time span.  I had been
> putting off dealing with this problem till we got closer to the first
> production release, but now we're about a month and a half off.
>
> I am also considering what you said about assigning the line numbers
> at the end of the order, but should something fail before the entry of
> the order is completed there will be order line records with out order
> line numbers.  I should note that since I'm not actually dealing with
> orders, the possiblity of something failing before all items are added
> is a possiblity I must deal with.- Hide quoted text -
>
> - Show quoted text -

The records do not exist until committed and if all the detail rows are updated and committed in one transaction then numbering or renumbering the line items at this time should not be an issue.

How are you currently numbering the rows? Have you looked at the row_number analytic function?

Are the "orders" entered directly into the target tables or is a staging table used until the data is validated?

Another issue you may need to consider: if it is possible for another line item to be added in the future then what happens to the line item numbering?

HTH -- Mark D Powell -- Received on Wed Feb 06 2008 - 10:04:14 CST