Where to maintain data integrity rules?

01 October,2014 by Jack Vamvas

Question: In database design , is it better to maintain data integrity rules in the database or at the application level?

Answer: Fixing  data integrity rules is critical to maintaining good data. There are disadvantages\advantages to maintaining in the database or at the application level. I prefer to keep the data integrity rules close as possible to the database.

Every database design decision has a cost

 Keeping the data integrity rules out of the database only means redistributing the cost onto the application. The database will look quicker but the overall application response is not necessarily quicker.

How to minimise Bad Data

Maintaining data integrity rules outside of the database increases the possibility of bad data. For example, using the application will enforce rules, but if a user accesses data through another application such as SQL Server Management Studio  and edits data this could cause bad data.

DDL is king of data integrity

DDL should be the start and finish of all data integrity. It is the only way I know ,for sure, that data will follow the rules.  If an application attempts to override the rules , it cannot. A solid database designer can construct rules that maintain a data entity , as deigned.

Read More on data management rules

SQL Server - ISO-11179 Naming Conventions and SQL DDL

SQL server – 5 things SQL Server developers should know about SQL Server

SQL Server Performance Killers

Stored Procedures versus ad-hoc paramaterized queries.Which is faster?


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Where to maintain data integrity rules?


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer