Part 2-ensure data integrity
1. Use constraints rather than business rules to force data integrity
If you want to follow the business rules, you should check the business level/User Interface: if the business rules change in the future
, You only need to update.
If the requirement is due to the need to maintain data integrity, restrictions must be imposed on the database layer.
If you use constraints on the data layer, make sure that you have a way to understand why the update cannot pass the constraints check.
Language to notify the user interface. The field name is not enough unless the field name is too long. -Lamont Adams
If possible, use a database system to ensure data integrity. This not only includes the integrity achieved through standardization, but also
Including the functionality of data. When writing data, you can also add triggers to ensure data correctness. Do not rely on the Business Layer
Ensure data integrity; it does not guarantee the integrity between tables (Foreign keys), so it cannot be imposed on other integrity rules.
-Peter Ritchie
2. Distributed Data System
For a distributed system, before you decide whether to copy all the data on each site or save the data in one place, you should
Estimate the data volume in the next five or ten years. When you transmit data to other sites, it is best to use the database Field
. Update your tag after the target site receives your data. For this data transmission, write down
Your own batch processing or scheduler runs at specific intervals instead of letting users transmit data after daily work. Local
Copy your maintenance data, such as calculating constants and interest rates. Set the version number to ensure that the data is completely consistent on each site.
-Suhair techrepublic
3. Force indication integrity
There is no good way to eliminate harmful data after it enters the database, so you should remove it before it enters the database. Exciting
Indicates the integrity of the active database system. In this way, the data can be kept clean and developers can be forced to invest more time.
Handle error conditions.
-Kol
4. Relationship
If there is a many-to-one relationship between two entities and it may be converted to many-to-many relationships, you 'd better set it in the first place.
Into many-to-many relationships. Switching from the existing multi-to-one relationship to the multi-to-many relationship is much harder than the first one.
-CS data effecect
5. View
To provide another abstraction layer between your database and your application code, you can create a dedicated
View without requiring applications to directly access the data table. This is equivalent to providing more information for you when handling database changes.
Free.
-Gay Howe
6. Plan data retention and recovery
Consider data retention policies and include them in the design process to design your data recovery process in advance. Released to users/developers
The Data Dictionary of personnel is convenient for data identification while the data source is documented. Compile online updates for "Update query"
In case of data loss, you can re-process the update.
-Kol
7. react the system with Stored Procedures
After a lot of trouble is solved to generate a database solution with high integrity, my team decided to encapsulate some
The function group of the associated table provides a set of general stored procedures to access each group, so as to speed up and simplify the development of customer program code
. During this period, we found that the 3gl encoder has set all possible error conditions, as shown below:
Select CNT = count (*)
From [<Table>]
Where [<primary key column>] = <new value>
If CNT = 0
Begin
Insert into [<Table>]
([<Primary key column>])
Values (<new value>)
End
Else
Begin
<Indicate duplication error>
End
A non-3gl encoder does this:
Insert into [<Table>]
([<Primary key column>])
Values
(<New value>)
If @ error = 2627 -- literal error code for primary key constraint
Begin
<Indicate duplication error>
End
2nd programs are much simpler, and in fact, we use the features we provide to the database. Although I personally do not like to use embedded text
Word (2627 ). However, this can be easily replaced by some pre-processing. The database is not just a place to store data
It is also the place to simplify encoding.
-A-Smith
8. Search
The best way to control data integrity is to restrict user selection. As long as it is possible, it should provide users with a clear value.
List. This reduces the errors and misunderstandings of the entered code and provides data consistency. Some public data is particularly suitable
Combined search: Country Code and status code.
-CS data effecect
