4

So I am changing my code over to a table driven formula system to make it sustainable in the long term.

So basically I have 5 foreign keys that will get me to a single formula to use. However I'm not quite sure how I should store the formula. At the moment I am just using a char(100) and parsing it in Java on retrieval. This seems risky though.

My formulas are quite simple (.9 * inputBoxA) but it just gives me a bad feeling doing it this way. Is there a standard for this or will I just have to push on?

Edit: Let me explain my situation a bit more. I have 5 foreign keys all based on user input. One other input is a rate that the user gives. So based on the 5 FKs I want to do a calculation on this rate.

The formula will usually be (inputRate), (.9*inputRate), etc. But it can get a bit more complex (.9*(.6*inputRate-.1)) but still nothing too bad.

I will be retrieving the formulas from my database with a JPA query and process it in Java so the user should never directly see the formulas. However in the future new formulas may be necessary so I need some standard for others to be able to enter them.

7
  • what is "table driven formula system"? Decision table?
    – gnat
    Commented Jul 18, 2015 at 20:49
  • @gnat I suppose a decision table is what most resembles what I want. I'm not really having an issue on how to access the data though. It's how best to structure my end result so that my application can interpret it. Keep in mind I have approximately 1000 possible formulas and I need to be able to add formulas without any changes to the code.
    – Locke
    Commented Jul 18, 2015 at 21:52
  • Why do you have formulas and how do you use them, and what for? You should edit your question to improve it! Commented Jul 19, 2015 at 3:04
  • 1
    Your edit is not enough. Foreign key are an irrelevant implementation detail. What is the formula useful for? Who is typing it? Commented Jul 19, 2015 at 3:16
  • 1
    asking for "best" anything is automatically asking for opinions...
    – jwenting
    Commented Jul 19, 2015 at 6:40

1 Answer 1

8

It is a matter of opinion. You might use postfix representation (à la RPN, or even some stack-oriented programming language), or some bytecode (specific to your expression language), or some s-expr syntax, if you want some more compact representation. JSON is probably a less compact textual representation but it is very widely used (and you've got many libraries to parse JSON); you could define your JSON format for your expression ASTs.

You could also store a compressed form of your formula's representation in your database.

You might consider using some bytecode of some existing interpreter, e.g. Lua, Guile2, or even some JVM class with your own class loader, etc...

And perhaps you want to store closures, not expressions.

You may want to embed an interpreter inside your application.

You need to learn more about programming languages (e.g. read Scott's book: Programming Language Pragmatics), compilers (read the dragon book) & parsing & interpreters & semantics ; if you know a bit of Scheme or Lisp (and learning it thru SICP is not a loss of your time) I strongly recommend Queinnec's book Lisp In Small Pieces, giving a broad view of interpretation & compilation techniques.

MathML is a standard for representation of mathematics, notably formulas (using XML). But it is not compact and not easy to parse, so very probably overkill to represent expressions.

OpenDocument is a standard for spreadsheet & word processing documents, so contains a notation for expressions (apparently related to MathML).

You can use some DBMS (from sqlite to PostgresQL, MongoDB, MariaDB/MySQL) to store your formulas (as TEXT or BLOB in SQL sense), you'll be able to store millions or billions of them (e.g. have some SQL TABLE containing them, one row per formula).

Don't forget to document the format of your persistent data, and the form of the "expressions" and/or "closures" in it. Even your advanced users could need to know that format and use it in some other way without having to reverse-engineer it.

Perhaps you are re-inventing business rule management systems or expert systems (look into CLIPS & JESS) or some scripting language (then perhaps try to use an existing one, not invent your own) or spreadsheets ...

I don't see any risk in storing your formulas as text, provided you define and document very well the syntax of your formulas (e.g. using EBNF) and their semantics (e.g. an operational semantics); publish that documentation (advanced users would need to know about them). Parsing efficiently short formulas is not a real issue (the disk I/O or database access would probably be the bottleneck, more than the parsing of the formula) : you can write a simple recursive descent parser or use a parsing expression grammar. Evaluating a formula is more difficult (and requires a semantics, and some evaluation strategy). You'll need some environment to deal with your variables and their binding! Read also about higher-order abstract syntax. Don't forget to validate a formula before storing it.

You might even take a metaprogramming approach and translate these formalas into a routine (or a method, or a class, etc...) or something runnable...

Not the answer you're looking for? Browse other questions tagged or ask your own question.