First I have to admit that I'm not a database professional neither are my colleagues. For a new project my colleagues and me came to a design question we couldn't really solve easily. And all the ideas had some disadvantages, so we could't figure out what's the best way to go.

We have a main entity "Transaction" which should be processed by "ProcessingRules". The processing rules can be configured by the users in the Web application (each rule has a different execution scheduling. One might be running every hour, whereas the others might run nightly).

Lets say Transaction gets 10'000 new records a day.

This would lead to a DB design where I need to keep the State "Processed YES/NO" for each "ProcessingRule" and "Transaction".

I thought the proper way is to have a relation table between the "ProcessingRoles" and the "Transaction". If no record present, the record has not been processed by this role yet.

Transaction [0..1] ------ [*] TransactionRuleProcessing [*] ------- [0..1] ProcessingRule

But when I think of the Query, this would lead into a WHERE NOT EXISTS (SELECT 1 FROM TransactionRuleProcessing...) query for the rule to identify new or unprocessed records.

If we have a large amount of rows in Transaction, I think this will affect performance because the NOT EXISTS will have to join the whole table to the state table. If I'm not mistaken, this might cause a performance issue.

On the other side, if we had only one state directly on the Transaction table, we could add an index and there would be no join between the large Transaction table and the state table.


Is it true that such a NOT EXISTS query would have to join the whole Transaction table with the TransactionRuleProcessing table to identify non existing (processed) rows? How could this affect performance of the database with a large Transaction table? What would be other recommendations to flag a record by a various amount of states?

Any ideas very much appreciated

closed as unclear what you're asking by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, StanleyJohns, dezso Sep 2 '13 at 5:25

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.


Using the cross reference table between Transaction and ProcessingRule is going to be a very efficient way of returning Transaction records that need to be processed.

You can get a list of Transactions that need processing by:

USE Test;

CREATE TABLE Transactions
    --extra Transaction fields here as necessary

    --extra Rules fields here as necessary

CREATE TABLE TransactionRuleProcessing
    TransactionID INT NOT NULL

DECLARE @RuleID INT = 123; --123 is the rule being processed

FROM Transactions AS T
        SELECT * 
        FROM TransactionRuleProcessing AS TRP1 
        WHERE TRP1.RuleID = @RuleID
    ) AS TRP ON T.TransactionID = TRP.TransactionID
  • Thanks. I still don't get how joins work: If I had only one state, I would add it to the Transactions table and add an index for it. I assume this would be very fast, because there would be only a few hundred with state Unprocessed at the time. I assume the performance would be constant even if the table grows continuous. But when the state is kept in another table, the Database Engine would have to join the whole Transaction table to identify its state. This would slow down performance if the table contains more records. Is my assumption wrong? Can non-existing rows be indexed? – Thomas Zweifel Jun 4 '13 at 6:54

You don't necessarily need to use a NOT EXISTS statement, you could always outer join the TransactionRuleProcessing table to the Transaction table and the ProcessingRule tables, and then simply check for empty columns in the result set that originate from the TransactionRuleProcessing table.

So basically, you could do something like this :

SELECT  A.id as TransactionId, 
    B.id as RuleId, 
    CASE ISNULL(C.TransactionId,0) WHEN 0 THEN 'No' ELSE 'Yes' END as DoesExist
    Transactions A
    LEFT OUTER JOIN ProcessingRules B ON 1 = 1
    LEFT OUTER JOIN TransactionProcessRules C ON C.Transactionid = A.id AND C.RuleId = B.id

If your Transaction.id column and TransactionRuleProcessing.TransactionId columns are properly indexed, you shouldn't run into any significant problems, performance wise

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