Search code examples
databaseseparation-of-concerns

Database integrity: manage it in DB or App Logic?


Usually, when i write apps that use DB, i try to make sure that the data is consistent by means of the programming language that i use (in my case it is Java), rather than the DB itself. And here is why:

  1. The data management logic is not "immediately visible", that is it is not the kind of code that (at least) I am used to.
  2. It is difficult to keep the DB code under source control.

However, I can see the point of keeping the "data integrity" logic on the db side, mostly that the same DB can be used by other applications (or parts of your own app written by other people) without the risk of corrupting it.

What is the "right way" for this? Are there any criteria for choosing between storing it in DB or in a special layer of your app?


Solution

  • From my perspective defining referential integrity in the database is mandatory.

    You should always define it there. Essentially it's your "last line of defense" and ensures that no external application will mess up your data.

    If your data is worth anything it will be accessed by more than one application in the long run. And it's very likely that your data will outlive the application you are currently building and a new one will take over.

    Additionally having the constraints in the database prevents you from human errors when SQL statements are run manually (think upgrade scripts, mass-imports)

    Most modern databases can also use the constraints to optimize statements. Very often statements that are generated by an ORM contain references to tables that might not always be needed. Having foreign key constraints in place can help the database to remove them.

    Here are two links showing some examples:
    http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/
    http://www.oracle.com/technetwork/issue-archive/2009/09-may/o39asktom-096149.html

    And last but not least: they document your model. If you look at a database without any foreign key constrains you have no idea how the tables relate to each other (and some tools even help you writing your statements if they can detect foreign key constraints in the database).