Referential Data Integrity: Necessity, nice-to-have, or old hat?

Question

Frameworks like Rails have encouraged moving a lot of the logic, even stuff like constraints and foreign keys, off the database - in my opinion. for the better, as it's more manageable and easy to change. Even so, some operations are easier faster, or just plain only possible in SQL.

The recent explosion in popularity of noSQL databases like MongoDB, Cassandra, etc, have change the approach to best practices in database development even more radically.

My question: is referential data integrity no longer a necessity?

I realize it often comes down to choosing the best tool for the job, but let's exclude financial applications and similar type apps where having transactions is a must-have and focus on more typical apps that make money but don't require banking-level integrity.

How necessary is referential data integrity? Can someone list some issues they have had when they're not using it?

Is using a database like PostgreSQL for more critical data, and MongoDB for less critical but highly requested data the smart strategy? How do you suggest defining exactly what data is "critical" and what is "non-critical?"

Answer 1

I have worked in a company (ebay.com) where the databases are huge. We are not supposed to use any referential integrity whatsoever in the database. This restriction was put in place keeping in mind the performance factor alone. We will not even define anything in the ORM (Object Relational Mapping) level. Everything has to be logically handled. I know its a bit tough to even imagine, but still thats what provides a better performance.

Now for your question, with too many abstractions happening at the ORM level, people do not even care about what is going on the database side. At least the new ones coming out to coding hardly take care of writing Triggers, declaring referential integrity directly in a database (such as oracle) where you can do lots by writing store procedures. But still people prefer and feel easier to code everything at the ORM level. So, IMO, I feel that its becoming a old hat.

Answer 2

I think your final comment about having two data stores is the future for most of the new mid-sized apps coming out. One backend with referential integrity for things like connecting core components of the site and another one for larger, Internet scale data.

Legacy companies like eBay shouldn't be used as a comparison since they have the resources to do rigorous QA and to think through the implications of everything the developer does. A typical small-midsize startup doesn't have those resources and keeping critical data in a store with referential integrity prevents alot of application flaws from being able to sit silently in your site for a long time.

Check out Django's support for multiple databases. Keep in mind that moving from an ACID datastore to a CRUD datastore is much easier than the other way around.

Answer 3

If you want to associate and refer to data, referential integrity will always be a valid concern. The modern question is not whether it is necessary, but whether to manage it in the traditional sql database fashion of validating foreign key fields through indexes managed by programmers and database administrators. Simple databases tailored to object access might hide traditional data integrity methods or may allow management of issues programmatically as exceptions, or such concerns can be managed manually.

That being said, the traditional methods work well for most applications (although apparently not eBay). Referential integrity seems silly until you have an integrity issue that is difficult to recover from. Since it is trivial to implement, you should start with it and only remove it once a performance need becomes apparent that can't be met by other means.

As for mongo, use it when it makes an application easier to implement and maintain. You definitely can use both if needed.

Answer 4

I think the other thing to consider is the lifecycle of the application and data store. If the data store is useful to the business it is likley to be accessed by more then one application and/or have interfaces to other data stores. The closer to the data that referential integrity sits the less risk of an interface or something else making a bad update.

And while the application you're working on now may now have interfaces what about 7 years down the track? (Apparently the average business application is retained for 7 years) When the business grows other tools will be used (e.g. either by implementation into the same business or by acquisition of another business)

Answer 5

I think the question and most of the answers here seem to be saying the same thing: that data integrity (RI is just one common aspect of data integrity) definitely IS necessary and is still as important to day as ever. Data integrity is probably even more important today than in the past due to increased concerns about governance, regulation and data protection.

It just happens that people are finding that the DBMS doesn't provide the facilities they need so they look to implement integrity rules elsewhere. This is strange, because after all the DBMS is closest to the data and therefore ought to be best placed to implement business rules efficiently. Declarative rules ought to be easier to maintain and validate than procedural ones. Maintaining rules centrally in the database also ought to be more cost effective than distributing the rules throughout many other layers and applications.

My conclusion is that if these things aren't proving to be true for some people, then that actually says a lot about the deficiencies of today's database software. It does not imply that integrity is unimportant - quite the reverse.