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
Best Answer
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.