Data integrity refers to ‘maintaining and assuring the accuracy and consistency of data over its entire life-cycle and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data’. Any unintended changes to data as the result of a storage, retrieval or processing operation, including malicious intent, unexpected hardware failure, and human error, is failure of data integrity. Today we are looking at data integrity from a database point of view, as developers have various options for guaranteeing the integrity of data stored in the database.
Data integrity is imposed within a system at its design stage through the use of standard rules and procedures, and is maintained through the use of error checking and validation routines.
Having a single, well-controlled, and well-defined data-integrity system increases:
- stability: one centralised system performs all data integrity operations
- performance: all data integrity operations are performed in the same tier as the consistency model
- re-usability: all applications benefit from a single centralised data integrity system
- maintainability: one centralised system for all data integrity administration
Furthermore, you can minimise or eliminate the risks and ensure data integrity by following these simple 6 steps:
- Access controls
- Validate input
- Validate data
- Remove duplicate data
- Backup data
- Always keep an audit trail
Databases act as a crucial backbone that form how a company’s data is stored and utilised. There are many Database Management Systems to choose from, allowing businesses to compare data points and query large data sets. Some popular options are: Oracle Database, Microsoft SQL Server, MySQL, PostgreSQL, IBM Db2.
In a database application, you maintain data integrity by using constraints; defining the rules to which the operations (updating, deleting, inserting, etc) have to be performed to maintain data integrity. Ensuring that the data in the tables, that the application manipulates, conform to the appropriate business rules. A business rule specifies a condition or relationship that must always be true or must always be false. These business rules are determined by the database administrator or application developer.
Data integrity is applied in all database models and can be divided into two main overlapping categories: physical and logical data integrity. Let us start with looking at the concept of logical data integrity.
Logical integrity concerns with the rationality and correctness of data, given a particular context present within the relational database. Challenges to logical data integrity include software bugs, design flaws, human error. Logical integrity constraints can be categorised into four subtypes:
- Entity integrity
- No two rows can be the same, each record should be uniquely identified from others. This can be done by the help of the concept “primary key”. these are assigned in each table so each entry of the table is uniquely identified. It is important that the value of the key is not NULL.
- Referential integrity
- Maintaining data consistency between tables. When two or more tables have a relationship, we have to ensure that the foreign key value matches the primary key value at all times.
- Concerns the concept of the “foreign key”.
- Domain integrity
- the validity of entries for a given column. the common ways to input and read this data. The range of acceptable values.
- User-defined integrity
- Apply business rules to the database that aren’t covered by any of the other three data integrity types.
The domain of physical integrity relates to the protection of data as it is stored, used and moved between apps. So it deals with protecting data against external factors, such as natural calamities, power outages, or hackers. Moreover, human faults, storage attrition, and several other problems can also make it unmanageable for data operators to obtain information from a database.
Well-designed databases incorporate the above principles, in such a way that when you change data the database itself prevents you from doing anything that might compromise the integrity of the data. So, it de facto becomes the responsibility of the database to ensure data integrity.
Want to know more about how Pinnaql can help your organisation to achieve and maintain data integrity throughout the entire database life cycle? Schedule your appointment today.
Resources :