Should we use foreign key constraints when saving domain models?

Question

Some time ago, I had a discussion with my colleagues about saving Domain Models and whether to apply external key restrictions in the database level.

My first reaction was that the very use of a relational database implied the application of such restrictions, but some argued that the database should be considered as a persistence mechanism, so we should avoid placing any business logic in it. We ended up not using foreign key constraints.

Here's my question (I hope it’s not too general): is it considered good practice to apply key constraints in these cases?

Answer 1

Use constraints, but don't rely on them in your business logic

• There is no business logic in the database: I agree with this principle. And if your non-SQL business code relies on database constraints to verify database consistency, you should rethink your business logic.
• There is nothing wrong with the fact that your business logic requires an additional database constraint. Moreover, such things as referential integrity with FOREIGN KEYS and other UNIQUE restrictions are easy to use, and RDBMSs make this work for you very efficiently without any special maintenance.
• Will you use indexes in the database because it is not related to cleanliness?
• Finding and fixing software errors can take some time, but you definitely don't want to spend even more time cleaning up or (worse) losing some data, simply because you saved yourself from having to write a one-line script for FK . Actually: do you get something for free here and reject it?
• [EDIT-1]: can you guarantee that the data in your database will be managed ONLY through your application? There are always exceptions, mainly users with permissions that sometimes (very rarely :-) make mistakes and execute some SQL statements to clear your code, update status (to invalid values ​​due to typos), etc.
• [EDIT-2]: Creating a domain-based model is no excuse for not hiring a good DBA. Using ORM is no excuse for not hiring a good database developer.

But if you and your team will be able to write error-free and handle all possible exception scenarios in your code (including hardware / network / dummy user / programmer errors), then "Hey, why worry about excessive FK restrictions ..." - -teaser -

Answer 2

I think so, I believe that this is not so much for business logic, but prevents the entry of "bad" data into the database. Once the database becomes very large, these restrictions will prevent headaches in the future.

This is especially effective if you have several developers developing applications against the same data. This ensures that they can only enter valid data. Restrictions controlled in one place and not in x applications are certainly beneficial.

Answer 3

It seems to me that ignoring really useful tools (data integrity at the database level) for a pure development methodology is productive. Databases are REALLY good at such things ... let them do it.

At some point, each methodology begins to collapse, and you just need to be practical.

Answer 4

I thought so, but my opinion has changed since I started writing many resource-oriented systems. As a rule, verification of a part of the data requires much more than just foreign key restrictions, for example, a ticket that has the assigned status must have a valid assign_to value, etc. All these rules should be placed in some specific verification procedure, and although theoretically this may not hinder having additional verification at the database level, if your verification at the application level works, checking the foreign key constraint is just wasted cycles. However, much worse, now you have a logic about the repeatability of your data model in two places - the verification code and database restrictions.

Think of it this way: do you want to move any other application logic to the database (for example, through stored procedures) if you don't need to? If you were not forced to do this for performance reasons, I think the answer should be no at all.

Answer 5

"My first reaction was that the very use of a relational database implied the enforcement of such restrictions, but some argued that the database should be considered as nothing more than a persistence mechanism, and therefore we should avoid placing any business in it. Logic We ended up not using foreign key constraints.

Yes, well, the mediocre majority always wins such a debate with the help of simple number strength, alas.

If you still want to fight this battle, you can ask your opponents how they intend to not use “direct database editors” (ala db2-aid, spufi, ...) and how they intend to keep anyone from corrupting a database using such tools (which, by definition, bypass their programmed business restrictions).

Answer 6

If you want to follow the Driven Driven Design paradigm, then the answer is yes for anything within Aggregate, and no for any cross-referencing.

In almost all cases, you want everything that was in the "Aggregate Root" element to be deleted when the Root itself was deleted, and therefore having foreign keys that represent this, with cascading deletes, will allow you to achieve this at the database level . You can also use your repositories for cascading deletions if you do not want to do this at the database level, but it is still worthwhile that the children of the aggregate should not exist without a root.

For cross-aggregate issues, you are likely to be dealing with business decisions about what should happen when one or the other is removed. Often you will deal with this asynchronously to provide scalability, and therefore your domain model will end up being consistent. Therefore, in these cases it makes no sense to force foreign keys, since there will be times when one or the other key may not exist.

Hope this helps! And for more information, be sure to check out Evans' book on Driven Driven Development - and many links on the Internet too.