February 13, 2014 at 7:02 am
I'm going to be the lone guy here and go out on a limb. First, in a new database or even a developing database from a business perspective, yes absolutely essential. Second, from an existing database that is deployed to hundreds of servers and ranges in size from 1000 transactions per month to millions per month, maybe not so essential.
You can go out on a limb, but this scares me. Especially given the importance of the data. Any time I have come across a database without the proper RI constraints, I found issues. The larger the database and longer in use, the more serious the issues. Tables without keys that had duplicates. Orphan records in tables with no FKs. I once designed an access application to which the users had no access to the backend objects. One I was examining it in production use several months later, I found that one of the foreign key relation between job and job details was gone. When I tried to re-established it, I found that someone two sets of job details didn't have a job. This was a small tightly designed application, and without the all the RI constraints, something still went wrong.
I really can't understand why this is a debate, even for data warehouses. I've seen problems caused by mistakes in the ETL. The purpose of the RI constraints is to help you manage the universal constraints. If we could be held to the same professional standards as doctors and lawyers, this in my view would be grounds for a malpractice suit.
February 13, 2014 at 7:21 am
Was this billing application implemented on the payer end, provider end, employer end, third party administrator end, or some other end?
February 13, 2014 at 7:42 am
venoym (2/13/2014)
I'm going to be the lone guy here and go out on a limb...
Don't you worry, you are not alone!
Threre are plenty of cowboy developers around....
😉
Are you working for UK NHS IT or new made US Obamacare?
:hehe:
February 13, 2014 at 7:54 am
Eugene Elutin (2/13/2014)
venoym (2/13/2014)
I'm going to be the lone guy here and go out on a limb...Are you working for UK NHS IT or new made US Obamacare?
:hehe:
.... you got something against NHS IT have you?!
Hahaha, this really made me chuckle but let's not go there. 😛
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 13, 2014 at 8:01 am
A little piling on in this case is good.
I've worked on systems that let the code handle all referential constraints. It failed. Every time. I'm sure it's possible to do, but I've never seen it.
And, OLAP or OLTP, if you're querying SQL Server, then foreign key constraints enhance read performance. Here's a blog post[/url] I wrote on it.
All the standard tropes of database design, normalization, primary keys, stored procedures, foreign keys, clustered indexes, indexes, they all exist for damned good reasons and almost every time I see someone trying to eliminate them because CODE, I know we're going to hit problems down the road.
As far as the system that's already in place and distributed everywhere. You're right, you probably can't implement referential constraints now, but that's not the same thing as saying it's a good idea. It is. It's just a very difficult one in your case.
"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
February 13, 2014 at 8:07 am
A little piling on in this case is good.
Maybe even a lot of piling on...
February 13, 2014 at 8:14 am
Grant, I agree regarding performance--no doubt about it.
In my case, in the two cases where the DWH fact table had no FKs, the fact tables existed for the sole purpose of being a storage area for the processing of SSAS cubes-no end user query services were available in the RDBMS. SSAS was in MOLAP storage and was the primary and only engine acting as the data presentation layer.
There was also a rather mature MDM program that addressed any RI issues earlier in the ETL process. This is very rare, of course.
February 13, 2014 at 8:32 am
I'd like to add my nickle's worth from my perspective as a developer.
FKs make my job so much easier. I don't have to write the code that prevents orphaned records. That's such a no-brainer to me.
I am a lazy developer. I want the database to handle as much of the load as possible. I will concentrate on the UI.
Tom
It's cool being lazy. 😎
Jeff Atwood has a writeup about this: http://www.codinghorror.com/blog/2005/08/how-to-be-lazy-dumb-and-successful.html"> http://www.codinghorror.com/blog/2005/08/how-to-be-lazy-dumb-and-successful.html
February 13, 2014 at 8:46 am
OCTom (2/13/2014)
I'd like to add my nickle's worth from my perspective as a developer.FKs make my job so much easier. I don't have to write the code that prevents orphaned records. That's such a no-brainer to me.
I am a lazy developer. I want the database to handle as much of the load as possible. I will concentrate on the UI.
Tom
It's cool being lazy. 😎
Jeff Atwood has a writeup about this: http://www.codinghorror.com/blog/2005/08/how-to-be-lazy-dumb-and-successful.html"> http://www.codinghorror.com/blog/2005/08/how-to-be-lazy-dumb-and-successful.html
Looking for work?
"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
February 13, 2014 at 8:56 am
Eugene Elutin (2/13/2014)
venoym (2/13/2014)
I'm going to be the lone guy here and go out on a limb...Don't you worry, you are not alone!
Threre are plenty of cowboy developers around....
😉
Are you working for UK NHS IT or new made US Obamacare?
:hehe:
Must be for Obamacare 😛
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 13, 2014 at 2:41 pm
venoym (2/13/2014)
The developers built into the logic of both the application and the business logic in the DB ways to find almost every orphan and resolve it by the user. Keep in mind that most healthcare billing applications are not OLAP or OLTP, they are a combination of both in order to give the proper information with speed to the small number of users
Ok - from this I get, DEC, Sybase and Microsoft spent hundreds of millions on database systems that *Easily And Out Of The Box* do this. And flawlessly. Doing it this way, what we in the know like to call "PROPERLY" means, not only do you have to write code to implement, at great cost, code to try and do the same thing, as you get FREE - OUT OF THE BOX - but buggier, and putting patients at risk due to crap data getting into the system. Trust me, I have and currently do work with healthcare systems.
But, SOMEONE, knows better. Why? Because they do not understand relational theory or how databases work. Or how cost based optimisers work. Or the problems caused by polluting data.
So they work on the basis of the Power Of Ignorance. Sometimes compromises do need to be made. Sometimes arrogant people are just incompetent and too lazy to learn anything out of their comfort zone.
<Gothard's Law #666>
You, or your team of 5, are NOT smarter than the crew that put the relational engine for SQL Server together. If you are too witless to understand this, someone should take your keyboard away and set you up for a job where the core skills are asking "Do You Want Fries With That". Someone else should work the till
</Gothard's Law #666>
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
February 13, 2014 at 3:28 pm
venoym (2/13/2014)
I'm going to be the lone guy here and go out on a limb. First, in a new database or even a developing database from a business perspective, yes absolutely essential. Second, from an existing database that is deployed to hundreds of servers and ranges in size from 1000 transactions per month to millions per month, maybe not so essential.Here's my reasoning: I worked on a healthcare billing application that was deployed to hundreds of individual clients that was developed entirely without foreign keys or check constraints. The developers built into the logic of both the application and the business logic in the DB ways to find almost every orphan and resolve it by the user. Keep in mind that most healthcare billing applications are not OLAP or OLTP, they are a combination of both in order to give the proper information with speed to the small number of users. So each user can handle about 100x the number of patients than a traditional (green screen is considered traditional) or paper based systems. Retrofitting that database to fully support FKs without big issues with the litterally hundreds of backend processes breaking would take several years work to do.
Like I said before, if you can do FKs, do them. New development, Do them. If you are given a highly complex system involving 700 tables of mixed OLAP and OLTP and more than 5000 stored procedures that is deployed to hundreds of client servers and has hundreds of background processes that both manipulate the data (through stored procedures largely)... you might want to take a much more measured approach as any change will result in hundreds of calls to your support center (just moving a delete button from the bottom right of the application grid to the bottom left resulted in over 100 calls complaining that we removed the delete function... we had to do special training and add KB articles, and a ton of other things in order to simply move a button to the left).
I've been in almost exactly this scenario. I fought for years to fix the issue and when we were first denied to fix, code the orphaning detection code. Never mind that the cost to code, find and fix the data issues was several orders of magnitude higher than it was to do the fix when we finally were permitted to do so. And yes you're right - there was a fair amount of grousing and a few years of development when it was implemented; the complaining continued all of the way until the CFO figured out that he no longer had 80M each year in claims that wouldn't bill because of crap data. The fix paid for itself in 6 months.
I'm hoping you're blessed with a case where your app isn't as poisonous as the setup I was dealing with, but I'd imagine there's a lot more fixing going on than anyone would care to admit.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 13, 2014 at 4:02 pm
I'm going to fire a bombshell that will probably get me shot, but here it is:
I rarely use FKs.
Not because I trust the application, I don't. I could throw the server room further than I trust any front end developers to isolate bad data.
If my users can enter bad data directly into a table, that's a whole different ball of issues.
Not because I like crappy data, I don't like that, either.
I don't use them because it's rare that you actually have to. However, when I need to, they're irreplaceable.
Most FK structures run off the surrogate keys. I know the argument for business keys but they're rare in *my* real world and not always valid. Because of this, and because of typical data persistence techniques, records are rarely, if ever, deleted. They're usually deactivated with a flag. If they were actually deleted, this usually causes a rollback in 99% of production systems I've worked on.
In general, other than enforcing cascading updates when using a business key that an end-user has the rights to modify, FK's are typically only required when the end user can type in the key that has to connect to another record. This type of validation SHOULD be in a combo box, but for some reason isn't. Well, alright, fine.
Performance gain you say? Most lookup tables (where you'd FK 95% of your system) are clustered indexed on the connector anyway. The other 5%? Sure, FK it.
Why am I reluctant? Two reasons:
When I take these systems down to dev and want to either truncate things to replace with testing data or some other concern they get in my way.
I'm also a proponent of don't over-code. If you're getting bad data in a field from the front end, clean it up and deploy a FK to keep it from recurring in that field. If your testing your optimizations and you think the FK can help, give it a try, see if your execution plans are cleaner.
FK's are FK's. They're not something you code around. They're an integrity check that has no substitue. If you don't think you can trust your front end, don't. Protect yourself and code to conditions. If there's a reasonable expectation (your users CAN'T access the table, for example) use them where it makes sense to enforce it because of possible user error.
As to warehouses, a lot of times it isn't used because of truncate/reload items for dimensions instead of doing a delta compare for smaller tables. Nothing more extravagant than that, at least in my experience.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 13, 2014 at 4:17 pm
Evil Kraig F (2/13/2014)
I'm going to fire a bombshell that will probably get me shot, but here it is:I rarely use FKs.
.
So you don't want free data integrity, and you don't want free performance improvement. You don't want free documentation if someone has to try and work out how your schema's put together if you get hit by a bus? I'll give due - I've seen you do fantastic work helping people on here. That just makes me all the more baffled by this. I'm not Relational Taliban, but that just escapes me. It's not dBase.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
February 13, 2014 at 5:45 pm
I rarely use FKs.
That's too bad. But as nothing anyone will say will change your mind, all I'll add is that one day you, or someone else, maybe me, will have to clean up the mess that the lack of FKs caused. Others have said the same as you, and I cleaned up their messes, sometimes years after they had moved on to create more messes somewhere else for someone else to clean up. Occasionally the mess was beyond repair.
Why am I reluctant? Two reasons:
When I take these systems down to dev and want to either truncate things to replace with testing data or some other concern they get in my way.
Then remove them in DEV. But not having them in PRODUCTION because you want your life to be easier in DEVELOPMENT is more than laziness and lunacy.
Hopefully the people that render services important to you don't have such misplaced concerns.
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply