CODEWITHSUNDEEP
sql-serverdatabasedatabase-designdatabase-schemamultiple-databases
Cellman
Cellman

Reputation: 135

Changing Constraints and Primitive Structure of Database

How is the database supposed to handle requirement of implementing new organizational policies. Which may make obsolete previously valid constraint and can demand new constraints for data in time to come.

If we change such constraint and critical structures as per new policies how old policy data will hold its integrity?

So what is best practice? Separate databases with every new policy implementation. And make a datawarehouse on top?

Upvotes: 0

Views: 66

Answers (1)

nvogel
nvogel

Reputation: 25526

Unfortunately SQL DBMSs are not very good at implementing data integrity rules. ISO Standard SQL has the concept of an ASSERTION constraint for example, but SQL Server and most other DBMSs do not implement it. Constraints between tables are seriously limited because of standard SQL's lack of support for multiple assignment. SQL normally only allows one table to be updated at a time which makes it difficult to implement inclusion dependencies and join dependencies.

These limitations mean that business rules tend to get implemented either in procedural code (stored procedures) or outside the database altogether: using data quality tools, rules engines or exception reporting. Such tools tend to have better support for version control when the rules change over time.

Upvotes: 2

Related Questions