0

I need you wisdom, I am debating with another person when is it recommended to put the code of a business rule into a database constraints or inside of the application business layer?

There is a particuliar field that is used in several calculations for the machinerie settings in the plant. Today, we faced an issue because the file maintenance people did not specified a required field that somehow was not validated in the UI of the ERP. That field cause a division by zero error in a calculation that crash the PLC machine and halt the production the time to reboot the PLC machine.

So I filled a request to file maintenance to populate all field that were zero value and a second request to the database administrator to create a constraint in the database for the field so the record get rejected if the required field is 0.

The DBA denied my request and told me to validate the particuliar field in the UI or in the Business logic layer. I told him that even if I do that, someone in Microsoft Access or any other tools with the right permissions could assign zero to the particuliar field and it will again halt the production. He answered me: "so what? do you expect me to reprogramming all the business rules from the Business logic layer into database constraints?"

After that, I was wondering the question "when to put a business rule in the business application layer or into a database constraint"?

Thank you! Sebastien

0

My 2 cents, although I am not nearly as experienced as many of the others on this site.

If it is related to data integrity (either from a relationship standpoint OR a standardized formatting standpoint), then I firmly believe that it should be handled by the database. If it's simply for a nice presentation, then I think that should be handled by the presentation layer.

In the example you site where the value caused a division by 0 error in a calculation .. if that calculation happened outside the database, then I do not believe a constraint on the data in the database is valid at that point. The database shouldn't need to know what the code does with its values and the code shouldn't need to know what the database does with its values...

The short answer (again, in my opinion) - the database should be responsible for delivering clean and reliable data in a standardized format ... what happens WITH that data is up to the person/code that is requesting it.

  • Does it change something if the field in question is "Density", a physical property related the matter (current record), and the field physically cannot be 0 because of the physics laws.It is used to calculate the volume inventory of the company that change because of the weather temperature? – Sebastien DErrico Dec 18 '14 at 22:52
  • Is that were the case, then I would say yes it should. If the number in question is supposed to represent a real-world measurement, then that would be part of making sure the data was reliable. (aka only permitting values that are possible in the real world) However - in the instance of "density", I would still think that the code should be doing its checks and balances as well. (Essentially the DB side verifies that the number is valid .. the code side verifies that the MATH is valid .. which still requires to verify that the numbers are valid) – Joishi Bodio Dec 18 '14 at 23:30
  • Just an example for my above statement .. the DB might have a check constraint to have the number be > 0 ... the code side might have checks and balances to make sure that (for example) division by zero doesn't happen. AKA - the java code shouldn't KNOW that the DB requires the values to be > 0.. – Joishi Bodio Dec 18 '14 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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