The Software Devotional

Highest Specificity Query - Database Query Pattern

I don't know a better name to call it, but a database query pattern I have used *A LOT* is what I call a 'highest specificity query'. The best way to describe it is to provide a simple example.

You have an application used by staff members in a hospital. All of the staff members use the application, but a configuration option in a central database is used to determine each user's level of security inside the application. The level of security, for the purpose of this example, is expressed as an integer value from 0-9, but could take any form required by the application. Since there are potentially thousands of users at each facility, you want to be able to configure a default value at the system level, and then override the value at the facility, department, and user levels. The application is designed such that a value specified at the user level would override department and facility settings and a setting at the department level will override a setting at the facility level.

I like to keep things simple, so I use a single table to host this configuration data. Check out the SQL Server view of the table configuration to the right.

Here is how the SQL table works. If you want to make an assignment at a facility level, set only the facility ID and securityLevel for the row (leave departmentID and userID Null) - any users associated with that facility that don't have a more specific securityLevel setting (set at the department or user level) will get that securityLevel value. Similarly, the securityLevel can be set for all users of a department by setting the facilityID and departmentID (leave the userID NULL). Check out the sample table below.

• rowID 1 provides a base security level for any users who aren't configured with a more specific securityLevel vaule.
• rowID 2 overrides rowID 1 and sets a security level of 1 for all users of facilityID 1. In this example, this would provide a default securityLevel for all users at the facility of 1. A security level of 1 is a low level that might provide only read-only access to non-sensitive information.
• rowID 3 overrides rowIDs 1 and 2 and sets a security level of 5 for all users of department 20 inside facility 1. This might represent a clinical department that requires write access to clinical areas of the application.
• rowID 4 overrides rowIDs 1, 2, and 3 and sets a security level 3 for user 345 inside departmentID 20 and facilityID 1. In this case, a new employee at the facility has a lower level of access than everyone else in the department.
• rowID 5 overrides only rowID 1 and sets a very high securityLevel for facilityID 2. This facility asked to keep security simple and give everyone a high level of access.

The SQL query used is shown below. It is written with the assumption that the query is running inside a stored procedure with parameters named @p_facilityID, @p_departmentID, and @userID. When the query is run, the values for facility, department, and user are provided by the calling application, and only the most specific row is returned from the query. Take a look at the query below.

SELECT TOP 1 securityLevel 
FROM SecurityLevels 
WHERE 
	(facilityID = @p_facilityID OR facilityID IS NULL) 
	AND 
	(departmentID = @p_departmentID OR departmentID IS NULL) 
	AND 
	(userID = @userID OR userID IS NULL)
ORDER BY 
	userID DESC, departmentID DESC, facilityID DESC

Some notes about the query:

• TOP 1 is included to force the single most specific row to be returned by the query.
• The mildly complex WHERE clause filters rows where the row either matches the value for the field or is set to NULL. This allows instances where none (or perhaps only a portion) of the criteria match rows in the table. For example, using the example table above, suppose a query is run where the following parameter values are provided: facilityID = 2, departmentID = 100 and userID = 300. The WHERE clause will return only the row where rowID = 5, because it was the only row where even a partial match of the search criteria was found.
• The ORDER BY clause puts the most specific row at the top of the results (the TOP 1 ensures that only the results from this row are returned). In the example, we want settings at the user level to override any other settings. The priority then falls to department and then facility. Therefore, we sort in the order of userID, departmentID, facilityID. The DESC argument ensures that non-NULL values sort ahead of NULL values, ensuring that the top record in the result set is the most specific row for the given search criteria. In the example table above, searching using facilityID = 1, departmentID = 20, and userID = 214 would return rowID 3, since that row is the best match.

You probably noticed that I used a NULL value to represent a value that isn't assigned in the table. It's also possible to use a zero for unassigned values. It's a trade-off either way: if you use NULL values then you need a surrogate primary key since NULLable columns are not candidates for primary keys. My example above uses rowID for the surrogate key. However, if you use zeros then you either can't use referential integrity or you need to add bogus rows (with zero values) to the primary key tables.

