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:
- formulas that define the bonus you have agreed on with your employer
- the criteria by which you are looking for a second hand car to buy
- the formulas that define the taxes you have to pay per year
- criteria for segmentation of a population
- identification of risk factors.
What are the key benefits ?
By storing formulas in database fields you can solve problems within your DBMS by simply writing a SQL statement.
- You can save a lot of time and money because you don’t have to write a program. Reduce time to production by 40%.
- Your solution is easy maintainable by the subject matter expert or data analyst without help of the ICT department.
- Your solution is well documented as the formulas are readily available in a table instead of external program code.
- It allows you to quickly test ideas and iterate towards a solid solution
- It speeds up discovery, profiling and documentation.
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.
- Calculated fields are fields that are calculated by the same formula that holds for all records.
- Formula fields hold a formula that is different for every record.
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 !
The challenge:
Show the individual annual employee bonus.
Colbert SQL with a Formula in the from-clause:
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.
Video 1
Bonus calculation: Every employee has a different bonus formula.
Video 2
- Criteria matching: Buyers define what second hand car they are looking for.
- Risk analysis: A set of risk criteria is defined with corresponding risk scores.
- Cohort definition: A population is defined into cohorts according to a set of rules.
video 1 Store formulas
video 2 More examples
Simplicity and Elegance
Video 3
In order to make it simple and elegant we defined some objectives:
- No extension of SQL: It must seamlessly fit in SQL.
- Intuitive in use: Use it like it has always been there.
- Strong type mechanism for formulas: Typing helps to write valid SQL statements.
- Orthogonal within SQL: You can use formula fields like any other field.
Based on these objectives we developed a very simple and elegant concept.
video 3 Concept
Contact
- Colbert Databases BV
- A.Groenlaan 83
- 9731 KK Groningen
- Netherlands
- info@colbert.nl
- +31 50 3603915
© Colbert Databases BV 2019