1

According to some database administrators for our org, it is generally not advisable to actually enforce foreign-key relations within our MySQL DBs. Instead, it is preferred to simply represent this with a pseudo foreign-key ID column and to instead perform extra application processing for the foreign-key coercion. The reason is that inserts and deletes (especially cascading ones) become prohibitively expensive as the database scales out.

But does this not defeat the purpose of a RBDMS to begin with? From what I understand, it seems that one of the biggest reasons to use a RBDMS to begin with (besides enforcing ACID properties) is to ensure application query processing involving related (i.e. bound by junction tables via FKs) objects are minimized.

Is it in general more practical to utilize pseudo foreign-keys with additional application processing then? And, if so, why would you even want to use an RBDMS? I presume the reason would be that the application processing would still be slightly less (and way more straightforward) than a NoSQL solution?

6

TL;DR. Unless you have a compelling niche case, then make full use of the capabilities of your RDBMS!


Hmmm... you say:

some database administrators for our org

What do the others say? You also say:

it is preferred to simply represent this with a pseudo foreign-key ID column

What, exactly, is a "pseudo foreign-key ID column"?

The whole point (and greatest benefit) you obtain from using FKs (foreign keys) is that you have automatic enforcement of DRI (Declarative Referential Integrity) - see below. This functionality of the database system is deliberately inflexible when changing data. This is for your developers' benefit, your DBAs' benefit, your company's benefit and most importantly of all, for your customers' benefit!

The modern RDBMS (most of them anyway) is a complex programming environment in its own right that you can leverage to maintain maximum control of your data as close as possible to your data.

You can effectively have fine-grained control of the data next to the data which is the best place for it. Your database is the last bastion of defence against rogue applications (honest errors for the most part) and/or sysadmins (again, mostly honest) modifying your data in a way that will render it inconsistent. Inconsistent data can be worse than useless - if you know your data is corrupt, you can restore from backup, but if it's inconsistent, mission-critical decisions could be made on the basis of erroneous premises!

Your DBAs say

that inserts and deletes (especially cascading ones) become prohibitively expensive as the database scales out

Yet, you appear to be implementing your home-baked RI scheme using pseudo foreign-key ID columns? This adds its own costs - e.g. for an INSERT into a child table, you have to (in the app - and all within one transaction) query the database (presumably checking for the existence of parents?) and then do your (consistent) INSERT and then COMMIT? For a DELETE and/or an UPDATE on a parent, you have to check for the existence of children, then DELETE or UPDATE the child according to a rule (CASCADing) and then DELETE/UPDATE the parent all within one transaction. Setting DRI in the database removes all that overhead! See here and the rest of that thread.

In my opinion what becomes far more prohibitively costly is lost data, orphaned records, childless parents and late-night calls from irate customers threatening to pull the plug on your company and its software!

Yes, there is a certain price to be paid for enforcing DRI. Your DBAs are suggesting that you enforce this in the app - yet this is also costly and it's far more expensive, far more error-prone and far more time-consuming to do it in the app than it is to do it using the RDBMS (see example above).

  • Apps come and go.
  • Languages come and go (apart maybe from C).
  • Frameworks come and go.
  • It was XML 10 - 20 years ago, now it's all JSON...
  • Fortran/Cobol -> C -> C++ -> Java -> Scala -> Clojure -> Go -> Rust -> whatever-you're-having-yourself...
  • Data is permanent (or at least, very long-lived in comparison to the above)!

The youngest of the mainstream RDBMS products, MySQL, was first released 23 years ago (as of 2018, although the system was being developed in-house from the late 80's) and it was only meant to be a lightweight copy of systems such as Oracle (1979), Ingres (mid-eighties or earlier - was important at the time), Sybase/MS SQL Server (1984), PostgreSQL (also mid-eighties or earlier) and Interbase/Firebird (1984 - also important at the time).

So, these systems have been around a lot longer than your app.

What your DBAs are proposing is reinventing the wheel - a wheel which works very well for millions of users every day in banks, supermarkets, factories, offices and warehouses (non-exhaustive list) all over the world.

There are a few main issues here.

  • Who knows? Maybe you have an excellent team of hot-shot programmers who can implement mainstream RDBMS capabilities in, say, less than 3 years?

    Ehh... why am I not buying that scenario? Well, the "cheapest" (but by far the better IMHO) of the two Open Source systems above - PostgreSQL - cost $15M and 269 work-years of effort and MySQL cost $63.5M and 1155 work-years. Do you think your team can match that?

    You have your database software - by trying to circumvent/rewrite its DRI capabilities, you are literally throwing away the years of programming effort and testing by them and (effective) testing by their (literally) millions of end-users.

  • Suppose, one bright, sunny morning, your boss/customer/tax-authorities/whoever decides that you need to write a new application to work with your database? You'll have to implement all of your DRI all over again! And probably in a different language because your boss has read in some in-flight magazine that X is the language du jour. It'll be like déjà vu meets Groundhog Day all over again!

    You then hire a contract whizz-kid programmer who wrote a system in X for their undergrad thesis. They don't know that column_A in table_W is meant to be the child of column_B in table_Z, so they happily produce hundreds of lines of code a day, that work swimmingly and quickly on your test data. Then it lands with the customer and they start to notice that records are going missing - sums/record-counts are incorrect. Your contractor has moved on to a startup (Cowboys 'R Us) and you're left holding the baby! (This scenario could also pan-out in your current set-up).

  • Check out these three articles

    • I can't wait for NoSQL to die - an admittedly jaundiced view of NoSQL, but not so much if you consider the following articles,
    • what happens when the rubber meets the road and (ahem...) advantage was taken of NoSQL's eventual consistency (BASE) model,
    • Finally, how NoSQL systems are now finding that SQL and consistency weren't really that bad after all - come back ACID/SQL, all is forgiven!