This type of table and the highest specificity SQL query that goes with it has been an indispensable pattern in building enterprise software solutions. In the example above, I used only 3 fields for search criteria (facilityID, departmentID, and userID), but the pattern supports any number of search criteria necessary for your needs - simply add the columns to the table and modify the SQL query accordingly. Further, since the bulk of the configuration in these tables will be in the higher-order fields (that's been my experience) such as facilityID or departmentID, the tables remain very small even for very large implementations and perform very well.

Suggestions for Keeping Your .NET Skills Current

I got caught in a flypaper conversation a while back during which one of my fellow developers asked for suggestions on how to keep his .NET skills current. Professional development, as it is generally called, isn't something I think about often. I read a lot, read lots of blogs, speak and write about .NET development, and experiment with lots of new technologies as they become available. Professional development is only a side-effect of my curiosities and interests.

If my observations are anything close to reality, I find that most developers do not spend nearly enough time sharpening their saws. Steven Covey, who coined the phrase in his book 7 Habits of Highly Effective People, describes sharpening the saw as "having a balanced program for self-renewal in the four areas of your life: physical, social/emotional, mental, and spiritual", with the mental area concisely described as "learning, reading, writing, and teaching." The part of the saw that I am talking about is in the mental department.

Other than the 'soft' skills required for advancing in any team environment, a developer must constantly be sharpening his skills in software development in order to continue to be a successful software developer. Developers need to be knowledgeable enough about a broad range of topics to bring the right tool to the table when a need arises. Even if you aren't an expert with the tool, or even very proficient, you need to know where to start. For developers who make a habit of keeping their saws sharp, not everything will look like a nail just because they know a lot about hammers.

Back to the question of skills. So I thought about the things I do. Here is a brief list of the things I do that I think help keep my .NET skills current.

Magazines

Unfortunately, there don't seem to be many mainstream magazines that specifically cover .NET development. However, we have some pretty good ones. If you are on a budget, you will find that you can find the content of most magazines available online.

MSDN Magazine is probably the most well known .NET/Microsoft development magazine, although it isn't my favorite. In my opinion, the articles are too long and detailed (does that sound backward?). In most cases, I am looking for bite-sized, ADHD-friendly, articles that cover the "why, what, and so what" questions along with enough technical detail to get me started on a project. I would prefer more articles that dive deep on more narrowly focused subjects so that I can pick the topics I am interested in. Nonetheless, it is still a very good magazine for staying current with Microsoft technologies - especially the new and yet-to-be released technologies.

Visual Studio Magazine, while it almost faded out of existence several years ago, is experiencing somewhat of a revival. Good authors have been contributing some great content and the magazine has become something I read more regularly.

Along the same lines, CoDe Magazine provides some pretty good .NET technical content as well, but hasn't made it into the group of sources I consult regularly for .NET content (no can explain). One very nice feature that I love about CoDe's website is their topical index. Click any one of numerous topics such as WCF, AJAX, XML, etc. to see a list of articles covering the topic (similar to a tag cloud on a blog).

Find It Online

In addition to reading magazines, preferable online versions, you will find that there are lots of web sites devoted to .NET development topics. I will suggest some, but there really are too many to try to list here. My suggestion is to find several that focus on the area of programming that you want to learn about, and check back with the site regularly for content of interest. Most of them allow you to subscribe to a syndication (RSS/Atom) or email feed to have regular updates pushed to you when new content hits the site. Subscribe to a few and spend a little time each week reviewing the new content and reading/skimming the articles that interest you.

Before getting to the online-only sites, keep in mind that most print magazines (including MSDN Magazine, Visual Studio Magazine, and CoDe Magazine, all mentioned above) can be read online.

One of my favorite programming web sites has always been DevX. <FullDisclosureStatement>I must point out that I also write periodically for DevX, but that is only because after searching around several years ago, DevX was the online publication I liked the most.</FullDisclosureStatement> DevX is a very broadly scoped programming site - it does not specialize in any single type of development and they generally don't dive overly deep on any single topic. Instead, DevX offers a very broad set of content for software developers of all types and ranges of experience. Subscribe to their email digest in the programming areas you are interested in.

The Code Project is also a favorite of mine. The Code Project is a site where developers go to post how-tos, sample code and articles about software development. Like DevX, The Code Project serves a very diverse audience. However, there are so many new contributions every week that you can typically find several different articles on any subject (most with sample code) you are searching for.

Two others that I follow very closely are InfoQ and TheServerSide.NET. InfoQ is a very broadly based architecture and development community news site. It covers everything from Java and open source development to .NET development. If you consider your role to encompass that of architecture, make sure you keep up with the news flowing out of InfoQ.  TheServerSide.NET is slanted more towards boots-on-the-ground developers practicing the art of .NET development. Both are good places to keep up with the world of development.

Even better than reconnoitering the web site on a regular basis, which you are likely to forget to do on a regular basis, sign up for the periodic emails from the sites. It pushes the content right under your nose. If you spend the time to read through these messages, you will have no trouble staying on top of the goings on in the industry.

Blogs

Blogs might initially seem similar to online magazine articles, but after reading through them for a while you will find that they are generally much different. Blogs are typically more narrowly focused streams of content. You will find authors blogging about things they come across in their jobs: programming VB.NET, software architecture, WinForms control development, etc. What's better, most blogs support subscriptions so you can not only be notified of new content, but also so you can read the content at your leisure from any of a number of great blog readers (if you don't have one you already LOVE, try Google Reader). The idea here is to pick lots of blogs from authors that post about topics that interest you. Plan to add more blog subscriptions as you find more content and to unsubscribe from blogs that go stale.

