Application Wide Data Integrity

October 22nd, 2008

Usually, due to time pressure, the analysis and design stages of a site implementation project are not given the time and resources that they should get.  It is human nature to believe that until someone is actually coding and you can see something tangible, then nothing is really getting done.  In the end management usually applies pressure in a misguided attempt to hasten the development process.  The problem when this happens is that all of the business rules, requirements and issues have probably not been identified.  This results in the possibility that one of the following will occur at some later time:

·         Having to retrofit rules and modify completed and tested code once the rules are identified

·         The complete absence of necessary business rules

·         Inconsistent application of business rules by different developers or within different areas of the application

None of these situations is good and each has the potential to cause lost time, frustration, duplicated effort and the possibility of inconsistent data and upset end-users.

While I’m certainly not advocating taking the Analysis and Design phases to the extreme, there is usually a balance and spending a bit more time on the Analysis and Design phases to ensure that the business rules and issues are resolved will pay dividends later and result in a much smoother and faster development cycle.

One estimate is that approximately 40% of all application code is exception handling (NO_DATA_FOUND, returning more than one row when only one is expected, etc.), so getting the rules correct and applied everywhere they are required within the application is extremely important.  If not done correctly, developer production is significantly decreased, errors are introduced and the development phase is lengthened.  In addition, testing becomes more complicated, debugging is adversely affected and error correction time is lengthened.

For all of these reasons I am a strong proponent of capturing as many business and data rules as possible within the database.  Yes, it certainly takes more time and since this is not routinely practiced on projects the project team may have to struggle a bit due to rustiness or lack of the required skills.  However, in the end project quality is enhanced and development and testing time is significantly reduced.  In particular, no matter where in the application data is inserted, updated or deleted or which developer performs the coding, or even which day a given coder writes the code, the data in the database will always be consistent and always follow the same rules since the ultimate set of rules applied to the data is stored and maintained from one central repository.

With this in mind, the developers of database management systems have gone to great lengths to provide the tools to ensure data integrity and consistency and avoid some of these potential problems.  Some of these tools include:  Unique Key Constraints, Foreign Key Constraints, Check Constraints and Triggers which I will discuss in greater detail in my next post.