Using Database Constraint or Business Logic for data integrity
Written on 5:52 AM by ronald
Recently, i send an email to a DBA asking some question about whether should we use database constraint or business logic.
This is the conversation i had with her.
Me :
Should we use database constraint or business logic to validate data integrity?
DBA :
I think you should try database constraints as much as possible. Only if there are special or
complicated conditions hard to set data base constraints for that you use business logic. And then
you also have to consider the different entry points in the application. Let’s say in one screen,
a remarks field should be 56 characters. But in another screen, a remarks field must be 100
characters. In that case you set the length to the widest in the database and use business logic
to validate.
Me :
Just to add on, why we avoid using database constraint or foreign keys in our scheme design.
We may have problem archiving data if there are constraints violation
We shouldn't rely on database constraint but instead should use programming logic to execute queries such as removal of child records when parent records is being removed
DBA :
From experience, programmers are highly unreliable. I prefer that the database constraint is there to ensure that a fatal mistake is not made, esp. when the parent child relationship or some other rule HAS to be followed.
There should not be constraints violation anyway, if the constraints are necessary. If there are, it means the data is dirty, so it is time for a clean up.
Ronald please listen to your DBA. Data integrity is critical and must be maintained in the database. Relying on developers and applications will never work in a real scenario.
Ronald,
i would say there isn't a simple answer.
Having constraints in the database is a good idea for ensuring a certain level of integrity. But additionally you'll have to have the same checks in your business logic if you don't want to show the database exception message to your enduser.
I personally would never create for example a foreign key rule that deletes detail recordsets when a master recordset is deleted. In this case you spread the code between database and your code.
As A Java Developer... I like constraints... but as a PHP Coder I don't like constraints....
Think of this ... Why would java and php creators ... put the keyword "final" in the lang keywords ???
you may say not to change the value of a variable ... well then ... why we just not change it ourselves ..!!!
1 usage is to fire errors while compile time .... if assigned a value to the variable other than the original value ....
same as database that fires errors while doing CRUD operations but not allowed by its restrictions ...
2 If the program was opened for outer usage ( a bean or a component ) the final won't be editable ...
same as the databse.. if the structure were used for other applications ..or if it were a public database ... that many applications adds data to it ... the data integrity will be maintained by the database ...
I think you should use database restriction whenever possible ..