If you are a .NET developer, check out Scott Guthrie's blog. He posts on lots of topics of general interest to .NET developers.

Do a Google search for ".net programming blogs". Subscribe to the ones that interest you. I find it helpful to browse the actual blog web site occasionally to find links to other interesting blogs. Do this periodically to keep your list of blogs fresh and interesting to you.

Although not of specific interest to .NET developers, one of my favorite techy blogs is Coding Horror, hosted by Jeff Atwood. The topic isn't always about programming, but Jeff normally writes about topics that are of interest to developers with a sense of humor that is sure to please all but my wife, who doesn't seem to find humor funny.

Books

There really aren't a lot of general purpose .NET programming books that I can recommend, since a good book has a narrow focus on a particular subject area. However, I have listed a few good places to start below.

Code Complete, by Steve McConnell, is one of the most widely read books about solid coding practices that exists. It is now in its second edition (I own both) and has been revised to include more .NET coverage.

A topic that ALL developers should know more about is design patterns. The best book on the market for learning design patterns is Head First Design Patterns. It is one of a number of books from the Head First series, which takes a narrative and almost Socratic approach to teaching design patterns. After reading Head First Design Patterns and when you have come to understand when a design pattern is useful and how it works, you can pick up Professional Design Patterns in VB.NET or purchase download-able code for each of the patterns from the Data & Object Factory. While I don't recommend it because it is more academic that most developers need, I will get beat up (no, seriously) if I don't also mention the so-called gang of four design patterns book: Design Patterns: Elements of Reusable Object-Oriented Software.

Another great way to stay fresh is to get certified as a Microsoft Certified Professional Developer. While I don't value certification for most developers, it is a big help for either new developers or developers getting out in the job market. A MCPD line on your resume can stand out when large numbers of resumes are being reviewed. If you are happily employed, the training can yield lots of nuggets of new information you didn't previously know and, in some cases, get you a small bonus or bump in pay.

There are lots of other books you can consider. There are only a few that I can recommend to a broad audience:

Practices of an Agile Developer should be required reading for developers. Even if your development group doesn't practice agile development, read this book. It should have been named Practices of a Professional Developer.

Peopleware and The Mythical Man-Month are age-old favorites that seasoned software developers know and bring up, often in a passing way, in hallway conversations. They are good bedside reading because they will put you to sleep and because there are sound management principles covered throughout both books.

Also, as a general rule of thumb, don't shy away from management books. Having noodled over some of the issues faced by managers might be a benefit one day when you have the opportunity to lead a group of developers. Management books are also good because they can help a developer to be more self-aware of himself when it comes to how managers view developers.

Conferences/Training

The most popular conferences are the large national conferences hosted around the country. The biggest conference with the broadest diversity of content available is DevConnections, held twice a year: Orlando in the Spring and Las Vegas in the Fall. This conference draws the best speakers every time.

Another big conference to check out is VSLive. VSLive is very similar to DevConnections, except on a smaller scale. VSLive travels to more venues so you might find one closer to home.

I am very happy to see that there are smaller locally hosted conferences popping up all over the country. These are often non-profit and often hosted by a local user group. They don't have nearly as many nationally recognized speakers, but are LOTS cheaper, don't require travel, and last only 1-3 days. These factors make local conferences easy to get approved by your manager. Ask around at a local user group or check out Community Megaphone to find a conference near you. If you live in or near Nashville or Knoxville (Tennessee), check out DevLink (Nashville) or CodeStock (Knoxville) - both are great examples of local conferences offering great content at unbeatable prices.

