Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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.

Edit #1:

tblTransactions:

TransactionID   TransactionNumber   EventTable   EventID
1               1                   'tblImport'  1
2               1                   'tblExport'  3
3               2                   'tblImport'  1
4               3                   'tblExport'  3
5               2                   'tblExport'  2
share|improve this question

1 Answer 1

up vote 2 down vote accepted

Why not just have a transactions table:

CREATE TABLE dbo.Transactions(TransactionID INT IDENTITY(1,1));

When you're about to start a new transaction (a set of imports and exports), do this:

DECLARE @tID INT;
INSERT dbo.Transactions DEFAULT VALUES;
SELECT @tID = SCOPE_IDENTITY();

Now in your import and export tables, add a column that holds the transaction id, and store @tID there for every action in this sequence.

Or use a separate table to store Transaction + Event combinations, if events can be recycled. For example:

CREATE TABLE dbo.TransactionEvents
(
  TransactionID INT,   -- foreign key
  EventTypeID TINYINT, -- 1 = import, 2 = export
  EventID INT,         -- implicit foreign key to import/export table
  ... other columns ...
);

You can have a lookup table for EventTypes if you want, e.g.:

CREATE TABLE dbo.EventTypes
(
  EventTypeID TINYINT PRIMARY KEY,
  Name SYSNAME UNIQUE
);
INSERT dbo.EventTypes VALUES(1,N'Import'),(2,N'Export');

You might also consider not having separate import and export tables, but one events table with a type of import or export. Then the foreign key here can be explicit.

And please ditch the tbl prefixes. All they do is make you type three extra characters every single time.

share|improve this answer
    
I don't want to add another column to the import export tables because I want to 'reuse' those rows. If I were to do it that way I would have to create a new row in the import export table for every transaction. If I were to create a transaction table how could I define a transaction that may change.(just a new row?) –  Archangel33 Apr 24 '13 at 15:29
    
Then I think you need to better explain exactly what you want. I can't piece it together from these random word problems, sorry. Can you use a M:N junction table of sorts (TransactionID, ImportID NULL, ExportID NULL) –  Aaron Bertrand Apr 24 '13 at 15:30
    
@Archangel33 I don't think you understand what Aaron is suggesting. You have a row in the transaction table for every event, but your import/export tables don't change at all. They are referred to in the transaction table but not changed themselves. –  JNK Apr 24 '13 at 15:35
    
So is the suggestion something like Edit#1? –  Archangel33 Apr 24 '13 at 15:56
2  
They don't help anything (so now you open the Tables node in Object Explorer, and everything is prefixed by tbl - this just makes it harder to find the table you're after). Also please see this article about the sp_ prefix, which you absolutely should not be using. –  Aaron Bertrand Apr 24 '13 at 16:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.