|
Sad but true. I used to support such db at the start of my career
|
|
|
|
|
Trust me, it could be worse.
My company sells a very large application using a SQL Server database. There are over 1500 tables in the database. You can count the number of defined FK relations on one hand and I suspect those were added by mistake.
I have brought this up several times and it's always the same answer. We don't need no stinking FK relations in the database - the application code handles all of that. Of course, the poor support people constantly have to deal with application issues caused by orphaned data, etc.
|
|
|
|
|
Believe it or not, when I first started here, the majority of tables didn't even have primary keys, let alone foreign keys. It's been getting incrementally better since then.
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
txmrm wrote: There are over 1500 tables in the database.
Let me guess. They are named like "AGC1", "RCV32", etc? I worked with business logic once, and got to look at the tables behind BPCS. F me, what a clusterF.
I once created an engineering change notice database which tracked changes to BOMs, and said which notice was on which person's desk, when they were due, which ones were associated with which project, etc. With the BOM table, there were only 32 tables in total. And some of them were just work tables which were repopulated via queries. 1500? Sounds like terribly engineered insanity!
|
|
|
|
|
Is this an asset/liability management system, by chance?
|
|
|
|
|
txmrm wrote: with application issues caused by orphaned data
Then they're not testing properly. A production app should not rely on constraints.
|
|
|
|
|
Are you saying that they removed a column from an associated table? (Very bad)
Or are you saying they removed the foreign key constraint from the associated table's matching column? (not as bad).
The relationships are still there even without the strict foreign key constraint.
They are not just a bit more philosophical. 
The discipline to insure they stay in sync, now just rests with the people and not the system.
Power to the people!! 
|
|
|
|
|
Rob Grainger wrote: Sadly, upon opening it, it became apparent that someone had for some unknown reason decided to remove all the relationships between all the tables, for no obvious reason.
"This is an unsupported version. You're elephanted due to stupidity. Do not pass 'start' and jump of the nearest building to hide your tracks."
I've become good at descriptive exception-texts 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
That someone could have been fan of No SQL world and sees relationship as spaghetti code
// ♫ 99 little bugs in the code,
// 99 bugs in the code
// We fix a bug, compile it again
// 101 little bugs in the code ♫
|
Tell your manager, while you code: "good, cheap or fast: pick two. "
|
|
|
|
|
Or as a tight-coupling to a specific storage paradigm.
"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli
|
|
|
|
|
On my own projects I always script my datatabase into an SQL file and save it into subversion along with my source code. Any changes can be found in the svn log afterwards, works great.
Wout
|
|
|
|
|
Got a drawer full of them.
Peter Wasser
"The whole problem with the world is that fools and fanatics are always so certain of themselves, and wiser people so full of doubts." - Bertrand Russell
|
|
|
|
|
This can happen for a number of reasons. I have seen this many times. From bad scripts, to some of MS data compare tools, etc.
Hopefully you have the scripts handy, an you can just recreate the missing keys. 
-- rants are the vehicle of the lazy and uninspired - JSOP 2/2018
|
|
|
|
|
Well, I certainly hope you don't have a production application that relies on the existence of them for correct behaviour. 
|
|
|
|
|
Your subject line upset me [until I read the message].
|
|
|
|
|
I had an Enterprise Architect come to me and demand that all the FKs be dropped in the lower environments because it was causing issues. I, politely, suggested that maybe the code was wrong. That did not go over well. So then I told him that I didn't have the authority to do that so he would need to talk to my boss (my boss was a much more devoted FK adherent). They fought, my boss lost. So I dropped them.
Less than a week later the test data was complete trash. At one point Texas was a province in Canada. There were many other examples, but that is the one that continues to stick with me to this day. When I showed them the bad data it was dismissed as just test data and that I shouldn't worry about it. There were other signs that this project was going to be a disaster so I transferred out. About four or five months later they went to production. Within ten days it had to be backed out of production. At this point I had been at this company for seven or eight years. In that entire time I had never heard of project going to production and then being backed out. It was a disaster. The executives had no stomach to spend the money it would take to get it working so they canned the project and let pretty much everyone on the project go. Which is a shame. There were some really good people who tried very hard to make it work, but the bumbling idiot E.A. was just too much to overcome.
|
|
|
|
|
Bloody foreign keys, coming over here, taking indexes away from native keys! Get rid of them all!
veni bibi saltavi
|
|
|
|
|
We won't be able to use them after Brexit anyway.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Bloody foreigners, sending their brightest and best keys over here to...
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
I was brought in to "improve" a system that had horrible performance.
They were not sure why... It was an Industry Product that had been around for decades.
One of their (20+) Goals:
When creating a new XXX, have it take 90 seconds or less to add a new blank row to the grid that the user can then edit. Currently taking 4 minutes on average.
Of course, my first assumption was "What type of grid, and how many rows..."
Then I got access to the DB... They were, in fact, PROUD of not having ANY FK relationships. In fact, they had leaned away from INDEXING as space wasting... Of course, I considered NOT having them as TIME WASTING (per user, for every user)... They had plenty of space to waste...
Upon reviewing the slowest requests, 80% were fixed simply by indexing things properly. They still refused to declare FK relationships (because good data should be avoided at all costs!)...
Oh, and the company had NO IDEA why they were so slow. It literally took someone else (us) to look at their system and ask a few questions... Nobody thought it could be the DB, because it was fast for everyone else... (all much smaller companies).
Ughhh...
|
|
|
|
|
That sounds horribly familiar.
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
[sarcasm on]
Everyone knows foreign keys have been imposed by storage-devices-manufacturers lobbys.
[sarcasm off]
"I'm neither for nor against, on the contrary." John Middle
|
|
|
|
|
Haha.. this is funny - not! I've come across this all the time. The problem "back then" were application designers that had no idea, zilch, nada; about database structures. Yet, here we are in 2018 and I still see the same mistakes. Primary key every table with an auto-fill ID column. Yes, you have a primary key, congratulations! But what's the point if you don't have a reference to the ID from another table? Did someone forget that the ID Primary Key column is a machine necessity, if at all? Like who care my next record starts with ID 2001!? A true Primary Key is one created based on data "snippets" off other columns in the table. Yeah baby... bring back Excel databases!  
Just do what I do when databases have no relations... DROP DATABASE.. oh but don't forget to save your data first. 
|
|
|
|
|
Fandango90 wrote: A true Primary Key is one created based on data "snippets" off other columns in the table.
The "natural vs surrogate primary key" debate is like tabs vs spaces - some people insist that there's only one "correct" way to do, whilst others make a decision on a case-by-case basis. 
SQL Server: Natural Key Verses Surrogate Key — DatabaseJournal.com[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Fandango90 wrote: the same mistakes. Primary key every table with an auto-fill ID column
Sorry, I don't see how this is a mistake.
Fandango90 wrote: Yes, you have a primary key, congratulations! But what's the point if you don't have a reference to the ID from another table?
I think you may have misunderstood the OP's issue. They didn't de-normalize, only removed the FK constraints.
Fandango90 wrote: Did someone forget that the ID Primary Key column is a machine necessity, if at all? Like who care my next record starts with ID 2001!?
I'm not even sure what you mean by 'machine necessity'. Perhaps it's sarcasm and I'm not getting it.
Fandango90 wrote: A true Primary Key is one created based on data "snippets" off other columns in the table
Again, I can't tell if this is sarcasm or a real opinion. So an identity or guid is not a true PK, but a couple of columns where one or more values can change is??? (or, just throw in a timestamp to be sure!) It sure makes future record maintenance a lot easier when I can tag a record with a single condition. Anyway, it would probably make an interesting poll.
"Go forth into the source" - Neal Morse
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
