February 25, 2014 at 9:23 am
Sorry for posting under an assumed name but I have to keep it anonymous due to corporate restrictions and generally to protect the innocent.
I've been a professional developer for about 18 years and though I'm not a DBA I've worked closely with them over the years and have formed what I feel is a pretty decent sense of what are and are not good database practices. I just joined a company where two developers are in charge of the database schema and I found they are very much opposed to the use of foreign key constraints.
Their reasoning as best as I can tell is that
(1) it makes unit testing stored procedures more difficult due to the extra data setup involved and
(2) foreign keys can raise errors as order is important. They would actually prefer orphaned data rather than stoppage of the application.
This seems like bad practice to me but they are unwavering in their position. We've brought up advantages foreign keys provide in terms of data integrity, query performance, generating database diagrams, etc, to no avail.
Am I not seeing something here? Any advice?
February 25, 2014 at 9:28 am
Nothing really to see, they just prefer things to be easy than to be done the right way. If they don't care about how accurate data is then lose the foreign keys and let the inmates run the asylum. The way you're doing it is the way you should be doing things, if you don't have foreign key constraints than many other unintended things can crop up and create a huge cluster ****
+1 for Foreign Keys 🙂
February 25, 2014 at 9:33 am
frankincognito404 (2/25/2014)
two developers are in charge of the database schema
That explains everything 😀
Foreign keys are tools to mantain and ensure data integrity. Not evil but all the other way around.
You could check the following thread very similar to this one: http://www.sqlservercentral.com/Forums/Topic1540801-391-1.aspx
February 25, 2014 at 9:34 am
As always... It depends
If you have an OLTP like a sales web site, RI is critical to avoid loosing customers information.
On the analytical side, is is common to drop RI or load orphan records to a dummy key as it is more important to know that product was sold than who it was sold to.
99.999% of the apps fall somewhere in the middle.
As far as testing, I never let the argument of difficulty interfere with the need to do things correctly. Far too often, developers use good test data and never test anomalies. Yes, it takes more time, but if you are not going to test properly, then why test at all?
February 25, 2014 at 9:52 am
If it's been like this for a while, you should be able to find orphaned data to help make your case. It still probably won't convince them, at which point you need to decide if you need to bring it to someone else's attention.
February 26, 2014 at 10:05 am
stormsentinelcammy (2/25/2014)
Nothing really to see, they just prefer things to be easy than to be done the right way.+1 for Foreign Keys 🙂
+1 Agree 100%
Rob
February 26, 2014 at 11:01 am
In terms of testing, building some setup harnesses for FK data shouldn't be too hard. It's also reuseable work, so that doesn't seem to be a valid argument for anything other than laziness.
However, if orphaned data isn't an issue for the business, then it isn't. If customers can re-enter information, and it doesn't impact business, then it's inefficient, but not wrong.
I'd argue the FKs prevent issues, and I've seen that in most applications, but potentially it isn't an issue, or less of an issue, than the application reporting errors to users. If that's the case, I'd make sure I had queries and checks written to look for orphaned or semi-lost data so I could find it if someone raises an issue.
February 27, 2014 at 6:23 am
Depending on your organizational structure, I would also mention that it really isn't their decision to make; it's more of a business decision.
Does the business know of
1. the existence of orphaned records and how it is affecting the data integrity
2. the potential of orphaned records and how that might affect the data integrity
3. Are the business rules being enforced?
I've seen the argument for not using FK's and enforcing business rules in the application, as well as parent/child relationships. For me, I haven't been convinced that it's better to not use FK's. One of our core responsibilities as a DBA is to protect the integrity of the data. We need to do everything within our power to do so, but due to business requirements do not always "win", but at the very least, the business is aware of the ramifications of not enforcing rules. I'd also document their decision.
Even still, on a number of occasions I've advised the business that something may need to be enforced or tables/schemas should be set up a certain way to prevent future problems or to provide for future features and they said they cannot see the situation where it was necessary. Then a year or two later, they want the recommendation and even though it was documented, "rework" had to be done.
Do you have the support of your manager? What are the database and application standards at your organization? If you don't have any database standards, consider writing some up and presenting them to your manager and the team.
The best you can do is maintain your integrity by making the case to the business and documenting the results.
Best of luck,
Steve
February 27, 2014 at 6:23 am
Just remember that enforcing referential constraints doesn't just ensure good, clean data. You also get performance enhancements when the query optimizer knows that foreign key constraints are in place and enforced. The clean data argument alone should be enough to win this argument, but it doesn't hurt to also know about the performance enhancements.
"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 28, 2014 at 3:41 pm
Unless management "sees the light" and fully backs you in bringing order into chaos you're going to lose. It works and they haven't had problems they're willing to address.
Try for incremental changes such as formal review of new databases/tables that require the DBA team sign off on them. That might be enough of a start to move things along.
If management isn't going to actively and consistently support you then don't give yourself an ulcer. Realize you're going to be living with that environment going forward. Then decide if you want to work there.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply