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
UPDATE the child according to a rule (
CASCADing) and then
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:
- We want our application to be "Database Independent."
- 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!
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.