Hi,

We have a transaction processing system that checks transactions, and accepts or rejects them based on certain criteria.

Currently, this system uses two database tables. There are no FK relationships, or DB validation rules at the moment - the program just uses text comparisons - I'm hoping to change that.

The first table provides criteria to match transactions on the source, account and state:

Weight Source Account State Tag
1 Foo Bob A Filter1
5 Foo Jane A Filter2
50 Bar Bob A Filter1
200 Bar Bob B Filter3

Each rule is assigned a "weight" - the rules are processed in order of weight, falling to the subsequent line if it doesn't match each line. The actual number chosen for weight is arbitrary.

Once we finds a match, we take the tag and consult a second table

Tag Minimum Maximum Step
Filter1 0 50 5
Fitler1 50 150 10
Filter1 150 20000 5
Filter2 0 100 15
Filter2 100 2000 5
Filter2 2000 100000 5
Filter3 0 100000 10

Each filter tag has multiple records. We find the one where our transaction amount falls between minimum and maximum, and where step applies (this is based on price movements). If we find a matching row, we let the transaction through, otherwise we reject it.

If however, our transaction doesn't fall between any of the min/max amounts, or the stepping doesn't match, we also reject that transaction.

Note that in the first table, each tag can occur multiple times - each rule falls through from top to bottom.

In the second table, each tag can also occur multiple times - each tag will have different min/max/step values.

Also, each tag may be used by different clients, hence the separation into two tables.

My question is to the database gurus out there - what's the best way to design these tables to take better advantage of the database, and provide some better validation?

I assume there's a M2M relationship between the two tables - with the intermediary table storing 'tag', right?

Is there a better way of storing the above?

Cheers,
Victor