I'm trying to design a table-driven routing engine for a contract management application that will compute appropriate reviewers and approvers given a set of input criteria. My platform is Sharepoint with K2 and InfoPath 2007 (I'm working on the InfoPath form, a colleague is working on the K2 workflow). The results will be used by K2 to route the contract form (an InfoPath doc) to the next person, and in the InfoPath form to list the reviewers and approvers and capture their approval and comments.
Since we're using SharePoint, the obvious options for implementing the table are pretty limited (SharePoint Lists). I'd like some advice on the design of the table(s), and some suggestions on the best way to implement them and the code that operates on them.
Here's a simplified sample of the rules tables I've been given:
Criteria
id IsCapital IsBudgeted Region
01 T T 1
02 T F 1
03 F T 1
04 F F 1
05 T T 2
06 T F 2
07 F T 2
08 F F 2
Approvers
id Role >$0 >$50K >$100K >$150K >$250K
01 reviewer1 n/a PM PM PM Dir
01 reviewer2 n/a n/a Dir Dir Sr.Dir
01 approver1 PM Dir Sr.Dir Sr.Dir VP
01 approver2 n/a n/a n/a VP Sr.VP
...
id Role >$0 >$25K >$50K >$100K >$150K >$200K >$250K
05 reviewer1 n/a PM PM PM PM Dir Dir
05 reviewer2 n/a n/a Dir Dir Dir Sr.Dir Sr.Dir
05 approver1 PM Dir Sr.Dir Sr.Dir Sr.Dir VP VP
05 approver2 n/a n/a n/a VP VP Sr.VP Sr.VP
05 approver3 n/a n/a n/a n/a Sr.VP n/a Pres
There are a lot more criteria (about a dozen columns), and a bunch of separate approver tables. As you can see, one of the big differences among the approver tables is that the cost columns differ both in count and potentially in amount (i.e. even if the count of cost columns is the same, the amounts they represent may be different from one table to the next).
I'm trying to flatten this into one big table -- that seems to be easiest to deal with in SharePoint. But the differences in count and amount of the cost columns are giving me a lot of trouble. I can try to break the cost columns into more columns based on least common denominators, repeating approvers horizontally, but that would be a nightmare to maintain -- as soon as the business changed a cost threshold the entire table would have to be restructured.
I can try to keep a bunch of separate tables and try to relate them, but not sure how to query across multiple tables either from Sharepoint or InfoPath.
This is my first time coding for SharePoint, and I'm not very familiar with the APIs and techniques appropriate to the task. I'm open to using C# in codebehind or web services, but not clear on the APIs available to relate multiple sharepoint lists. Someone suggested LINQ as a query option, but I haven't used that yet either -- how would I use it to relate separate lists?
I could put all this into SQL Server, where it's much more straightforward to relate the tables -- but it becomes harder to maintain the rules tables, as I'd need to build administrative front-ends to allow business users to edit the tables. With Sharepoint lists, I get that editing capability basically for free via datasheet views on the lists.
Can anyone suggest appropriate data structures, storage techniques and query techniques for this problem? Thanks!