Disclaimer: When I wrote this I was not an employee of Oracle, a RDBMS vendor. I am now, and you should know that.
First, please go read Karen Lopez’s rant on Normalization Myths here.
Now, a bit of my experience with relational database theory, design, and practice.
As a senior Computer Science undergraduate I took a 200 level course on ‘Databases.’ We were taught set theory, a bit of algebra, Boyce-Codd, what it means to have a ‘relational database’, first, second, third, and Boyce-Codd normal forms, and we used Oracle for our examples and lab work.
In my first (and only interview) coming out of school, the President of the software company asked me to explain what 3NF really meant. I totally flubbed the answer, but still managed to land the job. That was about 13 years ago, and I think I have a pretty good practical understanding of database design.
What I have a really good understanding of now is the frustration and problems caused by software vendors that hard-code their relations into their software and completely neglect the database. It’s so common that I would say it happens more often than not. One of the biggest offenders is in fact a database vendor, AKA Oracle!
Why don’t vendors have foreign keys in their apps?
Great question! I don’t have a good answer. My theory is that most systems are designed by developers who don’t understand databases or how their data works. But, here are the most common responses I hear from architects or DBAs when ‘confronted’ about the lack of normalization and foreign keys in their database:
- It’s bad for performance
- We need a design that can work on multiple database platforms
- Muttering and staring at shoes or something over your shoulder.
I am not going to debunk the first two points, but I will say that I think they are both junk. Here is one example that proves adding foreign keys actually IMPROVES performance.
What I Think is Really Happening
Someone a really, really long time ago heard that foreign keys caused performance issues or didn’t understand why they were important, so they weren’t considered during the design phase. In many cases there may have been no data architect and instead an application developer designed the data model.
Their system ‘works’ and now many years later it would be extremely expensive to fix the model. No one wants to spend money on a problem that isn’t creating problems for the end-users. To expect a vendor to completely re-design their database with no promise of additional revenue will only cause more disappointment.
Who Really Suffers
The people that support the system suffer. If they need to fix bad data or figure out what tables are in play for a complete ‘record’, then it’s going to be a nightmare. The business analysts suffer because it will take them many months to get the relationships straight when the presence of foreign keys would literally draw the picture for them in minutes. And of course the data suffers. Since the application is the only one preserving the business rules, any one that accesses the data outside the application is likely to screw it up.
I hate, hate, hate this. It’s unacceptable. If I were a customer evaluating a multi-million dollar ERP or CRM system, I would totally give mega-bonus points to a vendor that attempted to normalize their data model and used foreign keys to preserve the relationships IN the database. I’ve talked about this before here, if you can’t get enough of my foreign key rants
What Can You Do?
If you are ever tasked to build an application and you are going to store data in a database, spend some time and design a proper data model. If you don’t know how to do that, find someone who does and pay them good $$$ for their advice. This is a design decision that will affect you and your customers for decades.
PS I wanted to call this post Turd Normal Form, but I chickened out 🙁
11 Comments
I thought this was normal only here in Brazil, I worked for a multinational company in the energy sector, where I dealt with 12 systems written in C#, ASP, and VB. The database used was MS SQL. These applications had zero foreign keys.
Nah, you’re NOT alone.
I got my first break into the Oracle world in June 2018. I have mainly been maintaining existing schema’s, but I am starting to get into design. I started working with the Oracle SQL Developer Data Modeler. Really enjoying the articles to see that it is never easy without user feedback.
Pingback: You Know What Grinds My Gears? – Database Design Edition
I agree. I started my career as an application developer. I had the good sense to let the data architects and DBSs design the database. It was my job to code according to the designed database structure and handle the situations where RI kicked out an exception record. Too many devlopers are, I am sorry to say, lazy. Also, too many people think they know all subject matter relating to data mangement, but in reality, don’t. Having moved from app developer to DBA, then to ETL, then to a full-fledged data management role and title, I now know all the benefits of coding as many busimess rules into the database and letting the app developers deevelop to that model. This allows the database to handle many future enhancements, minimally impacting the app code.
All we can do is keep trying to reach out and help people – whether they be developers, DBAs, or architects. Jeff – do we ‘know’ each other? On Twitter, have a blog?
Hi,
in comments and in the article too i found idea that developers maybe made bad decision having worse knowledge than architects. My experience is that architects has so much worse knowledge of technical details e.g. oracle. In my experience on our projec i was as developer the one who wants fks but architects made desicion that there will be no one. Reasons: maybe we will have some schemas on other machines in the future, disabled fks are wasted of our time enabled force some rules maybe we need break this rules in the future. And lot of other !@#$.
Yeah you need make good analysis of business rules and if you change them u need change your code. More simplier and maybe cheapest is don’t implement this br on the start. So my opinion is that this bad stuff coming from architect and managers which know nothing about cbo, oracle, perf.
From my point of view who is bad but has some technical backroung – can talk about technical stuuf is became architect. Who can’t talk about tech background became manager. Who is good and has knowledge stay as developer or dba ;).
Jakub.
It appears we have no shortage of bad architects either. Keep up the good fight Jakob, and thanks for sharing.
Not entirely accurate either. You could define FK’s in Oracle 6.0.36 and they would be enforced in any SQL Forms code referencing those objects – just not in the database. And since Oracle apps back then were written using forms 2.3 and then 3.0 – if the app designers and programmers had followed good design they would have inlcuded fk’s. But it’s the same old story dba’s and architects see everyday – app developers don’t understand database features and thus don’t use them. That’s why the new NoSQL and other cloud databases are popular with developers – there is no design which fits the way most work.
I have a different theory: check the history!
Other EBS systems might have additional reasons NOT providing foreign keys, maybe due to different implementations on different RDBMS systems. This is not true for Oracle EBS.
But still there is one big reason:
Oracle EBS started somewhen in 1987 / 1988. At that time Oracle RDBMS Version 6 was out.
Foreign keys where introduced with RDBMS 7 in 1992 – EBS was somewhere between Version 8 and 9 at that time.
At that time it made little sense to go through all the core code again and implement foreign keys.
(all the numbers are not from official Oracle historiography, but should good enough).
So, it’s just not there as it was not at the beginning.
I assumed they were there from Day 1, or at least Day 2! I’m trying to find the docs now for 2-6 to see for myself.