Linking records together

We need to track work items across several existing applications. Items can start as a document on disk, can be renamed or moved, converted to a task in an application, until they reach a certain state. The applications know nothing about preceding or following applications and there is no common identifier for the items, which has always been a good thing.

My idea is to create a table for all events. Each application will record their
own events. A minimum is one event per application with a starting and finishing situation for that application. Example: "file \\folder1\x.doc was moved to \\folder2\y.doc". Another application may follow up with "\\folder2\y.doc was archived with id 12345". Each event will have a date and time.

My table will probably have the following columns:

event_id number(10)
start_item varchar2(255)
end_item varchar2(255)
event_date date

Sample data:

1    A    B    2009-10-11
2    G    H    2009-10-12
3    B    C    2009-10-13
4    H    I    2009-10-17

Records can be linked from end_item to start_item (the next application in line will continue where the previous application stops). So in this sample data I have two sets of records (1 with 3, 2 with 4) and recognize two starting points A and G with two end points C and I. From A to C takes two days, from G to I takes 5 days.

Data entry is no problem. Reporting is. My resultset (a view, another table) should be this:

A C 2
G I 5

How can I achieve this? I have looked at the connect by, but I get to many records. So basically I have no idea where to start.

Thanks in advance,
PJ Received on Wed Sep 23 2009 - 08:22:49 CDT