data integrity with dbms or application stack
My question is very simple and it's more of an advice that i'm seeking. What is better when it comes to maintaining data integrity: DBMS or Application Code?
Example: With DBMS we can开发者_如何学Go use things like Triggers, Transactions, Procedures etc to do ALMOST proper data management and making sure things go and fit into the right place.. Same can be achieved with Application code.
Which one would you prefer in particular?
Application Code or a Combination of both?
Generally you would want to bake any data-at-rest integrity into the DBMS. Referential requirements, data limitations (length, etc.), things like that. The idea is that the DBMS should, in and of itself (disconnected from and not reliant on the application[s] which use[s] it), maintain the integrity and rules of the data it contains.
They key thing to note there is that multiple applications may use this database. Even if that's not the case for a particular database, or not even likely to be the case in the foreseeable future, it's still good design. Not all of the applications necessarily have the same business logic or the same integrity checks. The DBMS should never assume that what it's getting has been checked for integrity.
The application(s) should apply the business logic and maintain integrity for data-in-motion. It should do its best to prevent even trying to persist invalid data to the database. But in any given point in the application it may very well not be reasonable to assume that it "knows" all of the other data in the database. The application can apply logic to the small piece of data it's currently holding, then try to interact with the database to persist it.
But the job of the database is to know and maintain all of the data, not just what's currently being used by the application. So where the application may believe that it has a perfectly good piece of data, the database may disagree based on the state of some other data which the application wasn't using. It's perfectly acceptable for the database to then return an error to the application to tell it that there's a problem with the data being sent. The application should be able to handle such errors.
To sum up...
From the point of view of the application, active data is a small subset of all data and the application is responsible only for the active data. Also, data is in motion and very fluid and part of a richer logical model of business logic.
From the point of view of the DBMS, "active data" is all data and the DBMS is responsible for maintaining the integrity of all data. Generally, data is at rest and static and should at any given snapshot of the tables be "good data."
精彩评论