I'm not saying that NoSQL systems don't have a "niche", it's just that a niche is precisely what they do have (much in the same vein as above). Unless you have very specific needs which are clearly aligned with a NoSQL use-case, then they are best avoided (IMHO).

If the above hasn't convinced you, then consider these final points:

  • Read this - point no. 2 from Jonathan Lewis. He wrote a book (530 on Oracle optimiser fundamentals), so he knows a thing or two about databases!

    More war stories, for fans of Chapter 8! "Now prepare yourself to read all about 'The World's Worst Oracle Project.'" - Jonathan Lewis.

    This chapter describes some of the most common mistakes in development Oracle database applications. You'll certainly recognise some of them, because so many people stubbornly cling to certain beliefs. I know I like to bring up several of his points when I get into common arguments like these:

    1. We want our application to be "Database Independent."
    2. We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.
  • Look at Brian Aker's amusing talk on NoSQL here - he makes the point that NoSQL is OK for ad-hoc queries where you're dealing with stale-ish data, it's just that SQL is great at slicing and dicing data also. The key advantage though with SQL is that you haven't to write a new Map-Reduce job or whatever to analyse your data, you can just write a query, et voilà, you're done! If you're not using FOREIGN KEYs on an RDBMS, you're effectively in a half-way house between SQL and NoSQL!

There are old-wives' tales about FKs being unnecessary overhead. This notion was kept alive by the fact that (for good, but probably now legacy, notably space, reasons) some RDBMSs did not/don't automatically create indexes on Foreign Key fields (this should be done in the majority of cases). This led to the mistaken view that Foreign Keys were overhead without benefit which is not true for the vast bulk of use-cases!

You say:

if so, why would you even want to use an RBDMS?

This is a very good question. If you're not using the RDBMS as an RDBMS, then why have it at all? Just put the data into Notepad/vi! You should have a serious sit-down with management and explain why using the RDBMS' capabilities is a good idea. Furthermore, I would ask your (No-FK) people to justify not using foreign keys. Unless your company has a real and pressing NoSQL niche, then point them to the works of Micheal Stonebraker and/or Jonathan Lewis on this.

Finally, my advice to you, if your company/management insists on pursuing this madness, is to run very far, very fast! You will end up in a situation where you are constantly firefighting, being woken from your bed at 03:30 and worse still, you won't get a chance to improve your skill set or learn anything of value.

  • Thank you so much for the super-insightful response! I will definitely follow-up here with whatever response I get. I completely agree with everything you said. I believe the main concern was that the cost of referential integrity enforcement would outweigh the cost of otherwise doing extra application processing, but like you said this statement doesn't even seem true and you make a ton of room for new error (by lack of integrity enforcement) as well as making everything way more complicated (both to digest and process on the application side). I learned a ton from the linked resources! – Leeren Jun 6 '18 at 9:05
  • Thanks for your feedback - it's always nice to know that one's efforts have been appreciated! There's one final point - some RDBMSs don't automatically create indexes on FKs - this can give an appearance of overhead without benefit. Will add later. Also, forgot this link (no. 3 in articles) - the best one - will add later too – Vérace Jun 6 '18 at 9:10
  • Thanks for the correct answer markup. I tidied up a couple of bits, but I would still be interested in your sytem's "pseudo foreign-key ID column" - maybe a brief outline of how this works? – Vérace Jun 7 '18 at 14:32
  • Apologies for the late reply. The feedback I received was that the performance degradation of enforcing foreign keys greatly outweighs otherwise processing data model integrity through application code. And it seems like the main culprit of all this is insert queries specifically, which really diminishes the ability to scale well. Again, I don't necessarily agree but I also don't have much experience working with large-scale applications. I was told that theoretically, DB-managed integrity through foreign keys is the standard, but for many industry use-cases it is not practical. – Leeren Jun 14 '18 at 3:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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