28

This question is not about the difference between SQL and NoSQL. I am looking for some rationale for something that really does not make sense to me at the moment (maybe because of my lack of understanding or appreciation).

We have started a new project from scratch using MVC5, Entity framework 6 code first and SQL Server 2008. When the architect reviewed the database schema it was stated that all foreign keys and other such constraints should be removed as this is “business logic” and should be applied within the business layer of the application code.

My opinion is that foreign keys form part of data/referential integrity and do not really mimic business logic. I see business logic as more the process and validation which controls what/when/how/why references are applied. I can kind of understand that unique constraints are arguably business processes, but for me this just complements the logic and forms part of the integrity.

A second argument is the aim is to adopt a NoSQL approach to the data. I found this really unusual and unorthodox: considering the use of SQL-Server 2008, the need for reporting, the data not scaling to terabytes and the lack of consideration towards technologies such as Mongo, Raven, etc.

Has anyone came across such a scenario before? Why would anyone adopt a NoSQL approach in an SQL Server designed for referential data and not want foreign keys?

Improve this question
14
  • 21
    Talk with your systems architect about the rationale of his advice. Either he has a very good reason which applies to your specific case (a reason that we won't necessarily be able to guess, without knowing exactly what your app consists of), or he is just incompetent.
    – Arseni Mourzenko
    Commented Mar 30, 2015 at 14:48
  • 24
    I've seen many databases implemented in a similar way: no FKs, no CHECK constraints—every time those were databases designed by inexperienced coders who knew nothing about database architecture, referential integrity, etc. But it is important for you to discuss it with your colleague, instead of simply assuming that he's incompetent.
    – Arseni Mourzenko
    Commented Mar 30, 2015 at 16:32
  • 5
    I'm slightly confused; you mention using Entity framework (code first)... doesn't that imply that you create Objects (in the C# sense) and then let Entity framework handle building the database equivalents, in which case trying to add/remove FK's etc. is an exercise in trying to outsmart Entity Framework (which is sort of like Mark Twain's quote about trying to teach a pig to sing?)
    – Foon
    Commented Mar 30, 2015 at 16:48
  • 2
    There are too many people who claim themselves "architects", but have actually not experience in coding or maintaining bigger systems.
    – Doc Brown
    Commented Mar 31, 2015 at 6:33
  • 2
    Relevant: thedailywtf.com/articles/The-Mythical-Business-Layer. "If you think about it, virtually every line of code in a software application is business logic." This extends to the database. "But it’s just as important — if not more so — to keep in mind that almost all of an application’s code will be business logic. There are already enough tools out there; your application does not need a generic layer" (emphasis mine). The person recommending this is probably trying to turn the database into one such generic layer. In short, they're most likely putting buzzwords above reality.
    – jpmc26
    Commented Mar 31, 2015 at 9:36

2 Answers 2

Reset to default
74

When he reviewed the database schema he stated that all foreign keys and other such constraints should be removed as this is business logic and should be applied within the business layer.

Then he's an idiot, and some excerpt from your codebase is likely to end up on The Daily WTF someday. You're absolutely right that his approach doesn't make sense, and frankly neither does his explanation.

Try explaining to him that referential integrity constraints are not "business logic"; they're a correctness standard with its own built-in verification. Business logic is about what you do with the data; integrity is about ensuring that the data itself is not corrupt. And if that doesn't work... well, he's in charge. You can either go along with his plan and try to mitigate the damage somewhat, or start looking for a better place to work. (Or both.)

Improve this answer
7
  • 17
    I had a slightly more diplomatic answer that tried to find a (sane) reason why the architect might be doing this. On sober reflection, I'm getting on board with this answer instead.
    – Blrfl
    Commented Mar 30, 2015 at 14:44
  • 7
    Whoa whoa, bad architecture attempts are a reason to go work somewhere else? Damn, I would never have been able to continue working anywhere if I took that outlook.
    – Kzqai
    Commented Mar 30, 2015 at 18:01
  • 5
    @Kzqai there's also the architect fundamentally misunderstanding the architecture. This starts sounding like directive 595 and yes, if someone wanted to force me to use hammers to put screws into a piece of wood, I'll find someone who isn't forcing me to misuse the tools to build something.
    – user40980
    Commented Mar 30, 2015 at 20:08
  • 4
    Referential integrity is a core topic in database theory, not to mention all the databases that support it in the real world. Clearly Andy's coworker is correct in his analysis, the rest of the academic and professional world is 100% wrong. Bonus points for mentioning The Daily WTF.
    – user22815
    Commented Mar 31, 2015 at 2:32
  • 2
    @AndyClark You should quit over this. Reason being is that its a nuclear time bomb in your company's core. Its only a matter of time when the fecal matter impinges upon the ventilator. When that happens, you'd be lucky to work a 72 hour shift, worst case scenario you'd be looking for a job...better to look for a job when you have an income.
    – ArTs
    Commented Mar 31, 2015 at 8:57
2

I've yet to have a reason to create a foreign key

-Joel Spolsky

Blanket statements aside, it is worth acknowledging there are legitimate and strong reasons to chose to not use uniqueness or foreign keys constraints. Most go something like this:

  • Performance. Doing integrity checks with atomic transactions is not free. And frequently, the database is most difficult part of your architecture to scale. Perhaps you already do the checks in your application logic, and would rather not incure a second performance hit.
  • Lack of need. Perhaps your data is dirty, and you are okay with that. This depends very much on what you are doing.

See also What's wrong with foreign keys?

But those don't match the reasons in your question.

This is business logic and should be applied within the business layer.

This reason is a bit odd. Uniqueness and other integrity constraints are very much the domain of an ACID database. Your application code cannot approach the atomicity and integrity guarantees of SQLServer. If you need strong data integrity, you'd be foolish to ignore the database.

A second argument is that they want to adopt the NoSQL approach to their data storage and so do not want such restrictions in place.

The second reason isn't really a reason; it's a conclusion. Though it is true that constraints are a trade-off between correctness and performance, and NoSQL databases bias towards the latter.

Perhaps your system architect has these legitimate reasons in mind, and you misheard. Or maybe he's a blathering idiot.

In any case, there are valid (though not universal) reasons to refrain from using uniqueness and foreign key constraints.

P.S. If you conclude that you don't want foreign keys, it's still okay to use a relational database. As a generalization, relational databases are more mature than their NoSQL counterparts. As a single node, they can even be faster, and a NoSQL abstraction can be built on top of them.

Improve this answer
13
  • 12
    I dare say Joel isn't an idiot, but a witty reply in a podcast, without any explanation is, IMHO, worth even less that a statement from any idiot.
    – Martin Ba
    Commented Mar 30, 2015 at 20:17
  • 12
    Joel is a spreadsheet guy, not a database guy, so his ignorance of standard relational database practices is, I guess, not surprising... No offense, Joel. :-)
    – Eric King
    Commented Mar 30, 2015 at 20:40
  • 3
    Joel's in-context actual quote is technologies, such as LINQ, provide a reason to bother to setup a foreign key. Joel isn't a database administrator and, from searching his blog and being a long-time reader, I can't find anything from him talking about the subject or trying to give advice on optimizing databases, designing data models, etc. Joel's awesome, but he is not now nor has he ever been - or claimed to be! - an expert in the area of databases or data modeling. It's like quoting Einstein about compound interest - or, for that matter, sandwiches. (XKCD reference, you're welcome.) :)
    – BrianH
    Commented Mar 30, 2015 at 20:50
  • 4
    Joel Spolsky is known for having strong, controversial opinions. See FogBugz is written in a proprietary language; Dependency injection is too complicated (btw, this was the most controversial answer on Stackoverflow before being closed); XML databases are slow; etc.
    – BlueRaja - Danny Pflughoeft
    Commented Mar 30, 2015 at 22:24
  • 2
    @BlueRaja: Umm... XML databases are slow, particularly compared to relational databases. Since when is that controversial, or an opinion?
    – Mason Wheeler
    Commented Apr 8, 2015 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.