Parent/Child Table Design

#21

On Feb 5, 10:26*pm, Digital Logic <mrodd... (AT) hotmail (DOT) com> wrote:

Quote:

On Feb 5, 6:52*pm, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:

On Feb 5, 4:54*pm, Digital Logic <mrodd... (AT) hotmail (DOT) com> wrote:

On Feb 5, 4:17*pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

On Feb 5, 3:10*pm, Digital Logic <mrodd... (AT) hotmail (DOT) 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 --

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Parent/Child Table Design

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Parent/Child Table Design - 02-06-2008 , 10:04 AM