Microsoft hosts events in lots of cities all over the country to promote their servers and development tools. Don't miss the free training and an opportunity to network with other developers in your area. Check the MSDN Events site regularly.

Another avenue you can pursue is focused developer training from one of many training providers. In most big cities, you will probably find several training providers that offer a wide range of training. Be careful though - many training providers simply choose to use the Microsoft-authored training curriculum, which is good enough to get you started if you are starting near zero. The best training providers that I know of are Pluralsight and DevelopMentor. Both have written top-notch custom training programs covering many .NET subject areas. Pluralsight edges out DevelopMentor (seems to me that most of the best talent from DevelopMentor moved to Pluralsight several years back) in terms of high-power talent teaching and authoring the courses, but DevelopMentor has considerably broader course offerings, more training locations, it offers each course much more often, and offers several courses in Guerrilla format (10-12 hours per day for 5 days).

Conclusion

I have undoubtedly missed some important points because I pulled this together very quickly. If I have missed something you think important to mention, leave me a comment.

*VERY* Simple Example of Loose Coupling

I have preached here once or twice before about the virtues of loose coupling. I will not belabor the point much more here, except to say that I get lots of emails from people asking about specific examples of loose coupling in their corporate projects. It seems everyone is feeling the same pains. I have also received a fair number of emails that go like this: "I am working on a project and was wondering if you could help me understand loose coupling by showing me how to make my code more loosely coupled." Attached to those messages is a zip file containing their Visual Studio project. I apologize if you are the sender of an email that went unanswered or worse, I answered your email and said that I couldn't spend enough time to give you a good answer. The truth is, it is somewhat difficult to look at a large body of unfamiliar code and quickly offer meaningful guidance. I have said before that a good rule of thumb is that vigilant use of good coding practices makes code easier to test - loose coupling is a paramount example of this principle.

Recently, I have come across code that is more tightly coupled than necessary, making unit testing more difficult than need be.  Here is one very simple example - consider the code below.

Figure 1: Tight Coupling of Method to Complex Parameter Type

Private Sub DoSomeLogging( _
 ByVal p_ApplicationState As ApplicationState)

 With p_ApplicationState

 '...

 End With

End Sub

Nothing looks amiss initially, but try to write a unit test for this method. Do you see it? The DoSomeLogging method is tightly bound to the ApplicationState object. The method isn't useful at all without an ApplicationState object. Also, as you can see, to test this method you must manually construct an ApplicationState object to pass into the DoSomeLogging method. The problem with passing big classes and structures into a method like this is that it can be very time consuming (and potentially dangerous in your production environment) to manually construct such objects.

The boss says write the unit test and ship it, so you do. You build bunches of private methods to manually construct an ApplicationState object. You see that your unit tests pass and everyone's happy. You get to keep your job.

Two weeks later, you make a small change to your code, and start running your unit tests. Many of them are failing. What happened? Someone changed the ApplicationState class and/or the classes that consume it, and now your manual construction of the ApplicationState object is stale. You research the changes that were made, make changes to your ApplicationState construction code, and then deploy your changes. You see that your unit tests pass and everyone's happy.

Two weeks later (yes, it happens again), you make a small change to your code, and start running your unit tests. Many of them are failing. What happened? Someone changed the ApplicationState class and/or the classes that consume it, and now your manual construction of the ApplicationState object is stale. You research the changes that were made, make changes to your ApplicationState construction code, and then deploy your changes. You see that your unit tests pass and everyone's happy.

Two weeks later ... you get the point. The simple feedback I have for this method is that (since the ApplicationState object is difficult to construct and fragile to build manually) it is very likely easier to just pass into DoSomeLogging the data it needs rather than passing in the entire ApplicationState object.  It will absolutely lead to longer parameter lists for your methods and some refactoring will be required, but it is worth the effort. Your testing will go more smoothly and your method will be more useful.

The new and improved DoSomeLogging method will look like the code in Figure 1.

Figure 2: Loosely Coupled Version of Method

Private Sub DoSomeLogging( _
 ByVal p_Parm1 As String, _
 ByVal p_Parm2 As String, _
 ByVal p_Parm3 As String, _
 ByVal p_Parm4 As String)

 '...

End Sub

Not only is this method easier to test, but it is easier to write, it is more likely to be shorter, it is more likely to have a lower cyclomatic complexity, and it is more likely to be reused.

