patternMinor
DB constraints in face of quickly changing requirements in an agile environment
Viewed 0 times
quicklyconstraintsenvironmentfaceagilechangingrequirements
Problem
I am in a team where requirements are changing often.
I see keeping constraints in application layers makes our life easy.
If I am going to follow the best practice of specifying most constraints into DB, I see hard times for my team members. When a requirement change needs a DB constraint change, we have to migrate the whole data. And migration seems to be a big headache.
What should we do? What is the best practice keeping in view that requirement changes very often?
We want to keep DB schema flexible and yet have data consistency.
What constraints can we still impose on DB while keeping things flexible enough?
I see keeping constraints in application layers makes our life easy.
If I am going to follow the best practice of specifying most constraints into DB, I see hard times for my team members. When a requirement change needs a DB constraint change, we have to migrate the whole data. And migration seems to be a big headache.
What should we do? What is the best practice keeping in view that requirement changes very often?
We want to keep DB schema flexible and yet have data consistency.
What constraints can we still impose on DB while keeping things flexible enough?
Solution
As a data professional, I'm going to tell you straight up to leave the constraints in the database.
However, there's no right or wrong answer, because it depends on what risk you're prepared to deal with.
If you don't want relational integrity, use XML or JSON or some other thing that isn't an RDBMS. But if you want to use SQL Server or Oracle or PostgreSQL, go right ahead and manage it in the app.
Somewhere along the line, though, there will be data corruption and orphaned records. That risk is real, but I can't tell you how big of a risk it is.
If the effort of fixing corruption like that is less than the effort of maintaining constraints in the database, that's a manageable risk.
On the other hand, if you can't bear the thought of fixing corruption or orphaned data, then leave the constraints in the database and spend some money on the right tools to help you with database changes and migration.
P.S. "Agile" is not an excuse for bad design or lack of documentation.
However, there's no right or wrong answer, because it depends on what risk you're prepared to deal with.
If you don't want relational integrity, use XML or JSON or some other thing that isn't an RDBMS. But if you want to use SQL Server or Oracle or PostgreSQL, go right ahead and manage it in the app.
Somewhere along the line, though, there will be data corruption and orphaned records. That risk is real, but I can't tell you how big of a risk it is.
If the effort of fixing corruption like that is less than the effort of maintaining constraints in the database, that's a manageable risk.
On the other hand, if you can't bear the thought of fixing corruption or orphaned data, then leave the constraints in the database and spend some money on the right tools to help you with database changes and migration.
P.S. "Agile" is not an excuse for bad design or lack of documentation.
Context
StackExchange Database Administrators Q#149202, answer score: 7
Revisions (0)
No revisions yet.