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?

TRiG
  • 1,170
  • 1
  • 11
  • 21
Andy Clark
  • 415
  • 6
  • 8
  • 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 Mar 30 '15 at 14:48
  • I am yet to discuss it further and I will raise concerns of course. I am just curious if anyone else has come across this or implemented something similar – Andy Clark Mar 30 '15 at 15:13
  • 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 Mar 30 '15 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 Mar 30 '15 at 16:48
  • Yes, I am now considering not using Entity Framework which will only add time to the project (more time than needed if you ask me). I'm not sure this is a case of incompetence though I would never say assume that, my aim as already stated... is to understand this approach. – Andy Clark Mar 30 '15 at 20:32
  • Alternately (if we stick with EF) we may have to manually join navigational records manually using linq queries across multiple repositories. But in that case I would rather use stored procedures, but this is (again arguably) business logic... not my preferred approach. – Andy Clark Mar 30 '15 at 21:18
  • As an aside, Entity Framework Code First does not mean that the code has to build the database. You can still start with an existing database (complete with proper primary/foreign keys) and [import the database into a Code First model](https://msdn.microsoft.com/en-us/library/jj200620.aspx). – Eric King Mar 30 '15 at 21:23
  • I am aware of this apprach to EF code first, however as well as building the app from scratch we are scaling the DB from scratch also. Code first generation seemed the best approach before this constraint requirement was realised. – Andy Clark Mar 30 '15 at 21:27
  • 1
    Understood. I was kinda responding to Foon's comment implying that Code First meant that you would be necessarily creating the database from the EF code. You don't have to. --- But, you'll be in for a world of pain if you try to make EF work with a database full of tables with no keys. :-) – Eric King Mar 30 '15 at 21:30
  • Agreed, if the no constraint requirement sticks I will suggest ditching EF in favour of SPs, which an argument will probably be made this is also business logic and shouldn't be in the database. In-line SQL to the rescue!!!! its the future!!! – Andy Clark Mar 30 '15 at 21:41
  • 2
    There are too many people who claim themselves "architects", but have actually not experience in coding or maintaining bigger systems. – Doc Brown Mar 31 '15 at 06:33
  • 2
    Relevant: http://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 Mar 31 '15 at 09:36
  • 1
    This reminds me of another fun [Daily WTF article](http://thedailywtf.com/articles/Directive-595). While I seriously doubt your bosses are actually this bad, it does show what kinds of things can happen when you don't have a strict DB schema to protect you from business layer bugs. – Phil Apr 03 '15 at 03:06
  • Andy, here's an article on what you're going to be doing in the future if you stay: [Bad CaRMa](https://www.simple-talk.com/opinion/opinion-pieces/bad-carma) – Kyralessa Apr 08 '15 at 15:19

2 Answers2

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.)

Mason Wheeler
  • 82,151
  • 24
  • 234
  • 309
  • 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 Mar 30 '15 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 Mar 30 '15 at 18:01
  • 5
    @Kzqai there's also the architect fundamentally misunderstanding the architecture. This starts sounding like [directive 595](http://thedailywtf.com/articles/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. –  Mar 30 '15 at 20:08
  • @MichaelT: Thanks for the link. I *knew* that was TDWTF material... – Mason Wheeler Mar 30 '15 at 20:17
  • Looking for a new job over software architecture is a bit drastic. Not going to throw my toys out of the pram just yet! Not that bad. – Andy Clark Mar 30 '15 at 21:31
  • 4
    [Referential integrity](http://en.wikipedia.org/wiki/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](http://thedailywtf.com/). –  Mar 31 '15 at 02: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 Mar 31 '15 at 08: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.

Community
  • 1
Paul Draper
  • 5,972
  • 3
  • 22
  • 37
  • 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 Mar 30 '15 at 20:17
  • 1
    @MartinBa, the podcast was not about FKs; it was about LINQ and data access. Joel *does* have strong opinions against FKs, but in this postcast, it was a side issue. I would like a better quote from him, but at least this shows the opinion, even if it doesn't also provide the reason. – Paul Draper Mar 30 '15 at 20:25
  • 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 Mar 30 '15 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 Mar 30 '15 at 20:50
  • 4
    Joel Spolsky is known for having strong, controversial opinions. See [FogBugz is written in a proprietary language](http://blog.codinghorror.com/has-joel-spolsky-jumped-the-shark/); [Dependency injection is too complicated](http://stackoverflow.com/a/871420/238419) *(btw, this was [the most controversial answer on Stackoverflow](https://data.stackexchange.com/stackoverflow/query/466/most-controversial-posts-on-the-site) before being closed)*; [XML databases are slow](http://www.joelonsoftware.com/articles/fog0000000319.html); etc. – BlueRaja - Danny Pflughoeft Mar 30 '15 at 22:24
  • @BlueRaja, yeah. That quote is not the most substantive part of my answer. – Paul Draper Mar 30 '15 at 23:07
  • 1
    I'll just give you +1 for your reasoning. – oleksii Mar 31 '15 at 11:11
  • I find the "uniqueness *and* foreign key" part strange: Uniqueness is a matter of enforcing data quality, and as such there may be cases where tossing out any check constraints makes sense. FK ... without them you just have a pile of data, and in many schemas, the orphaned rows would stop to make any sense w/o the master entry. – Martin Ba Mar 31 '15 at 19:31
  • @MartinBa, I agree those are the consequences. What is odd? – Paul Draper Mar 31 '15 at 19:34
  • I meant to say that the rationale(s) for leaving out check/uq constraints vs. Fk ... they should be quite different, not subsumed in the same list. – Martin Ba Mar 31 '15 at 19:38
  • @MartinBa, they are different, but the arguments the the same, in broad strokes. A unique constraint is a check against the same table; a FK is a check against another table. Both require index/table locks, and both vary in how essential they are. – Paul Draper Apr 01 '15 at 00:16
  • 2
    @BlueRaja: Umm... XML databases *are* slow, particularly compared to relational databases. Since when is that controversial, or an opinion? – Mason Wheeler Apr 08 '15 at 14:55
  • @MasonWheeler: He makes it sound like they are orders of magnitude slower, and wrote an entire article on how searching within strings work, completely ignoring the fact that any data lookup will go through an index file first *(this is true in both relational and XML databases)*. So basically, everything in that article is irrelevant. – BlueRaja - Danny Pflughoeft Apr 17 '15 at 21:45