Ruminations on Software

Why yes, I do a bit of Modeling...

2007-08-19T15:24:00.001-07:00

One of my favorite sayings from Ben Franklin is "Experience is a dear teacher, but fools will learn at no other." True enough, but why, with the cornucopia of programming books, how-to articles, and blogs out there is experience still such the dear teacher?

Mistakes. Books, blogs, and best practices might tell us how to perform a task, but sadly, little but experience shows us why we shouldn't do something else. Someone recently asked me to review a database design, and I responded to this person with an admonition to "don't do that" and a friendly scowl. Of course, the response was, "Why not?". Here's the answer.

The Temptation of Surrogate Keys

If you do enough database programming, you'll probably see that developers like IDENTITY (AutoNumber, for you Access people) columns as table primary keys. A lot. In database language, this is referred to as a surrogate key. Why? Because they seem to simplify database design and they seem to make it easy to query for single values on a table. But if you aren't careful, you'll get into a heap of trouble, and it'll be hell to dig yourself out.

Surrogate Key Fields on Many-to-Many Relationship Tables

Let's say we're designing a budgeting system for a company. Remember that this is all for the sake of argument, I'm not trying to show you how to design a budgeting system. My apologies to all you CPAs out there...

There are three entities in the system, and a couple of simple business rules:

Our Entities:

Account: If you don't know what an account is, I suggest reading someone else's blog.
Department: A grouping of people within a company, such as "Accounts Receivable", "Software Development", "HR"...
Budget: A grouping of the amounts assigned to each account in each department for a period of time (say, a fiscal year).

Our Rules, for the sake of argument:

Only certain Accounts can be used in certain Departments. For example, "HR" isn't allowed to budget for "Sales Travel", but are allowed to budget for things like "Staffing Advertisements".
Only certain Accounts are valid for certain Budgets. Maybe certain accounts are retired as the company restructures its budgeting process, and others are introduced.

Let's Start Designing (Badly)!

So let's start designing! For this first round, we'll design the system such that every table in the database has a surrogate key. To keep the diagrams simple, we'll leave off the attributes of each entity and just show the keys. First off, let's implement Business Rule #1 with our Account and Department entities. We'll do this by using a relationship table called AllowableAccounts and two foreign keys:

Now for Business Rule #2. We'll implement this one the same way:

Now, for the final relationship -- the "Line Item" entry portion. This is where we enter in the amount budgeted for each account, for each budget. You get a gift if you can already see where I'm going with this:

There are a couple of things to note here. There is a foreign key relationship between LineItem and the DepartmentAccount and BudgetableAccount tables. Notice that there are no references to BudgetID or AccountID in the LineItem table. This design seems to implement our business rules. Unfortunately, there's a fatal flaw.

There's no Relationship Between Budget, Account, and Department

Here's the problem: LineItem does not enforce the rule that the AccountID referred to in BudgetableAccount is the same as the AccountID referred to for a given DepartmentAccount. It's therefore possible to run these queries against the database and return different results:

SELECT AccountID c
FROM LineItem li
INNER JOIN BudgetableAccount bi
ON bi.BudgetAccountID = li.BudgetAccountID
WHERE li.LineItemID = 5;

and:

SELECT AccountID c
FROM LineItem li
INNER JOIN DepartmentAccount di
ON di.DepartmentAccountID = li.DepartmentAccountID
WHERE li.LineItemID = 5;

This is possible because you don't have direct relationship between Budget, Account, and Department. What would happen if the data were up-to-date and someone were to update the DepartmentAccount or BudgetAccount tables to a different AccountID? What if your business logic did not check this constraint before inserting?

There are Other Reasons, too

First off, consider how you're going to be using the database. Will you ever be querying off of DepartmentAccountID? BudgetAccountID? These fields have no business meaning, so you'll probably have to use a join in order to look up these columns. This will probably mean when asking questions like, "What's the total amount budgeted for Budget X?" -- That requires a join across 2 tables -- and all for such a basic question. Yuck.

A Better Design

Instead of the previous design, let's eliminate the surrogate keys on the BudgetableAccount and DepartmentAccount tables, and create a relationship table for our LineItems based solely on the Budget, Department, and Account IDs. Note that this design requires the use of 2-way composite foreign keys -- you can't just use one column to create your relationship, you must create a foreign key using both the Department/Account and Budget/Account key combinations. This is the key point that most beginning database programmers miss. These special foreign key relationships are shown in blue:

Conclusion

Watch out for surrogate keys in relationship tables. Avoid them when at all possible entirely -- they're meaningless from the context of your business. Some object-relational frameworks force you to have a surrogate key on every table. Perhaps you work in an environment where your DBA's or your "senior developers" mandate this practice, and you can't stop the madness. Fine. Just do yourself a favor -- don't define relationships based on a surrogate key in your relationship tables. It's a recipe for data integrity nightmares.

Boycott TestDriven.NET!

2007-06-01T17:47:00.001-07:00

The Flap

I must say, there are very few times when something on the web makes me so angry I lose sleep over it, but Jamie Cansdale has finally found a way. I'll assume that you've read all about the flap between Jamie and Microsoft regarding Visual Studio.NET Express and the TestDriven.NET add-in. Unfortunately, most of the buzz on the web is pretty "pro-Jamie" and "anti-MS". Of course. I expect that. The "borg" icon of Bill Gates on Slashdot, Product Activation, the software that costs more than $0 to install. Microsoft must be the bad guy here...

Jamie Quells Dissent on his Blog

You probably won't hear me paraphrase Chuck D much on this blog, but don't believe the hype. Why not? Well, I as well as others (see Eric's post) have tried to write entries in his blog telling him that he's in the wrong on this issue. Somehow our posts never made it to his blog! Surprise! I wonder if it's a bug in his blogging engine. Or perhaps Jamie doesn't want to have a backlash against him start on his own weblog.

Update: Jamie responds in the comments below. All comments should be visible.

TestDriven.NET is not Free Software

If you read most of the responses to this story in Slashdot, or (believe it or not) Jamie's blog, you'll probably be led to believe that TestDriven.NET is free, open-source software, and that Jamie Cansdale is a lone developer, toiling away for the sake of the community, and being bullied by the 800 pound Gorilla that is Microsoft. Nothing could be further from the truth. TestDriven.NET costs$95 per user. Want an enterprise license? That'll be $10,500, please. Some have made the patently ludicrous claim that Jamie does give the software away for free. Not if you're using it professionally, he doesn't! His licensing terms are... wait... that's the next section! Stay with me here, this is the best part!

Jamie's License is Just as Bad

It's time to play a little game, boys and girls. The game is, "let's guess whose license agreement this is":

...you may use the software only as expressly permitted int his agreement. In doing so you must comply with any technical limitations in the software that only allow you to use it in certain ways... You may not work around any technical limitations in the software

versus:

...Licensee shall not, and shall not permit others to ... use the Software in any manner not expressly authorised by this Agreement.

Sound pretty familiar, don't they. The same clause that Jamie complains is vague, irrelevant, and unenforceable in the MS license is pretty much exactly the same in the TestDriven.NET license. Jamie Cansdale is a hypocrite, and he deserves to be exposed as such, and punished.

TestDriven.NET's Real Functionality comes from Software that is Free

For those of you unfamiliar with the product, TestDriven.Net is basically a plug-in for Visual Studio that allows developers to run NUnit, MBUnit, and NCover tests with a click of the mouse, or a quick keystroke. It performs a few other functions, but that's about it. These products are all open-source and free. Others have labored long and hard to generate these tools, and Jamie ships them with his non-free product. All of the heavy lifting is done by these utilities -- TestDriven.NET is a convenience product that makes using them easier.

Boycott TestDriven.NET

It all adds up to one thing -- Jamie Cansdale is using the publicity he's generated to line his pockets. If you are a developer of NUnit, NCover, or any other product that he exploits, I suggest that you either demand your fair share of his revenues or withdraw your permission for him to distribute your work with TestDriven.NET. Ever noticed that his site never got "slashdotted" in all of this brouhaha? He's ready for your orders, folks! Step right up! Gee, I wonder how many copies he's sold already. How about a disclosure there? Somehow I doubt we'll ever see it.

My Prediction

Jamie is going to wait this out for a bit while the orders come rushing in due to this being the "hot topic" in the developer community. Once he's satisfied that he's done that, he'll withdraw his plugin for Express, claiming that he's finally been beaten by the big, bad monster of Microsoft. Microsoft will be happy that this PR nightmare is over, and will let Jamie off the hook. And you-know-who laughs all of the way to the bank.

ORDER BY in Views: A SQL Server 2005 Gotcha!

2007-01-01T17:03:00.000-08:00

Hugo Kornelis just brought up a very interesting point about SQL Server 2005, and its behavior when using views with ORDER BY clauses in them.

You may be aware that SQL Server 2000 (and previous versions) allowed you to essentially create an ordered view -- something that's not really supposed to be allowed -- simply by using TOP 100 PERCENT:

CREATE VIEW myView AS
SELECT TOP 100 PERCENT LastName
FROM dbo.Stones
ORDER BY LastName;

In fact, this is exactly what Enterprise Manager, and as Hugo points out, Management Studio , does when you define a sorted view with the query designer (truly a tool of the Devil himself).

When I do a
SELECT * FROM dbo.myView;
in SQL Server 2005 I get the following results:

Wyman
Jones
Watts
Richards
Jagger

As you can imagine, this will break any query you have in your databases which rely on this "feature". The truly amazing thing is that SQL Server Management Studio still allows users to choose a sort order in views without so much as a warning dialog box!

So I wonder... Would this be considered a bug in the query parser, if it allows syntax that can't possibly work? Is it a bug in the engine? Should this evil trick still work? All of this got me to thinking (uh oh) -- how could one work around this issue if one is upgrading a legacy database?

And, what if you were to create the view like this?

CREATE VIEW myView AS
SELECT TOP (99.9999999999999) PERCENT LastName
FROM dbo.Stones
ORDER BY LastName;

Lo and behold, it "works" -- when you query the view, the results are shown in order. But add just one more nine:

CREATE VIEW myView AS
SELECT TOP (99.99999999999999) PERCENT LastName
FROM dbo.Stones
ORDER BY LastName;

and you're back to the unordered list. Strange. I suppose that the SQL Server engine is doing a bit of rounding internally and throwing away the sort order altogether as an optimization so it won't have to perform a final sort on the resultset for no reason. I really have no way to know exactly, though.

Think setting the compatibility mode to 80 will save you? Think again. This behavior is independent of the compatibility mode you use.

The moral of the story:
Before migrating your databases to SQL Server 2005, make sure you have no TOP 100 PERCENT ... ORDER BYs in your views.

Analyze every query you run which consumes views with TOP 100 PERCENT ... ORDER BYs, and make sure they're ready for the new behavior.

(This behavior is consistent both in SQL 2005 SP1 as well as the SQL 2005 SP2 Community Technology Preview)

(Photo by FRANKIEB)

What this is all About

2006-12-29T18:58:00.000-08:00

Welcome to my first blog ever, "Execution is Everything".

This site is about all things related to the discipline of software engineering and software development. You'll see an eclectic mix of process topics, front-end design and implementations, and database-related articles.

I feel that before I begin, I need to give you some promises.

No personal stuff. I won't be blogging on my vacations. You won't ever see baby pictures here. I won't be talking about my hair or "that really cute girl at work/on the Metro/etc".
No politics. I don't even care if you work for Globex Corporation -- I won't judge you.
I will moderate comments here. Constructive criticism is encouraged. I'll do my best to keep the signal-to-noise ratio here as high as possible, even in the comment section.
I will try to post once every week or two. I guess that's not really a promise. Hey, you get what you pay for.

To get all of this stuff out of the way, let me share with you some of my favorite blogs. You should check these out:

PluralSight's blogs: These are some of the most experienced folks in the industry. Keith Brown's security articles are always on the top of my must-read list.
TheDailyWTF.com: If you can't learn from your mistakes, at least learn from these guys. The content seems to be slowing down, but I still check it out from time to time. You should too.
Coding Horror: Jeff Atwood always has something interesting to say about the industry -- and more often than not, the folks commenting on his site can be quite thought-provoking.
Joel on Software: He may have jumped the shark, but we still love him anyway.
Doomed Engineers: OK, it's not really a live blog, but it's always nice to know that it could be worse.
SQLBlog: It's worth checking out periodically just because the illustrious Kalen Delaney posts there. If any one author has paid my grocery bills for the last five years, it's her. Inside SQL Server is one of my favorite technical books of all time, and gives an enormous amount of insight into what's inside the "black box" of SQL Server. I've come to believe over the last few years that if you haven't read this book, you probably aren't a SQL Server expert.