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

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 ID´s 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 Table1 (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 - 17:26:26 CET