current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 20 down vote favorite
2

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?

share|improve this question
19  
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. –  MainMa 2 days ago
18  
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. –  MainMa 2 days ago
4  
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 2 days ago
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 2 days ago
2  
There are too many people who claim themselves "architects", but have actually not experience in coding or maintaining bigger systems. –  Doc Brown yesterday

2 Answers 2

up vote 60 down vote

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

share|improve this answer
13  
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 2 days ago
6  
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 2 days ago
3  
@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. –  MichaelT 2 days ago
3  
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. –  Snowman yesterday
1  
@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 yesterday
up vote -1 down vote

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

-Joel Spolsky

Overstated, 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. You 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.

share|improve this answer
10  
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 2 days ago
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 2 days ago
9  
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 2 days ago
2  
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.) :) –  BrianDHall 2 days ago
3  
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 2 days ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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