August 31, 2015 at 1:33 pm
I'd say that the "* if you hadn’t caught on, just a jest …" comment refers to Oracle, or perhaps not.
Either way, this article sounds like the old, mostly failed argument for object databases. Isolate the data to the object. Relational databases have their place. Building a solid accounting system without FK's would be crazy.
The more you are prepared, the less you need it.
August 31, 2015 at 2:43 pm
I wouldn't know what do do without foreign keys.
August 31, 2015 at 2:48 pm
Iwas Bornready (8/31/2015)
I wouldn't know what do do without foreign keys.
I have some idea of what doo doo would happen without foreign keys.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 31, 2015 at 6:37 pm
rnunn (8/31/2015)
I agree.In addition to eventual duplication, I also think transition to a data warehouse will be difficult at best, and the resulting information unreliable.
A world without foreign key relationships is like a world without genealogy. Who's your daddy?
Course, what happens if you have a table where duplications are allowed from a data source you don't control to stop them? Primary keys become null unless you specifically attach a unique identifier that means nothing.
It's one of the things I face in a OLAP structure that is batch loading data from a system where the duplicates are authenticated, just not uniquely identified from the source system within a single Fact table.
August 31, 2015 at 7:09 pm
Eric M Russell (8/31/2015)
Orphan records can pose serious problem in an operational or OLTP database. However, they are less of an issue in a data warehouse. There is this thing called "late arriving dimensions", where fact records arrive coded with things like Customer IDs or Product IDs that havn't been loaded yet. In a DW where reports are released periodically and most user interaction is in the form of ad-hoc queries, orphan records are more of a nuisance or known issue that gets resolved shortly with a followup data load.
This is true for me in a similar data warehouse environment that is servicing reporting. Late processing dimensions that have not yet been through the ETL system are common when the Facts are given priority first load.
While it's not causing a major issues with the late arrival of those dimensions, it does cause major delays for all source systems waiting on their daily refresh.
August 31, 2015 at 7:12 pm
One thing to note straight out of the gate is that he doesn't specifically say the problem is Foreign Keys. Rather that his solution to his problem is to create multiple databases and we can use Foreign Keys across database.
So the argument is that having a single database behind a set of micro-services introduces coupling within the application. And we know that coupling is bad because SOLID tell us so. So what is coupling and why is SOLID telling us it's wrong?
Basically coupling is when a change in one code module/class necessitates a change to a different module/class. Coupling can't be avoided completely; as we decompose our application/code into modules, these modules still need to communicate with each other. So if we can't avoid coupling we have to control it, we aim to have loose coupling rather than tight coupling. We create modules while applying dependency inversion, interface segregation and (because it helps) single responsibility. But what does all this OO gibberish have to do with data?????? Nothing. But one point to notice is that we don't eliminate coupling, we control it. And by controlling it when we have a code change the impact is minimal and contained to a single module/class. And we can do unit tests providing mocks for our dependencies.
Back to the article where we are told there are several impacts to having a single "monolithic" database. Our application is not stateless (wow a DB application maintaining state :-P). That this in turn makes it harder to test. And also harder to (develop and) deploy our micro-services in isolation. But he doesn't say why, we are left to guess.
Firstly testing. Yeah, creating automated tests that touch the DB are a pain. We have to clean out the DB, populate the DB with test data and maintain it when there are schema changes. I suppose that having a smaller DB for the Order micro-service, one that excludes the Product table, would reduce that overhead. Or you could not include an actual DB in the unit test, but rather mock it. The purpose of Unit tests isn't to provide a complete application test. People argue about how big a unit test should be. You could follow "The Art of Unit Testing" by Roy Osherove where he argues that it should be as small as possible. Or you could follow the 2nd edition of his book where he now argues it should be a "unit of work" or a "use case". We write code to try and make it testable, but splitting a DB up seems like going to far; the tail wagging the dog.
Secondly, deployment of the micro-services. I don't think it's the actual deployment, but rather the isolated development. We have micro-services because we want to role out a change to one service without effecting the others. And I think the problem will be they are accessing tables directly from C#/Java code.
Personally I like stored procedures, they hide the implementation details of my schema from my C#/Java code. I can make schema changes, refactor my tables, and I don't have to change the C#/Java code, I just change the stored procedure. If my C# code is accessing the tables directly, then a change to the table is more likely to require a C# change. And it might impact 2 or more of my micro-services at a time. Now if I truly had a functional change that would only effect a single service, but that necessitated changing multiple services because of the underlying schema change, isn't the problem that I haven't hidden the concrete schema implementation behind stored procedures? Yes I am potentially looking at a explosion in the number of stored procedures. And the pain of keeping track of which stored procedures are no longer used by the C# code. But what is more likely is that the micro-services will be sharing classes to query the database anyway, at which point the service requiring the change gets a new mapper class to use and the existing services continue with their original mappers.
August 31, 2015 at 8:07 pm
Just out of curiosity, it seems like a lot of people are putting a lot of faith in foreign key constraints I'm wondering why? Compared to a primary key or unique constraint where I can tell you exactly what it's enforcing all a foreign key constraint enforces is that some arbitrary matching record exists in the parent table? A foreign key has no way of knowing whether it's referencing the right record at all and no way to handle errors when something goes wrong.
August 31, 2015 at 10:28 pm
Hi Steve, did you proof read this article before posting it. I got used to from you brilliant ideas, samples and solutions to follow, but this latest effort of yours about FKs just does not make sense. It made me feel that it was written to just go on and on to fill the webpage. Disappointing this time.
I hope your next post will be as brilliant as any in the past except this.
regards
Julien
August 31, 2015 at 11:08 pm
ZZartin (8/31/2015)
Just out of curiosity, it seems like a lot of people are putting a lot of faith in foreign key constraints I'm wondering why?
The job of the Foreign Key is to guarantee that the chosen value is from a predefined list of values. It also guareentees that any used values from the parent table can not be deleted because they are referenced in another table. When either of these rules are violated an error occurs that can be trapped and handled properly.
You also mentioned that the value could be the wrong value. Yes, that is a data entry issue and can occurs with unique constraints an natural primary keys as well.
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
September 1, 2015 at 4:37 am
julienchappel 38298 (8/31/2015)
Hi Steve, did you proof read this article before posting it. I got used to from you brilliant ideas, samples and solutions to follow, but this latest effort of yours about FKs just does not make sense. It made me feel that it was written to just go on and on to fill the webpage. Disappointing this time.I hope your next post will be as brilliant as any in the past except this.
regards
Julien
I think that is not a fair charge - Steve is presenting a challenging idea to us from those he has encountered in the course of his work or study. Personally I think it is sensible to challenge any orthodoxy.
However, at least for me, from the article linked, the orthodoxy remains well and truly intact - it's a foolish idea at least for the projects that I work on. No coherent case made IMO. FKs are here to stay - we have had legacy projects without them and it always causes issues.
September 1, 2015 at 5:34 am
Just wondering if we rebrand rdbms and Foreign keys whether it will become the latest greatest toy that developers must now use 😉
September 1, 2015 at 6:25 am
ZZartin (8/31/2015)
Just out of curiosity, it seems like a lot of people are putting a lot of faith in foreign key constraints I'm wondering why? Compared to a primary key or unique constraint where I can tell you exactly what it's enforcing all a foreign key constraint enforces is that some arbitrary matching record exists in the parent table? A foreign key has no way of knowing whether it's referencing the right record at all and no way to handle errors when something goes wrong.
I wouldn't say that anyone has suggested we put more faith in foreign keys than we do in any other construct within the database. When given a foreign key definition, just like with a unique constraint, I can tell you exactly what's enforcing things. It's not a arbitrary row. It's one that is enforced by the definition of the foreign key. Now, can that be the wrong data? Certainly. Just as, with a unique constraint, the data will be unique, but that doesn't make the data right. The structures we have enforce certain behaviors that helps us define data integrity. They don't help us define data rightness or data usefulness. That's a different problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 1, 2015 at 7:01 am
all a foreign key constraint enforces is that some arbitrary matching record exists in the parent table
What does this even mean? Is it arbitrary to ensure the items order actually have a parent that contains the address to which the items should be sent? Is it arbitrary to ensure that invoice lines have an invoice header?
September 1, 2015 at 11:17 am
julienchappel 38298 (8/31/2015)
Hi Steve, did you proof read this article before posting it. I got used to from you brilliant ideas, samples and solutions to follow, but this latest effort of yours about FKs just does not make sense. It made me feel that it was written to just go on and on to fill the webpage. Disappointing this time.I hope your next post will be as brilliant as any in the past except this.
regards
Julien
I struggled a bit with presenting this, and perhaps I missed. I think there may be too many things to follow. FKs, microservices, and more.
The FK part is based on the idea from the linked piece. Perhaps you could explain what is confusing or doesn't make sense.
September 2, 2015 at 6:09 am
If you are the person who will bear the consequences then if you are willing to do so then you can do without foreign keys. Chances are that if you are the person who would bear the consequences then you would be quite emphatic that they should be used.
What is highly unprofessional is to make a decision where people for whom you have no responsibility bear the brunt of your decision.
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply