Design consideration regarding state handling: how to store multiple, variable states for one entity 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 &quot;Transaction&quot; which should be processed by &quot;ProcessingRules&quot;. 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 &quot;Processed YES/NO&quot; for each &quot;ProcessingRule&quot; and &quot;Transaction&quot;.
I thought the proper way is to have a relation table between the &quot;ProcessingRoles&quot; and the &quot;Transaction&quot;. 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.
Question:
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
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
(
    TransactionID INT NOT NULL PRIMARY KEY CLUSTERED IDENTity(1,1)
    --extra Transaction fields here as necessary
);

CREATE TABLE Rules
(
    RuleID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
    --extra Rules fields here as necessary
);

CREATE TABLE TransactionRuleProcessing
(
    TransactionID INT NOT NULL
    , RuleID INT NOT NULL
);



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

SELECT T.*
FROM Transactions AS T
    LEFT JOIN (
        SELECT * 
        FROM TransactionRuleProcessing AS TRP1 
        WHERE TRP1.RuleID = @RuleID
    ) AS TRP ON T.TransactionID = TRP.TransactionID
WHERE TRP.TransactionID IS NULL;