November 13, 2007 at 11:14 pm
Hi Guys,
I have come across a DB where there are no Foreign key relationship's ...
Is this a wise thing to do? ... Since the data is being inserted through a ASP.Net application and it makes sure to the relationships and data integrity .... but then is the overhead involved in this approach more ?
Can there be a scenario where Foreign keys are an overhead to maintain and best be avoided ?
Do Foreign keys help query processing at all or are they just constraints to maintian referential integrity ??
Please give me pointers to solve these question.. Thanks .....
November 14, 2007 at 5:37 am
Foreign keys do not directly help performance. They are simply constraints on the data.
What you're saying is, that the developers are not like the rest of us humans. They make zero mistakes. So they're never going to cause any kind of data integrity issues, inserting the wrong values between tables, deleting parents without deleting children, or any other horrific relational nightmare that a constraint would prevent?
You want data integrity in order to protect the data, not to enhance performance. In fact, you'll probably need to create indexes on the same columns that have foreign keys in order to enhance performance. However, that doesn't mean you remove foreign keys from the system.
Every system I've ever seen that was designed in this manner had very serious and long term data integrity issues.
"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
November 14, 2007 at 5:53 am
In case the integrity is being maintained somehow. Should i still go ahead add the foreign key relationships or is it unecessary overhead?
November 14, 2007 at 5:59 am
It's the old argument regarding where business logic should be managed and what is business logic. Arguably, foreign keys are business logic, so some developers want to put this type of logic in their application.
I tend to think that is not the way to go and try to put anything that could be considered data integrity at the lowest possible level in the application (usually ending up as constraints of some sort in the database).
This is always at the cost of some performance at the database level (sorry to say adding a foreign key makes your database slower for inserts, updates, and deletes). However, this is typically the fastest place to do the check, so if the same check would need to be in an application layer or a stored procedure, a constraint of some sort (at least a well-built one) will usually be faster. So, if you have to do it, why not do it in the fastest way possible?
Finally, there is the argument that if the logic is in the application and the data is never to be accessed with anything but the application, you are perfectly safe. If anyone out there has seen an application using an MSSQL database in which the database has never been accessed using another tool, please let me know.
November 14, 2007 at 6:48 am
Personally, I'd say yes, add the constraints.
Everything Michael Earl said is true. You can simply rely on the fact that the application has the integrity covered. However, as a DBA, my first responsibility is to protect the data. That means good backups and maintenance and security, but it also means good integrity. In most, but not all, situations, the overhead for maintaining integrity is extremely low. The benefits, if integrity is always good, are invisible. You'll only realize how important having the constraints in place are when they're not there and you're suddenly faced with large scale data cleansing projects.
Seriously though, there isn't a single constraint that can be placed on the data within the database that could not also be done in code. So why not toss non-null columns and data types and primary keys along with foreign key constraints? All these types of constraints done within the database are merely safety features like the airbags in your car, protecting the data like the airbag protects you. I've never caused activation of the airbags in my car. I'm not going to remove them because I've never used them.
"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
November 14, 2007 at 8:12 am
I think foreign keys are crucial, but I don't think you notice what they do till they're not there. I've seen many third-party databases that don't have them, however, and that has always concerned me.
Even from the development perspective of having key code in one place that is then referenced from another, the foreign key would seem to do that ideally. If the business process ever necessitated a change for the key (I know, in real life business practices don't change, but let's suppose) you could change it at the database level w/o having to go to multiple locations to change the code.
For me, however, the bottom line will always be the inevitable data inconsistencies that I've seen first hand when tables don't have primary keys and foreign keys. The time needed to identify and resolve these issues can be significant and even prohibitive.
In one important way, foreign keys are query enhancements: in a database w/o them, a properly written SELECT statement would have to account for the possibility of orphaned records, which would surely be a performance hit. This doesn't not need be done against tables that are properly keyed. In my experience most requests against a database are SELECT queries. I would suggest that the performance gain in this area outweigh the performance "loss" in the INSERT/UPDATE areas.
November 14, 2007 at 8:33 am
there are no good and bad or black or white in SQL, only gray areas and what is good for your situation
we have very few FK's where i work as well because the devs like the application this way. a lot of our business is sales people making up manual orders and they rarely get it exactly right. and the price is that we have some people spend a fair amount of time fixing data mistakes in the database that could have been prevented with constraints. but then again we would probably have the same thing since no one would get it right and put in trouble tickets that their data input is not working
November 14, 2007 at 8:49 am
SQL Noob (11/14/2007)
there are no good and bad or black or white in SQL, only gray areas and what is good for your situationwe have very few FK's where i work as well because the devs like the application this way. a lot of our business is sales people making up manual orders and they rarely get it exactly right. and the price is that we have some people spend a fair amount of time fixing data mistakes in the database that could have been prevented with constraints. but then again we would probably have the same thing since no one would get it right and put in trouble tickets that their data input is not working
You mean the literal business model is that you have time to fix stuff, but not time to get things right... Yikes. :blink:
Usually, especially when dealing with data that equates to some monetary value, you reverse that formula.
"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
November 14, 2007 at 9:23 am
SQL Noob (11/14/2007)
there are no good and bad or black or white in SQL, only gray areas and what is good for your situationwe have very few FK's where i work as well because the devs like the application this way. a lot of our business is sales people making up manual orders and they rarely get it exactly right. and the price is that we have some people spend a fair amount of time fixing data mistakes in the database that could have been prevented with constraints. but then again we would probably have the same thing since no one would get it right and put in trouble tickets that their data input is not working
I'm still skeptical. I've seen bad data entered that simply can't be fixed. Data entry forms can go a long way towards rectifying the information. If someone enters a zip code that doesn't exist, how would that be fixed. If the city only has one zip code, someone can figure that out, but what if it's New York? What if someone misspells a company name and there are two with similar spellings? I concede that same data entry problems can't normally be fixed by constraints--entering a bad street address for example, but for most having foreign key contraints means knowing that a state code on an INSERT is really a valid statecode, or that work done has a valid work order.
I have seen where
what is good for your situation
was a lack of knowledge or desire to get things right and only created problems down the line that would not have existed in the first place with proper constraints. It MAY be true that there are no black and whites, but that doesn't mean that everythings grey, it just means that sometimes you have whites with just a tinge of black and blacks with just of tinge of white. I would never, for example, have a table without a primary key no matter how much the developers would want one. Identity columns, if nothing else, are just too easy.
November 14, 2007 at 9:49 am
zip codes are one thing, but a lot of times you have varchar data where a sales person fills it out and just puts in the wrong data for whatever reason and you only find out about it when some process fails
you can set up a PK/FK relationship for this, but then you will have the same amount of people calling because the system is telling them they can't enter whatever gibberish they are trying to enter
November 14, 2007 at 10:00 am
SQL Noob (11/14/2007)
zip codes are one thing, but a lot of times you have varchar data where a sales person fills it out and just puts in the wrong data for whatever reason and you only find out about it when some process failsyou can set up a PK/FK relationship for this, but then you will have the same amount of people calling because the system is telling them they can't enter whatever gibberish they are trying to enter
But doesn't that then become a training and documentation issue? Surely less costly than the bad data and the bad decisions or bad billing or bad contracts caused by it in the long run?
"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
November 14, 2007 at 10:47 am
only in an ideal world
in the real world CSR reps and sales people put down whatever the slang name is for something or they put in trouble tickets that something is broken
these things start off as simple MS Access apps and grow into 120GB databases with over 300 tables that are used by so many applications that no one can track them all down because your company goes from selling stuff to mom and pops with uniform prices to fortune 500 companies where you have unique deals for every sale and you bought a few competitors with totally different db schemas and you had to migrate all the data into your database
November 14, 2007 at 2:05 pm
I have seen cases in which critical performance is required and Foreign keys are in the way. But those were extreme cases.
Cheers,
* Noel
November 14, 2007 at 2:44 pm
The only time you should have a database without foreign keys is when there are no logical relationships between the entities that your data represents.
In other words, never.
November 14, 2007 at 2:58 pm
SQL Noob (11/14/2007)
only in an ideal worldin the real world CSR reps and sales people put down whatever the slang name is for something or they put in trouble tickets that something is broken
these things start off as simple MS Access apps and grow into 120GB databases with over 300 tables that are used by so many applications that no one can track them all down because your company goes from selling stuff to mom and pops with uniform prices to fortune 500 companies where you have unique deals for every sale and you bought a few competitors with totally different db schemas and you had to migrate all the data into your database
You've just very effectively argued FOR the foreign constraints being in the data layer (the UI's are all different and none has the time to make sure that any consistency is maintained). The amount of "soft" time loss your organization must go through to handle said garbage in its corporate data must be staggering. I'm sorry you have to be saddled with that (having been in your shoes many years ago).
That being said - I'd document the amount of time, aggravation, revenue loss due to bad data, etc... Crappy data benefits NOONE: not the customer (they don't get what they want or they get it delayed due to data cleanup), not the CSR (they'll lose revenue based on the customer experience), not Billing or shipping or the company (errors cost money - ALWAYS).
The only real reason it's being put up with in most cases is that those who need to PAY for these soft cost don't know how to quantify it. Give them a hand - you might actually get some things built better and/or fixed that way.
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 1 through 15 (of 87 total)
You must be logged in to reply to this topic. Login to reply