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