This thread was also posted here. I got what I believe to be a workable solution, but I was hoping to get some feedback regarding my proposed implementation but also alternative suggestions if anyone has them.
I will be designing a database over the next few months, and there is one problem on the horizon that I think will be a significant challenge.
How to track relationships between agreement terms and products using And / Or conditionals.
I need to be able to accurately model the agreements a customer has on various products, and whether the agreements are concurrent or disjointed.
Some things to keep in mind
1) A single customer could have multiple agreements, covering various products.
2) A single agreement can look something like this (although they get much more complex):
5 of Product_A AND
10 of Product_B OR
50 of Product_C OR
$500 of Product_A
So I am thinking of a table that models the deal's structure like so:
AgreementID
Product
Line
PreceedingLine
TrailingOperator
Count
Cash
55555 Prod_A 1 0 AND 5 NULL 55555 Prod_B 2 1 OR 10 NULL 55555 Prod_C 3 2 OR 50 NULL 55555 Prod_A 4 3 NULL NULL 500
I feel like I am introducing some redundancy here, ie if one agreement line gets removed from the deal, all the PreceedingLine records will have to be updated along with the Line records.
But this is the only way I can think to accurately model this kind of data.
Anyone have a better idea? Any alternative suggestions, or ways to improve this design would be greatly appreciated!