Designing a Data-Based Logic System

Question

I am developing a tax calculation system that applies various taxes based on a set of set criteria.
Information often changes, so I'm trying to create a way to store all these logical rules in a database.

As you can imagine, there is a lot of complex logic to applying taxes.
For example, a tax can only be applied if A is true, B is less than 100, and C is 7.

My current design is terrible.
I have several database columns for filtering very common criteria, such as location and tax year.
For more complex logic, I have a column with JavaScript, and in the code I run an interpreter to filter the results. Performance and maintainability suck.

I would like to improve this design by making the logic completely data-driven, but it's hard for me to figure out how to correctly represent this logic in a relational database. What is a good way to model this logic in a database?

Answer 1

I have been working on this similar problem for over a year now for an application for creating production costs. In the same way, it takes into account a lot of product design data and is based on design and other inventory considerations, such as quantity, bulk purchasing options, parts supplier, electrical ratings, etc. The result is a list of direct materials, costs and expenses.

I knew from the very beginning that I needed some kind of query language instead of a computational one, and it had to be written in a script, not compiled. But I have yet to find the perfect solution:

METHOD 1 - SQL I created tables that represent my objects and columns that represent properties, and then manually enter all the SQL SELECT statuses needed in the item_rules table. What I did was first store the object in the database and then I did

rules = SELECT * FROM item_rules foreach(rules as _rule) { count = SELECT COUNT(*) FROM (_rule[select_statement]) as T1 if(count > 1) itemlist.add(_rule[item_that_satisfy_rule]) } 

What he does is each rule in the item_rules table and run it against my object, which is now in the tables. for example SELECT * FROM my_object WHERE A = 5 AND B> 10. If I pick it up successfully, I get a positive amount, and then I know that I must include the corresponding rule element in the list of my positions.

METHOD 2 - NCALC Instead of storing SQL queries, I found the NCALC open source expression parsing library. NCALC takes a string expression and an optional variable and evaluates the result. String expressions can be saved in text format in the file system.

METHOD 3 - EXCEL EXCEL is actually very good data retrieval software. You can create formulas in excel and then transfer data from your application to excel and then let excel run the formulas to give you the results. The advantage is that many people know how to use excel, so different people can support it.

But, as I said, none of this is perfect for me. I just share and hope that we can get the best recommendations.

Answer 2

If you want to go with Jake's approach, you can also use Dynamic Sql.