I am using Sql Server 2008 R2.
I have two tables that describe import processes and export processes. Import table has these columns with others that just have details about each type of import
ImportID, ImportFileFormat, ImportFileLocation,...
The Export tbl is similar to the import table except it describes outputs and other export specific data.
ExportID, ExportFileFormat, ExportFileLocation,...
Now I need some way of storing a 'Transaction' and I am stuck on how to do this exactly.
A 'transaction' is any number of imports and exports in any unique sequence. Eg:
T = I1, E1, E2 or
I2, E1, E3, I1 or
E3 or any unique combination of importing / exporting with no limit to number of Imports/ exports.
A 'transaction' may eventually include other 'eventtypes' other than just import/export like 'run_sp' or some sort of action in the database or application.
How might I go about doing something like this? I have looked at what I think may be a similar question on stackoverflow. This seems like something that may do what I want but I am unsure.
Any help would be greatly appreciated.
TransactionID TransactionNumber EventTable EventID 1 1 'tblImport' 1 2 1 'tblExport' 3 3 2 'tblImport' 1 4 3 'tblExport' 3 5 2 'tblExport' 2