Planning, ad nauseum

OK, I've talked about planning in the past and I'm going to mention it again, but from a different perspective.  There are many different types of planning for many different types of people:

Programmers. These people need to know what they are doing now and for the rest of the week.  To be honest, while they might like to know what they are doing next week, as long as they are confident that their boss knows, that is probably good enough.

Team Leads.  With programmers reporting to them they need to have a much better grasp of what is currently going on and what is coming up.  A week isn't good enough.  A month may not be good enough depending on the project, so let's say that these people need 6 weeks of planning under their belt.

Project Managers.  Ah, the brains behind the project.  For any particular project that  they are working on, they need to have a good idea of staffing requirements from beginning to end.  Yes, they need to know staffing for the entire project, because if they are short, it is their responsibility to get staff.  If they are going to be over staffed then it is their responsibility to see what other projects need help.

Program Managers.  These people are responsible for the smooth execution of multiple projects within an overall program.  For example, you may have a Program that is composed of a lot of smaller initiatives, all of which are leading to a single vision, much like PASI.  Program Managers need to understand the interrelationship between the different projects and how they impact other projects and the program as a whole.  This sort of planning stretches into the years.

Organization.  Different organizations use different benchmarks, but it is considered a good idea if an organization can tell you what they are going to be doing for the next three calendar years.  This will not be to a great deal of accuracy three years down the road, but the overall plan of the organization, the inter-relationships between ongoing programs and the intended implementation date of various projects should be at least plotted out so that both clients and staff have an idea as to what is currently happening and what the short and longer term goals are.

As you can see, the level of detail goes from the microscopic, tactical information needed for day to day activities to the macroscopic, strategic information needed for long range plans.  Virtually everyone in an organization needs a different level of planning with a different focus, depending upon the roles that they are currently occupying.  The key to all of this, though, is that there needs to be someplace where all of this information can be stored.  Some place where the microscopic and macroscopic views can both find a home.  A place to go when information is needed and a place to go when you have new information.

Peer Review

Why are some people against having their work reviewed by their peers?

Commonly called a "peer review" in academia, the concept revolves around letting other people knowledge in the same field comment on your paper (design, theory or observation) prior to "publishing" it.  Specifically to the IT world a peer review is when a design is reviewed by knowledgeable people in an effort to determine any flaws in the design.  I will grant you that these reviews can be brutal on the ego, but aren't they the right thing to do anyway?

One of our primary objectives in our job should be to produce quality work.  Now, the exact measurements behind defining quality are highly contentious, but in general, the idea is to produce something that accomplishes the objective and does it as efficiently as possible.  One of the keys to creating a quality product is having a good design, one that is as simple as possible yet still satisfies all of the objectives and goals.

A good design.  How do you know if a design is good?  Well, one of the ways that the rest of the world  does it is through design reviews or peer reviews.  When a design has been completed it goes to someone else knowledgeable about the requirements so that they cam see if the design does indeed meet those requirements and does it in an efficient manner.  Seems pretty simple doesn't it?  So why don't we do it more often?

The Enterprise Architecture Working Group (EAWG) did this, to a very limited extent, when they reviewed the application architecture on various projects, but the problem was that at 50,000 feet everything looks blurry.  As a friend of mine used to say "Good from far, but far from good".  Understanding how someone is going to piece things together is important, but the details, the microscopic manipulations that occur on an individual program level are completely oblivious to everyone except for the designer and programmer.

Every critical piece should be peer reviewed to ensure that it is designed properly.  This means that if you are writing something that manipulates funds it needs to be peer reviewed.  If you are writing a mission critical import/export/reporting piece, it needs to be peer reviewed.  If you are writing a security component, it needs to be peer reviewed.

If it's never bee peer reviewed, never had an objective review of it's design, how are you every going to know if you designed something properly?  Is it fear that is stopping you?  Fear of people pointing out mistakes?  Or is it narcissistic in nature?

GUIDS and Clustered Indexes

GUIDS should never ever be used as a clustered index.

Wrong.  GUIDS can be used as a clustered index if they are not an MBUN: meaningless but unique number  I've talked about this before, but it seems to be difficult for people to understand so I'm going to take this slowly and give some examples.  For these examples, let's assume that you are running an online bookstore.

Naturally you want to keep track of orders and you have a table in SQL Server that does just this.  For each order you assign a GUID.  Should you cluster on this GUID?  Probably not and the reason is that the GUID, in this table, is meaningless.  It has no context with any other data and as a result it is truly an MBUN.  Indeed, you would probably want to sort on the customer number instead as you are more likely to retrieve all of a customers orders than you are sequential GUIDs.

Hmmmm.  We're keeping track of orders, but not the items on the orders.  Let's have an OrderItem table.  In order to connect the rows in the OrderItem table with the Order table we would probably link them via the GUID that you assigned to the Order itself.  To show the individual items within the order we would need to create another column, let's call it an ItemNumber.  This could be a GUID, but it makes more sense just to make it an integer.  For this table the clustered index should be the Order GUID and the ItemNumber.

Why?  Well,. when we create a clustered index we are telling the database that we want records that appear next to each other in the index to actually be stored next to each on the disk.  In the first example, using the GUID made no sense as there was no reason for the records to appear next to each other.  In the second example it makes perfect sense to use the GUID because we want all of the items for an order to appear next to each other on the disk.  This speeds up the retrieval of data and makes more effective use of memory.  In the second case the GUID is no longer an MBUN as it actually has meaning: it is the order number that we are tracking.

So you see, if the GUID has meaning it can be part of the clustered index.  If the GUID has no meaning, don't put it in the clustered index.  Any questions?

Database Design

How much time do you spend designing your database?  Twenty percent of your overall design time?  Ten percent?  Five percent?  Two percent?  Design?

Well, the magic number should be 15%, or thereabouts, and can be determined by the juxtaposition of the solar days as they are related to the cardinality of the days of the week, the overage as attributed to project management style and the variability imposed by Pantone colours as they are blended on a surreal palette of olives and daisies.  Just kidding, there is no magic number.

Good database developers require less time to develop a well structured and functional database.  A smaller application with a more focused purpose requires less time as do re-writes of existing systems and applications for which there is a wealth of knowledge available.  Conversely, applications that are new to the organization, new to the designers or are complex in nature require considerably more time in the design phase.

Database design, contrary to what many people believe (and contrary to what I've seen implemented) is a complex task.  Understanding the data access paths that the application takes, the appropriate indices to create and how to optimize that access is difficult for anything more complex than a simple inventory system.  Why?  Well, if you have a column that contains two values "YES" and "NO", with approximately 100 "NO" for every "YES",  and you create an index on that column, will SQL Server use it?  That depends on how many rows are in the table.  SQL Server will make the determination, at some point, that the index in its current form is useless.  How about if the column contains four values, will that help?  It will delay the inevitable, but regardless, SQL Server will, at some point, say "To heck with it" and not use the index.  The cardinality of the columns in an index is as important as the index itself, yet very few people understand this rule and as a result, indexes are created that may work well today or tomorrow or even for months.  One day, however, the house of cards will collapse.

Take some extra time and work on the design up front and I'm sure you'll be pleasantly surprised by how little pain you feel when it goes in.