Search code examples
sqldatabase-design

Database Design Without Foreign Keys


After having worked at various employers I've noticed a trend of "bad" database design with some of these companies - primarily the exclusion of Foreign Keys Constraints. It has always bugged me that these transactional systems didn't have FK's, which would've promoted referential integrity.

  • Are there any scenarios, in transactional systems, whereby the omission of FK's would be beneficial?

  • Has anyone else experienced this, if so what was the outcome?

  • What should one do if they're presented with this scenario and their asked to maintain/enhance the system?


Solution

  • I cannot think of any scenario where, if two columns have a dependency, they should not have a FK constraint set up between them. Removing referential integrity may certainly speed up database operations but there's a pretty high cost to pay for that.

    I have experienced such systems and the usual outcome is corrupted data, in the sense that records exists that shouldn't exist (or vice versa). These are the sort of systems where people believe they're okay because the application takes care of it, not caring that:

    • Every application has to take care of it, rather than one DB server.
    • It only takes one bug, or malignant app, to screw it up for everyone.
    • It is the responsibility of the database to protect itself! That is one of its best features.

    As to what you should do, I simply put forward the possible things that can go wrong and how using FKs will prevent that (often with a cost/benefit analysis "skewed" toward my viewpoint, if necessary). Then let the company decide - it is their database, after all.