September 2, 2015 at 7:01 am
Apparently app programmers building a persistance layer think they're real database developers. 😛
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 2, 2015 at 7:26 am
David.Poole (9/2/2015)
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.
Hmm... I guess why I find this topic so interesting is that I keep hearing this "the consequences will dire" statement about not using foreign keys and I just don't see it. Foreign keys protect the integrity of an ERD they do not protect the integrity of data.
September 2, 2015 at 8:05 am
I guess why I find this topic so interesting is that I keep hearing this "the consequences will dire" statement about not using foreign keys and I just don't see it
I guess you've never had to explain why parts of orders are missing and so on. How "dire" the consequences are is perhaps open to interpretation. But for the customer who didn't get an order filled because there were line items but no header the impact is consequential.
This is a topic that speaks to professionalism. We don't expect people who build our homes to take shortcuts. We who build databases shouldn't as well.
September 2, 2015 at 8:18 am
RonKyle (9/2/2015)
I guess why I find this topic so interesting is that I keep hearing this "the consequences will dire" statement about not using foreign keys and I just don't see it
I guess you've never had to explain why parts of orders are missing and so on. How "dire" the consequences are is perhaps open to interpretation. But for the customer who didn't get an order filled because there were line items but no header the impact is consequential.
This is a topic that speaks to professionalism. We don't expect people who build our homes to take shortcuts. We who build databases shouldn't as well.
Okay.... and in that example with foreign key constraints in place the customer still wouldn't get their order because not only would there not be a parent order there wouldn't even be line items which would make trying to track down the issue that much harder.
September 2, 2015 at 8:22 am
Okay.... and in that example with foreign key constraints in place the customer still wouldn't get their order because not only would there not be a parent order there wouldn't even be line items which would make trying to track down the issue that much harder.
I'm not telling you theory. I'm telling you what I have observed first hand. Tables without primary keys usually get duplicates. Relationships not enforced via primary keys end up with orphans. And I'm the guy who has had to clean up the messes for designers who have gone on to repeat the mistakes at other companies.
September 2, 2015 at 8:27 am
I've seen cases when someone was attempt to use the Import Wizzard to load a table and accidentally checked the option 'Delete Existing' instead of 'Append', or where a miscoded delete script or stored procedure would be written something like this:
@CustomerID = 1234;
...
DELETE Customer WHERE @CustomerID = @CustomerID;
A foreign key constraint will save your bacon in this case.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 2, 2015 at 8:32 am
ZZartin (9/2/2015)
David.Poole (9/2/2015)
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.
Hmm... I guess why I find this topic so interesting is that I keep hearing this "the consequences will dire" statement about not using foreign keys and I just don't see it. Foreign keys protect the integrity of an ERD they do not protect the integrity of data.
But those relationships do protect the integrity of the data. You can only add information where the enforced relationship allows you to add information. For example, let's say you set up the classic foreign key relationship to a list of Provinces. The only data that can ever be entered is from the list of Provinces. That's protecting the data, not the ERD. Throw away the foreign key. Now you get to deal with about 600 variations on the string 'KANSAS' because there is nothing to prevent anything at all from going into the database. Multiply that by all the Provinces. Further, since you don't have referential integrity protecting the data, we get to have Provinces from the US included in French data. Counties in the UK tossed in amongst the Canadian Provinces. All this making things worse and worse for the data, not simply for the ERD. To heck with the ERD. Who cares about the ERD. We care about the data. We care about what we can feed back to the business people in a meaningful fashion.
I've seen reporting projects and BI processes abandoned because the data was so horrifically dirty. I've seen quite literally thousands of man hours spent cleaning data because what was in the database was unusable. This isn't about theory. It's about the real world and real work.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 2, 2015 at 11:28 am
Hmm... I guess why I find this topic so interesting is that I keep hearing this "the consequences will dire" statement about not using foreign keys and I just don't see it. Foreign keys protect the integrity of an ERD they do not protect the integrity of data.
I'm not sure exactly what you mean. Do you mean that foreign keys just make sure that a value is one of a proscribed list and don't guarantee that the chosen value is the correct choice from that list?
Larry English used to give the example where call centre staff predominantly selected "Broken Leg" from a list of complaints because they were bonussed on the number of claims entered. Data quality did not benefit them and "Broken Leg" was the first entry on the drop down list so choosing it let them enter data faster.
If that is the type of problem you are describing then that is a business process problem. I'd sooner have such a glaringly obvious data problem than a random data entry problem.
Foreign keys, unique constraints and check constraints complete with a well designed application that works in harmony with the DB certainly increases the probability of good data but I have yet to see the truly idiot proof app. I've seen the harmonious DB/app partnership perhaps twice in my career and that was because the DBA and developers worked exceptionally well together and held each other in high esteem.
September 2, 2015 at 12:27 pm
We all just need to come to terms with the fact that application programmers have a need to locally persist things like shopping carts, messaging, or just generic objects. It doesn't fit well within a relational model, or it just doesn't need a relational model, and that's OK, because what they're doing is not real database design in the first place.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 2, 2015 at 1:18 pm
We all just need to come to terms with the fact that application programmers have a need to locally persist things like shopping carts, messaging, or just generic objects. It doesn't fit well within a relational model, or it just doesn't need a relational model, and that's OK, because what they're doing is not real database design in the first place.
I could come to terms with this, if that's all this were. But I've seen too many cases where the foreign keys could and should have been used. But they weren't, and there are data issues. There are things that don't lend themselves to the relational model, such as messages. And while a shopping cart might not immediately lend itself to the relational model, when the items are bought or returned, they should be able to fit in the model. There may be some good reasons, but most of the time in my view people are just avoiding what they see as unnecessary or tedious work.
September 2, 2015 at 1:32 pm
RonKyle (9/2/2015)
We all just need to come to terms with the fact that application programmers have a need to locally persist things like shopping carts, messaging, or just generic objects. It doesn't fit well within a relational model, or it just doesn't need a relational model, and that's OK, because what they're doing is not real database design in the first place.
I could come to terms with this, if that's all this were. But I've seen too many cases where the foreign keys could and should have been used. But they weren't, and there are data issues. There are things that don't lend themselves to the relational model, such as messages. And while a shopping cart might not immediately lend itself to the relational model, when the items are bought or returned, they should be able to fit in the model. There may be some good reasons, but most of the time in my view people are just avoiding what they see as unnecessary or tedious work.
I was thinking more along the line that inter-process messaging and in progress shopping carts (or just web application session state in general) probably should be persisted in something like MongoDB or XML rather than contained in a RDMS. Now, once the user clicks the submit button on the order entry web page, then the shopping carts gets inserted as a real purcahse order into the RDMS. Until then, it's just digital fluff. Other types of website fluff like click stream data or someone's pet BI project to analyze Twitter feeds should never be persisted to the RDMS... ever.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 2, 2015 at 3:29 pm
Eric M Russell (9/2/2015)
We all just need to come to terms with the fact that application programmers have a need to locally persist things like shopping carts, messaging, or just generic objects. It doesn't fit well within a relational model, or it just doesn't need a relational model, and that's OK, because what they're doing is not real database design in the first place.
Agreed. Let's keep the unstructured data out of structured storage and treat structured storage as if it had, you know, a structure.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 3, 2015 at 1:52 am
ZZartin (9/2/2015)
RonKyle (9/2/2015)
I guess why I find this topic so interesting is that I keep hearing this "the consequences will dire" statement about not using foreign keys and I just don't see it
I guess you've never had to explain why parts of orders are missing and so on. How "dire" the consequences are is perhaps open to interpretation. But for the customer who didn't get an order filled because there were line items but no header the impact is consequential.
This is a topic that speaks to professionalism. We don't expect people who build our homes to take shortcuts. We who build databases shouldn't as well.
Okay.... and in that example with foreign key constraints in place the customer still wouldn't get their order because not only would there not be a parent order there wouldn't even be line items which would make trying to track down the issue that much harder.
Not to sound harsh, but have you ever done any real, complex database development work? It certainly sounds like you haven't.
For instance, typically what might occur is that some peculiar web hiccup allows an invoice to be entered without a customer. Then some developer's assumption is proven wrong and the invoice screen and / or reports all stop working. The company loses money whilst this is fixed. This is a simple and obvious example that could of course be avoided - but there are a few thousand variations on this theme that can, do and will occur without foreign keys.
It's clearly not a universal panacea to stop inconsistent data but it's practically a free check if designed in early, that allows the developers to concentrate on the real issues that will always come up on anything of any significance.
September 3, 2015 at 6:25 am
Not to sound harsh, but have you ever done any real, complex database development work? It certainly sounds like you haven't.
I have done real, complex database development, both in OLTP and OLAP. Can't even imagine where that comment came from, or what it has to do with the topic at hand. As I've said, my reasons for being insistent on this point is that I've had to clean up more than one mess because these basic rules weren't followed. I've seen the results when the rules aren't followed. And generally when I've had a chance to talk with those who would omit them, they don't seem to have a good understanding of database design, or have a mistaken view that performance trumps all, or are just lazy.
September 3, 2015 at 6:59 am
RonKyle (9/3/2015)
Not to sound harsh, but have you ever done any real, complex database development work? It certainly sounds like you haven't.
I have done real, complex database development, both in OLTP and OLAP. Can't even imagine where that comment came from, or what it has to do with the topic at hand. As I've said, my reasons for being insistent on this point is that I've had to clean up more than one mess because these basic rules weren't followed. I've seen the results when the rules aren't followed. And generally when I've had a chance to talk with those who would omit them, they don't seem to have a good understanding of database design, or have a mistaken view that performance trumps all, or are just lazy.
Pretty sure that was directed at the other person questioning you, not you.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply