Here's a link to an article about keys in SQL. Make sure you read the comments. I wonder where most people fall on this subject and the split (if any) between developers and DBA’s (and people have to fill both roles)?
Personally, I don’t give a crap about whether identity keys are “exposed physical locators”. How about GUIDs or Stored Procedures which generate an incrementing number instead? I’ll be honest, I’ve never used them to identify the physical location. It’s just that I’ve found that “natural keys aren’t” (I think I first heard that from Jim Booth http://www.jamesbooth.com ) and having a system generated key greatly simplifies any code I’ve needed to write against a SQL backend (VFP or SQL Server). I’ve worked with systems that exclusively used natural keys and invariably what seemed like a reasonable natural key suddenly wasn’t any more. This usually meant we now had to use multiple columns to uniquely identify the record (this usually ends up happening more than once on the same table). No thanks. I’m sure some people would argue that changes like this just means we didn’t do a proper design. Yeah, that could be the case. But I’d much rather use a style of development that was more flexible to change and more forgiving of developer errors than one that leads to nightmarish spaghetti code just because someone didn’t take something into account during whatever design process there was. The other way may lead to the most elegant, clear code ever written, but if it’s dependent on people never making any kinds of mistakes (or incorrect judgment calls), I’ll skip it, thanks.
If you’re interested, Joe has a blog where he answers questions about SQL in his own, abrasive, insulting (if sometimes funny) style.
http://joecelkothesqlapprentice.blogspot.com/
How many of his “rules” have you broken to actually deliver usable software (within the confines of the tools you’re using)? Why is there so much support built into these tools to do things the “wrong way”? Right now, I’m specifically thinking about Crystal Reports: where are you doing most of your data manipulation – in SQL Server or in CR? I guess it could be argued that the limitations of these various development tools doesn’t mean that the various practices they (sometimes) force on you are correct. Could be, but ultimately most of us Just-Need-to-Get-it-Done. “In theory” is nice, but “in practice” pays the bills. Do you think your customers would accept the excuse that you wouldn’t generate some report for them because the reporting tool doesn’t have the ability to do what they wanted and putting it into SQL isn’t the correct way to do it?
As a side note, it seems like this is a distinct personality trait with some “computer” people in general. You know the type: really smart, could be really helpful to have on the team but they just don’t play well with others. Fanboys (and girls) of them will usually say things like, “I know online he can be rough, but it personal he’s great”. In other words, that just means he’s an asshole. Being blunt (as it’s sometimes described) is OK as long as it’s tempered with the understanding that it’s not an excuse to be an asshat, online or in person.
(I just hope the above isn’t taken as some argument for writing crappy code). Write the best code you can for the given problem and time constraints. Refactor mercilessly.
Links
http://www.intelligententerprise.com/channels/bi/showArticle.jhtml?articleID=201806814
http://joecelkothesqlapprentice.blogspot.com/
http://www.jamesbooth.com