Re: Data Constraints Vs Application Constraints

fOn 9 Mar 2005 05:05:34 -0800 in comp.databases.theory, gibsong4077_at_yahoo.co.uk (sparky) wrote:

>Dear All,
>
>I have just started work at a new company as a 'Data Solutions
>Architect', and am now patently aware why they need someone with Data
>Architecture experience.
>
>There databases (be they Oracle or SQL Server etc) have no constraints
>other than Primary Keys (thank Codd, for small mercies) i.e. all
>'relationships' are handled/enforced by the application.
>
>I am in the middle of proposing that as a 'first step', before I look
>at data integration etc that the integrity of the data should be
>enforced at the DB level as well as at the application level. Now the
>business arguements, to them will I imagine look pretty weak at first
>i.e. the systems working fine, without errors etc, so we don't need
>another overhead.
>
>As a consequence I am compiling a list of arguements for enforcing
>data integrity within the database (as well as within the app) and
>would be interested in anyone's opinions and/or experiences within a
>similar situation, and what they found to be the key arguements which
>won the business round.
>
>All thoughts, opinions, arguements, musings welcomed.

If you do anything in the DBMS that breaks any application, your credibility will be damaged, possibly irreparably.

If you do anything in the DBMS that could affect any application, you need to persuade someone in the company to come with money, people, and time for retesting applications that currently work.

So, you need to get involved in all maintenance and development projects that are changing code or data, for which there is a testing budget, and get the business, application, and DB people onside to make changes to improve the current situation, so that future changes will require less money from the business, less code from the application developers, and less maintenance from the DBAs, as well as knowing that all the data is following more rigid integrity rules.

You should spend the rest of your time developing models that show the database with the integrity rules that should be there, and which ones are not currently implemented, so you can track progress. Also run checks on the data, to see if any proposed integrity rules are currently being violated, and find out why, in case there are good business reasons behind it, even if it is not the ideal method that could have been chosen for implementation.

If your country has corporate compliance reporting requirements similar to the US Sarbanes-Oxley (SOX) act, you can argue that database integrity rules ensure that business rules are being followed globally and consistently for all data, across all applications.

-- 
Thanks. Take care, Brian Inglis 	Calgary, Alberta, Canada

Brian.Inglis_at_CSi.com 	(Brian[dot]Inglis{at}SystematicSW[dot]ab[dot]ca)
    fake address		use address above to reply