Single Point Administration

This article will in all probability solicit a Don’t-you-think-I-know-That-….Duh! Response from the majority of people who frequent this site, but I still think it worthwhile to reinforce some good coding practices and to give myself a good excuse for a few moans and groans.

Wherever I have come across existing coding, be it SQL or VB et cetera, I never ceased to be amazed at the decisions some developers make at the beginning of a project and the consequences of their actions thereafter. It’s further exacerbated by the fact that I am often the person left to deal with the problem as I am sure a lot of people reading this are also.

I’m talking about duplication of code that makes for an administration nightmare. I cannot reinforce enough the importance of making sure that you should never write the same code more than once. Not only is this inefficient and a waste of valuable resources (I.E. yourself), it also makes administration a real pain when you have to visit multiple lines of codes to make one change. Rather than taking the code into notepad and performing a search and replace, there are prevention methods available that are easy to implement.

The title acronym of SPA is synonymous with cleanliness and can be visualized as somewhere a person may go to bathe and generally refresh themselves away from work. The same principle applies here. By taking the correct approach to coding, you can save yourself and others a lot of stress by keeping everything tidy and clean.Rather than go into great depth on a particular area of coding, I will leave that to the specific experts around and the many postings on other sites. I am looking at the bigger picture in terms of dos and don’ts.

Firstly, I’ll start with some scenarios and go on to identify a common sense approach that would have prevented the problem. As with most things, there is more than one way to skin a cat and these solutions are an example of one way of achieving the objectives.

Example 1

Work performed: Rather than select * from a table, a developer takes

the approach to only select the columns required in some SQL code,

which is more efficient in essence. The same table is selected from

fifteen times in various stored procedures, none of which are

documented.

Change required: One of column names has been changed and a new column

added.

SPA Solution: A view should have been used to select from the table.

1 change required in 1 view only.

Example 2

Work performed: A developer writes multiple stored procedures using

xp_sendmail to send mails to five named users.

Change required: One of the users leave and is replaced by someone new.

SPA Solution: Rather than mailing users by name, an email group should have been used. 1 change required in 1 mail group.

Additionally,

one separate stored procedure should have been written to use the

xp_sendmail in case the group name changes. 1 change required in 1 sp

only.

Example 3

Work performed: An application requires a specially formatted date that a manager prefers. Every page in a web application displays this date in the top right corner. A developer writes the same SQL statement in all the pages.

Change required: A new manager decides he does not like the date and would prefer the format returned from SQL getdate() function.

SPA Solution: Create a UDF in SQL selecting getdate(). If the date format changes in the future, 1 change required in 1 function.

Example 4

Work performed: A DBA has created multiple tables, all storing the 8 digit product code. For accuracy he/she has chosen to use a char(8) datatype as the manager has confirmed that the code size will never change.

Change required: The Company has merged with another. Their product codes differ and are up to 12 digits long.

SPA Solution: Create a user defined datatype called product. 1 change require to 1 UDDT.

These are only a small sample of headaches available for free. Most of the problems created can be solved through careful planning in the modelling stage. No area should be ignored or treated any less significant than another. A lot of the problems can be solved by using common sense; you do not need to have advanced coding skills. Indeed, the mail example requires the work to be done by somebody else in terms of changing groups.

The golden rule is:

If you do or might do something more than once – Don’t.

Always look for a solution that will allow for administration at a single point. We are probably all guilty of ignoring this rule now and again. It’s human nature to do something twice and think to ourselves that we’ll change it later on. It’s also human nature to forget to. Keeping this rule in our heads whenever putting together models and specs WILL save work in the long term. Especially important if it’s me.