If you read either of my previous articles, you will know that there is more to consider than just loose versus tight coupling. Consider the following.

• What if this method is an overload of another DoSomeLogging method (like the one shown in Figure 2) and its only purpose in life is to make it simple to call the DoSomeLogging method without having to specify all the parameters in every location where the DoSomeLogging method is called. Good call, I think passing ApplicationState as a parameter is a good move.
• What if this method was intended to be used only with ApplicationState objects. My gut tells me that perhaps the DoSomeLogging should have been a method on the ApplicationState class. Even if it isn't, it is still worth considering the pros/cons of decoupling the DoSomeLogging method from the ApplicationState object. You will need to consider how often DoSomeLogging is called (see bullet above), how fool-proof you want to make the DoSomeLogging (you might want to prevent someone from passing in the wrong parameters), and lots of other things before knowing which way is the right way to go: loose versus tight coupling.
• Keep it simple. One look at the code in Figure 1 tells you what the method does and its likely consumers. Readability is a paramount goal of mine and only egregious problems are likely to trump readability.
  

If you use the difficulty-of-unit-testing method of judging code smell, you would have caught this one right off. Luckily, I think this type of coupling is relatively easy to fix and even easier to avoid.

Do Certifications Matter?

It comes up a lot. Should I get certified? Specifically, most of the people who ask me are asking about the Microsoft Certified Professional Developer (MCPD) program. In short, my answer is generally no. There are some times when I think a certification matters, which I mention below, but in general, I don't think it helps most developers.

I don't think it helps most of the time because it is usually *very* shortly after you begin working with someone that you know how good they are. Once you get to know someone, other factors become better indicators of a developer's likelihood of success. Consider the amount of software development experience and how Smart and Gets Things Done the developer is.

Here's when I think it helps:

• When you are new to software development: A really good friend that I work with has been a DBA his whole career. He works harder than anyone else I know and wanted to get into software development. He began working on his MCSD and he learned an amazing amount in a very small amount of time. And while I don't think Microsoft always publishes the very best development advice, it is always a good place to start.
• When you are jumping to a new language or framework: I know this point is similar to the bullet point above, but I wanted to call it out specifically because I have seen it happen so often. If you want to learn a new technology very quickly, set a personal goal to get certified in that technology. It will push you way past the "Learn .NET in 24 Hours" level of understanding.
• When you need to demonstrate that you know a technology: There are times when you need to demonstrate a basic level of understanding for a technology - these are times when someone doesn't know your background and needs an objective measure of your skill level.
• When you are job hunting: If you are job hunting, you should consider the benefits of getting certified in one or more of the important technologies you will be using in your job. Lacking substantial and demonstrable experience in the technology that you can show on your resume, it might help distinguish you from other candidates.
• When you are expected to have the certification: There are times when your job or role necessitates a certification. If you are a consultant, or want to be, you should consider getting a relevant certification.

If you fall into any of the categories above, go get a certification. I can't think of a time when it could actually hurt your career.

Visual Basic 10 Feature - No More Line Continuation Characters (Mostly)

It seems like just yesterday that I was writing my post about the new features in Visual Basic 9. Alas, one of my favorite features of VB9 is extension methods, but I have not been able to use them so much in my day job.

With the release of Visual Studio 2010, Visual Basic 10 will also receive some enhancements. The VB Team announced that VB10's new Implicit Line Continuation will mean that most line continuation characters that were required in the past will become optional. Because VB the carriage return is used as both a line termination character for the Visual Basic compiler and as a line termination character in the code files, it meant that we were not free to organize our code to help make it more readable. Underscores were required to inform the compiler that the current line of code continues on the next line of text. C# handles line continuation without any trouble because it uses a line termination character (the semicolon) that doesn't get confused with the line termination character used to organize code visually (the carriage return).

This is not among the biggest features in VB10. By itself, I don't think this is going to improve readability all that much (as others have said) because the underscores just don't get in the way of reading the code. It will not save VB developers much time either. Underscores will still be necessary in a few circumstances, as described here, but we should see their use go down dramatically.

Maybe getting rid of the underscores will help with readability if it encourages developers to add artificial line breaks where they are helpful for improving readability. We've all seen the ungainly parameter list in a method declaration or a LINQ query that should have been put on 6 lines instead of 2. Not requiring underscores for line continuation might help some developers who eschewed the use of line continuation underscores.

It might also give C# guys one less reason to make fun of VB;