Formulas in Database Fields
Why store formulas in database fields ?
Colbert offers the possibility to execute formulas that are stored in database fields. We call these fields formula fields.
Formulas might be relevant properties of an object you want to store in your database. For example:
What are the key benefits ?
By storing formulas in database fields you can solve problems within your DBMS by simply writing a SQL statement.
If it’s so simple, why is it new and innovative ?
First of all, we normally try to solve problems with the tools that are available to us. So when computer scientists are confronted with a problem involving formulas like above, they are almost always convinced they can solve these problems with their database tools. But after some unsuccessful attempts they find out, they do have to write a program.
At the same time, managers can’t believe that their IT department does not have a simple solution for these kind of problems.
So, yes. It is innovative and does not exist elsewhere ! We would love to hear from you if you found out differently.
What is the difference with calculated fields ?
The functionality of formula fields is often confused with calculated fields.
So, if every employee receives a bonus according the same formula, you can use a calculated field. However, if for every employee a different formula for the calculation of the bonus is applied, you need a formula field to store and execute it.
Is it difficult to use ?
Absolutely not! Colbert uses a very simple concept to store and execute formulas. It is hassle free and very intuitive in use. You can use it without learning additional skills as if it has always been there. It is seamlessly integrated in SQL.
Example: An Employee has been assigned a bonus formula which is stored in the formula field Bonus. This is Listed in the table Employees. Their Sales performance figures are listed in the table Sales.
The challange is to show the individual annual calculated bonus. In Colbert SQL you just call the formula field by its name and the bonus will be calculated in the context of the parameter values. Simple and intuitive !
Just think how much effort it would take to solve this without formula fields !
Show the individual annual employee bonus.
Name, Year, Bonus from Employees natural join Sales
where Year between 2016 and 2018
How does it work ?
The best way to experience the power and simplicity of formula fields is to see it in action. Therefore we have created a couple of educational videos that go through the various steps we just described.
Bonus calculation: Every employee has a different bonus formula.
video 1 Store formulas
video 2 More examples
Simplicity and Elegance
In order to make it simple and elegant we defined some objectives:
video 3 Concept