Q:Organizing a sequence of actions in Oracle/SQL?

From: Chris Vaessen <cvaessen_at_aol.com>
Date: Thu, 07 Jan 1999 17:22:13 +0100
Message-ID: <3694DF34.F1253F37@aol.com>

Hi,

I´m trying to design a simple database in oracle that is able to manage a sequence of actions efficiently, but I don't know if oracle has any special features to solve my problem:

Basically, I've a list of IDs in one table that will be referenced from another table. These Ids describe (or reference) a sequence of actions:

   Table 1                                 Table 2
 -------------------                --------------------- - - -


| SeqID |  ActionID |              | ActionID |  Name | etc...


 -------------------                --------------------- - - -


|   1   |     1     |              |     1    |  Ac1  |


|   2   |     2     | n          1 |     2    |  Ac2  |


|   3   |     3     | ------------ |     3    |  Ac3  |


                                   |     4    |  Ac4  |

and so on..
The SeqID should be unique.

Now it will be a common operation to insert or delete some sequences into Table 1.
In this case it will be necessary to change the numbers of all higher SeqID´s to preserve the order of the sequence.

For example, if I want to insert two new sequences with ActionID 4 and 5 after SeqID 1, the new tables would look like this:

   Table 1                                 Table 2
 -------------------                --------------------- - - -


| SeqID |  ActionID |              | ActionID |  Name | etc...


 -------------------                --------------------- - - -


|   1   |     1     |              |     1    |  Ac1  |


|   2   |     4     | n          1 |     2    |  Ac2  |


|   3   |     5     | ------------ |     3    |  Ac3  |


|   4   |     2     |              |     4    |  Ac4  |


|   5   |     3     |              |     5    |  Ac5  |

and so on..

To achieve this, the application (or PL/SQL) would have to re-number the SeqIDs
2 and 3 to 4 and 5. You can think of the SeqID as some kind of line- number in a program
(as they were used in the early times of BASIC ;-).

But we will need to manage a lot of Sequence_IDs (up to 200.000) and not only an ActionID but
other attributes in Table 1 (but only SeqID has to be unique).

Now I think it would be very runtime- consuming if I always had to renumber the SeqID when
deleting or inserting new items into the Table 1. I thought of a linked list implemented
with tables but this would not be easy to manage with a database (queries etc.)

I would be very happy if anyone could give me a clue how to change the design and/or
implement this efficiently in oracle!

Thanks!

bye
chris Received on Thu Jan 07 1999 - 10:22:13 CST