Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Voluntary Constraint? Expand / Collapse
Author
Message
Posted Saturday, December 31, 2011 11:53 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:01 AM
Points: 594, Visits: 2,580
Comments posted to this topic are about the item Voluntary Constraint?


Best wishes,

Phil Factor
Simple Talk
Post #1228688
Posted Saturday, December 31, 2011 7:01 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:16 AM
Points: 833, Visits: 1,600
Thank you, thank you, thank you, Phil.

The question isn't so much about whether constraints are a good idea, it's more about understanding the psychology of developers who don't want to know that they're a good idea. It seems to me, that in the sphere of application development, it is too easy for bad database design to be implemented. But why?

I suspect I'm not the only one who has to deal with software developed by (usually highly intelligent) people who are NOT developers by trade, but who:
are very good at what they do in their particular profession of choice
enjoy dabbling in software development as a kind of hobby
can see where some quick wins could be gained by a "simple" bit of software
because they are intelligent and good at what they're trained to do, develop an arrogance that there's nothing they could possibly learn from database designers. "All that guff will just slow us down and cost a fortune" ...which is true in a very narrow sense.

What happens is that hobby software ends up in production, and is on the face of it, much better than what was there before (paper processes for example). Once it's in production though, removing it eventually becomes akin to cutting out a cancer. Things that worked OK for a few thousand rows eventually start to disintegrate, but only after hundreds or thousands of business dependencies have been built up around them.

I deal with databases from around 20 different software vendors. The seven most problematic (i.e. over 30%) followed the above path on the way to becoming commercial software. The sheer audacity and arrogance of some hobby developers ("I know the business, therefore I know every aspect of the software requirements") is a large part of their commercial success. As sales people selling to people in their own professions, their cases can be compelling. I honestly don't see a cure.

I was sitting in a meeting with a vendor once and we were attempting to discuss the manifold data integrity problems we were having because of the non-existence of constraints. The vendor simply closed the discussion down by saying "It's not the software's fault that the data entry staff are idiots." And that was that.




One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1228711
Posted Sunday, January 1, 2012 5:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:54 PM
Points: 26, Visits: 109
Also well said Old Hand.

One of the conundrums I often observe in the hobbyist intellectual is that even after the first sign of dark clouds on the horizon they don't seem inclined to join the dots! I think in many cases the ego that created the success (often by force of effort) is not receptive to a retrospective analysis.

I guess in many cases where success in the venture occurs, that a cohort of underlings has to be engaged to scale up the solution - and it is these unfortunates who have to wrestle with the monster that has been created. In many cases this backfill cohort is younger (cheaper), impressionable, dis empowered and not at all necessarily talented - all of which leads to a perpetuation of the original "dodgy" practices under the "ain't broke" mantra.

In a personal experience where I have sneaked a peek under the hood of a second time success story (the first having been very successful as well) I was dismayed to see an appalling database implementation - ably masked by a glamorous user interface - but in reality simply a brute force effort built on foundations of pure sand.
Post #1228765
Posted Sunday, January 1, 2012 11:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 1:46 AM
Points: 28, Visits: 59
I certainly am not arguing with Phil, or the two posters. Maybe I'm just frustrated with some deadlock investigations where neither transaction was doing anything wrong, and if both had asked a few milliseconds apart, both would have succeeded, but instead one of them died in deadlock. Then tracing through the constraints, trying to figure out exactly how it happened and coming to the conclusion there isn't any way to stop it from happening.

Then sometimes, you can solve the problem by setting up locking hints to cause locking in the right order. Just, why do you have to jump through all these hoops, to solve a problem produced by SQL logic?
Post #1228775
Posted Monday, January 2, 2012 2:33 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:01 AM
Points: 594, Visits: 2,580
@Ken Lee
Put your question to ASK.sqlServerCentral.com, or one of the forums, and see if one of the experts can help with this problem. I agree that, when faced with a frustration like this, one's instinct is to strip off as much complexity as possible to get to the root of the problem, but I've never known constraints to be the guilty party of a deadlock problem, but I'd rather one of the specialists dived in to help you. See Brad's 'How to track down Deadlocks using SQL Server POrofiler' Also check out Jonathan Kehayias and Ted Krueger's book Troubleshooting SQL Server: A Guide for the Accidental DBA



Best wishes,

Phil Factor
Simple Talk
Post #1228811
Posted Monday, January 2, 2012 3:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 35,772, Visits: 32,443
The article sounded a bit like a rant...




... I only wish I'd thought of it first!

Well said, Phil.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1228994
Posted Monday, January 2, 2012 4:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:37 PM
Points: 2, Visits: 88
Agreed. Only recently have I felt strongly about designing in constraints. I now see an inverse relationship between the number of constraints versus post implementation support issues.
Post #1229007
Posted Tuesday, January 3, 2012 4:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:54 PM
Points: 26, Visits: 109
Ken Lee-263418 (1/1/2012)
... Maybe I'm just frustrated with some deadlock investigations where neither transaction was doing anything wrong, and if both had asked a few milliseconds apart, both would have succeeded, but instead one of them died in deadlock. Then tracing through the constraints, trying to figure out exactly how it happened and coming to the conclusion there isn't any way to stop it from happening.

Then sometimes, you can solve the problem by setting up locking hints to cause locking in the right order. Just, why do you have to jump through all these hoops, to solve a problem produced by SQL logic?

I have been here too (with SQL Server as it happens) and the reason is not IMO (1) SQL logic or (2) embedded constraints. In my case it was perverse behaviour of the implementation - in particular the query optimiser recycling query plans in a case where 2 instances of the query with different sargs had run at the same time (previously) and produced inverse (of each other) lock escalation strategies ... then voila a deadlock when reused at a later date!

What made it even more perverse was "in testing" (ie. without 20K sessions seen in production) the 2nd "bad" query plan never got created (and therefore not left in cache) so showplan just played out sweetness to the unskilled reader. Unfortunately the vendor was not much interested and there was not much room to move on a 2 table join.
Post #1229153
Posted Tuesday, January 3, 2012 5:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 28, 2014 11:38 AM
Points: 45, Visits: 77
If a developer argues that these constraints are not necessary because the code manages the integrity, it can be argued that after implementing these constraints in the database there will never be a violation of the database constraint. So what's the problem with having them in the database. Furthermore, can it be guaranteed that all access to the database will be through the application? Perhaps. It can be guaranteed that all access to the database will be through the database.

On the topic of purchasing products with their proprietary databases, perhaps if enough data architects were involved in the purchase of these products and if enough of us said no to those which do not adhere to these constraints, the software vendors would have to build them in or not sell their products. Those are a couple of pretty hefty ifs, but worth pursuing.
Post #1229160
Posted Tuesday, January 3, 2012 12:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
And then there's me, who's a DB Developer and not a front-end guy, and I build the back end architecture.

Why don't I usually use constraints except in "absolute, must have" scenarios?

Because I'm a lazy git and I don't want to do a schema deployment when you change your mind in a week. Besides, this way the Constraint illustrates the absolute necessity of that data in that particular system, rather than being littered with them everytime you need to make a length change.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1229512
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse