Administering a Web Application some thoughts

In the last couple of years I have started administering a bought in Web Application from an outside vendor. I have found this really quite educational. I will not reveal the name of the product the vendor seems neither better nor worse than other vendors and many of the issues I am experiencing I see repeated with other admins of other web applications.

The software consists of various pieces of middleware linking SQL Server and the .Net and Javascript files. It uses a fairly standard Microsoft Stack to deliver a single page application. The big selling point was that that it was widely available through browsers pretty much anywhere on the web.

We have had it up and running in anger for a couple of years now and I have about 75 users all internal.

The vendor has created a new version and we now need to update. In discussion with the company they tell me that I should not be doing this myself and it is standard practice to get them in to upgrade the version. We already use it more as a stack technology to build our own applications I would want to continue this in the future. They have indicated that it will cost money to get them in to upgrade the software. This is triggering alarm bells because the existing configuration is very complicated, errors are quite regular and error messages tend to be pretty cryptic or non existent. I also like doing setup myself as it teaches me the ins and outs and speeds my learning up for systems which have non-trivial configuration.
A project board has been set up senior management are on board and well its grown legs arms and tails.

We have not widely publicised the application to the general public and they do not access the application at present.

The application itself requires significant amounts of configuration to set up correctly and parameters tend to be static and buried deep in very large HTML files. All this leads me to conclude that configuration in the new version is likely to be just as awkward. The company’s help desk is very good and there is extensive documentation but the sheer obscurity and quantity of configuration parameters makes the documentation the size of a phone directory, difficult and opaque.

Here’s some questions I should remind myself of when looking at New Web Applications in the future.
Are staff really so distributed that you require wide distribution?
Is it an internal or external distribution? – If not external do you really need a web enabled application.
Do you have internal expertise that is interested in developing applications?
With Web Applications or Local applications the backend database technology could well be the same. Internal local area development is likely to be far more flexible / cheap / quick and could motivate staff additionally it can be far more rich in its scope. It would be possible for instance to develop rich interface internally and a minimal interface externally using something like Lightswitch. This would take advantage of the strengths of both development environments.

There is a big push towards web applications I just think IT managers need to carefully consider their positions these applications tend not to be cheap and have a number of disadvantages that seem to have been pushed out of sight.

Posted in All, Configuration, Continual Professional Development, Dev Ops, General Opinion | Comments Off on Administering a Web Application some thoughts

Database Normalisation – a review of the rules to refresh my knowledge

Here is a review of the 6 forms of normalisation (yes the 4th is called 3.5 for some reason) – there are lots of summaries on the web of these, but I wanted to re-write them in my own words to ensure I understand them fully. Revision of these post real world experience is particularly enlightening.

Normalisation rules are cumulative ie a database or set of tables can be said to be 1 through to 5 compliant. To rank at the highest level it must comply with all previous forms.

That is to say that a database (or table) to be in the 2nd Normal Form must first fulfill all the criteria of the 1st Normal Form.

A database is said to be properly normalised if the first three normalised forms are inviolate.

Violations of normalisation forms subsequent to the third normal form may be legitimately required and as such are NOT absolutely required for a database to be classified as properly normalised.

Some web explanations do not list forms post 3 I would argue there is still a lot of value in trying to adhere to these less well forms but must admit to not always including them in design usually due to poor knowledge of the application domain.

The differing forms of normalisation start simple becoming increasingly complicated.
If you have a lot of experience in creating relational backends you may have deduced these issues for yourself.

(1NF) First Normal Form – Edgar F Codd – first stated 1970
Eliminate duplicate columns from the same table.
Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

(2NF) Second Normal Form – Edgar F Codd – first stated 1971
Create relationships between these new tables and their predecessors through the use of foreign keys.

(3NF) Third Normal Form – Edgar F Codd – first stated 1971
Remove columns that are not dependent on the primary key.

My example take on this is that calculated columns should NOT be included in tables

So here imagine you have an invoices table with the following
InvoiceNo (PKID)
CustomerNo (FKID)
DateofPurchase
UnitPrice
Quantity
Total

So according to the first and second normalisation forms there are no duplicated forms and the FKID customer no shows that there are relationships. However a Total figure can be calculated by multiplying unit price by quantity and as such violates the 3rd Normal Form.

The new table would resemble the following
InvoiceNo (PKID)
CustomerNo (FKID)
DateofPurchase
UnitPrice
Quantity

Total is not stored and would be calculated on the form. It is noted that sometimes you wish to store things like titles because for instance unit prices may vary over time and you don’t record want to loose the overall total by updating unit price – especially unit price is referenced from another table. A similar argument relates to things like VAT which can be changed on a yearly basis. In such a case I would argue that you still don’t break the 3NF as the value of column is not dependent on the primary key it is dependent on the value of the VAT rate at any given time.

The 3.5 Normal Form or the Boyce Codd Normal Form
This is a slightly stronger version of the third normal form developed in 1974 by Raymond F. Boyce and Edgar F. Codd to address certain types of anomaly not dealt with by the 3rd Normal form as originally defined.

Only in rare cases does a 3rd Normal Form table not meet the requirements of Boyd Codd Normal Form. A 3rd Normal Form table which does not have multiple overlapping candidate keys is guaranteed to be in Boyce Codd Normal Form.

Web investigation indicates that the most concise description is;
Every determinant must be a candidate key

and that Boyd Codd Normalised Form is not always possible

Nearest Shops
Person Shop Type Nearest Shop
Mark Jewellers H Samuel
Mark Barbers Mr Man
Natalia Coffee Shop Starbucks
John Coffee Shop Costa
John Barbers Mr Man
John Jewellers H Samuel

The issue above is that Mr Man on record 2 could be changed to a Hairdressers for shop type but the second last record John would not change and as such it would seem to be inconsistent. In this case I would split this table into two with Nearest Shop and Shop Type being linked and there being some kind of table that links People to Nearest shops.

The 4th Normal Form
A table is in the 4th Normal Form if it has no multi-valued dependencies.
Definition: Multivalued dependencies occur when the presence of one or more rows in a table implies the presence of one or more other rows in that same table.

For example lets say you have a you have a shop selling coffee – lets call it Starbucks and you have a table that lists the permutations of cofee sold in its store
CoffeeShop Coffee Product Location
Starbucks Café Late Livingston
Starbucks Americano Livingston
Starbucks Café Late Edinburgh
Starbucks Americano Edinburgh

If we know that all Starbucks sell the same products if there is a new product in one shop then we would expect that new product to be in the other shops so adding a Skinny Late to the Livingston shop implies that there should be another record for Skinny Lates in the Edinburgh shop.

To eliminate the possibility of anomalies we must place the facts about the products and the location of the shops into two tables

Products
Starbucks Café Late
Starbucks Americano
Starbucks Skinny Late

Location of Coffee shops
Starbucks Livingston
Starbucks Edinburgh

Alternatively if the coffee types did vary from location to location the original table would satisfy 4NF

The 5th Normal Form
A table is in fifth normal form or Project-Join Normal Form if it is in the Fourth Normal Form and it cannot have a loss-less decomposition into any number of smaller tables.

Or as I like to call it the – I know about it and try to adhere to it but sometimes I’m a bit lazy form.

I certainly try to keep my designs to the full six normalisation principles but there are instances where certain tables do not comply.

Posted in All, Continual Professional Development, Database Design, My UI Design Patterns, Personal Development | Comments Off on Database Normalisation – a review of the rules to refresh my knowledge

AutoHotKey : Navigation between Satellite Applications to improve Work Flow

A while back I wrote a post about how allowing parameters to be passed to URLs is a big benefit in increasing the speed with which you can navigate to individual records in apparently non-connected web applications.

But what do you do if you are faced with a satellite application whose vendor has not implemented this URL friendly facility. Users are left with the very jarring break to the flow of their work when they have to leave the application they are in and navigate to another application sometimes manually having to link to the other application records form via a search field. This searching task when multiplied many times can be really tedious, repetitive, demotivating and time consuming not to mention pointless.

How can we better serve our users?

The other day I came across an open source program called AutoHotKey that allows me to improve this task.

AutoHotKey

Autohotkey is an open source project that allows the creation and compilation of simple or complicated scripts that can be used to navigate anything on a computer. That means desktop OR web applications. The following is something that I worked out last week to be able to navigate a web application by triggering a script from MS Access vba. The great thing is that you can pass parameters from a database application to a middle layer and trigger a set of commands to be run.

Let us take the example of a recent problem I faced. Many councils throughout the United Kingdom have bought an application from a company that manages the information associated with making planning applications, it consists of both desktop and web applications that help manage the submission and decision making associated with development. The vendor recently “upgraded” the application resulting in it no longer accepting planning application numbers to its URL as a method of going straight to the record. This was meaning that users of one of my satellite applications were faced with being dropped into a search screen and then needing to manually type a field from one application into the field of another application. QED dull and repetitive task.

There follows and overview of my solution. Firstly download the following programs
1)AutoHotKey

AutoHotKey

2)iWB2 Learner – which is a small program for identifying element names and id in INTERNET explorer.
iWB2 Learner
iwebbrowser2 Download

My script for Autohotkey was as follows.

FindPlanningApplication.ahk (written in plain old notepad and saved to a known location with the suffix changed to ahk)
=====================
APPLICATION = %1%

URL := "https://planning.westlothian.gov.uk/online-applications/"

WB := ComObjCreate("InternetExplorer.Application")
WB.Visible := True
WB.Navigate(URL)
While wb.readyState != 4 || wb.document.readyState != "complete" || wb.busy ; wait for page to open
Sleep, 10
wb.document.getElementById("simpleSearchString").value := Application
wb.document.getElementsByTagName("INPUT")[4].Click()
While wb.readyState != 4 || wb.document.readyState != "complete" || wb.busy
Sleep, 10

return
===================

Using iWB2 Learner to identify the element names on the web page
This video shows iWB2 Learner being used it unfortunately does not have any sound.

VIDEO Using iWB Learner with AutoHotKey

—-
Next you will need to trigger the AHK – You will need design access to the program that is sending the instruction to do this. In my MS Access application I have the following code that triggers the script in the above.

Private Sub Command43GoToApplicationOnlineRecord_Click()

Dim strPlanApp As String
Dim strAHKname As String

strPlanApp = "LIVE/" & Me.ApplicationNo

strAHKname = "\\server1-cluster\ahk\FindPlanningApplication.exe"
Call Shell(strAHKname & " " & strPlanApp, vbMaximizedFocus)

End Sub

Notes:
The computer that holds the AHK script need not have AutoHotKey installed if it doesn’t you can compile your script into an executable that will not require installation. Here I created the executable on another computer and transferred it to the \\server1-cluster\ahk location ready to be called by the VBA

Consecutive parameters passed to Autohokey are consecutively named %1% %2% etc.. In my script I pass the planning application as %1% and rename it APPLICATION immediately.

Compiling the AHK is done by moving to a computer with AHK installed and navigating in Explorer to the file and then right click and Compile will be an option. Note the processor architecture is important when compiling. If your target machine is 32bit then you need to compile on a 32bit machine – same with 64.

Posted in All, AutoHotKey, Productivity, Programming, VBA Code MS Access | Comments Off on AutoHotKey : Navigation between Satellite Applications to improve Work Flow

VBA function to Pivot and Concatenate Child records

In situations where a Parent record has a limited number of children (0 to 10 works well) and you would like to list those children next to the parent somewhat like you would with a Pivoted table. Pivoting the table would however result in a massive table which is extremely wide. Pivoting and concatenating the fields can keep the resulting list within a manageable width. Take for example the following table.

ParentTable

So we have table with four header records and the children all relate to those parents. For each parent obtain a list of children

Firstly place the following function in a module

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function

Now set up a Query and call the function in an expression

QueryChildrenDesign

This results in the appropriate list

GroupChildrenbyParents

Posted in All, VBA Code MS Access | Comments Off on VBA function to Pivot and Concatenate Child records

Save Record before Event

If users are editing or creating a record and there is an option on the form to print out the form if they press it they will expect the information that they have just created to appear on the print preview. Normally MS Access does not save the information to the database until the record is exited or specifically instructed. This can lead to user confusion when they enter a record hit print preview expecting to see the record and see a blank preview.

Placing the following code before calling the print preview will ensure that the record is saved prior to the print preview being triggered resulting correct information being displayed in the print easy. An easy fix – you should always do the easy wins.!

Note order is important, put this before the event you are wishing to trigger (quite ubiquitous in my code)

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Its the constant small touches that make great applications

Posted in All, Database Design, MS Access, VBA Code MS Access | Comments Off on Save Record before Event

Appropriate User Interfaces

I have three guitars 10 windsurfers and 4 bikes (5 if you count a static exercise bike) I have three regularly used computers (tablet , desktop and laptop) – four if you count my phone. My bikes are not duplicates one is for racing, one is for training, one is for commuting and the final one for mountain biking. I often use my commuting bike instead of my car. I try my hand at DIY and have got to the point of building a cupboard containing drawers only for tools I have so many tools I cannot list them all from memory.

I get the impression that companies are pushing for all applications to be pushed to the web. Fine but if you have invested in an amazing building with an amazing Local Area Network why push absolutely everything to the web? Milk your assets often the longer you leave it the more stable the web environment will be – it still continues to experience massive change. Web has its advantages but flexible cheap very rich User Interface design doesn’t seem to be it. The benefit of database driven applications today is that the backend is totally independent of the User Interface. Developing an internal local area based UI can be totally independent of building a web based user interface.

Carefully consider and don’t be afraid to use the full range of tools available to you get the backend right and then work on a very very usable front end. Users want quick usable design that fit their needs they really don’t care about the technology.

Posted in All, General Opinion, Productivity, Project Management | Comments Off on Appropriate User Interfaces

Forcing or Limiting Columns Using Cross Tab Queries

MS Access has a version of SQL that can be edited to enforce specific columns being shown in queries.

Within the query editor navigate to the SQL View

Normally when a cross-tab query is selected MS Access will generate columns for every value that exists. This is often very useful but if there are no values there may be gaps in the scale. Additionally you may wish to limit the number of columns that are displayed in the query or force limited numbers to tally with a report. Some reports will crash if certain columns are not found.

Generally the last line of SQL code within a query will follow a similar syntax to the following;

PIVOT [QueryName].YEAR;

Where YEAR is an actual field name that could alter in your circumstances

Altering this to the following will for instance generate years 2000 to 2010 with no gaps even if there are no values in certain columns.

PIVOT [QueryName].YEAR in ("2000","2001","2002","2003","2004","2005","2006","2007","2008","2009","2010");

Posted in All, Database Design, MS Access, SQL MS Access | Comments Off on Forcing or Limiting Columns Using Cross Tab Queries

Using a Crosstab Query to produce Yearly Monthly or Daily breakdowns on a child table

Lets take an example where you have two tables with a one to many relationship. In my example I have a table of costcodes and a table of costs with the table of costs being a child of the table of costcodes. How do you create a query that will show you the costs by year by costcode.

Cross-tab query.

CrosstabYearlyReceiptsQueryDesign

This will give

CrosstabYearlyReceiptsResult

To alter the column headings choose the differing alternatives as follows;

For years = Format([DateActualSet],”yyyy”)

For months = Format([DateActualSet],”yyyy-mm”)
*Please note need to use – as a separator between yyyy and mm otherwise sorting may go off.

For days = Format([DateActualSet],”dd”)

And for financial periods you can alter it to a specific month by using the following

For Financial Periods = Year(DateAdd(“m”,-3,[DateActualSet])) – note in this case the year that appears in the start column is the start of the Period -3 Denotes 2015-April to 2016-March but appears in the column heading simply as 2015

This format would work well for example for Stores against receipts.

Posted in All, Database Design, MS Access, VBA Code MS Access | Comments Off on Using a Crosstab Query to produce Yearly Monthly or Daily breakdowns on a child table

The Handover – where the dragons lie

woman-see-dragons-everywhere-2

The handover – often someone figures out that something needs to be done – maybe they have realised an important omission or maybe there is a problem that only comes to light in a process when you start designing.

For whatever reason you can’t do the work yourself and you need to hand the work to someone else. This I consider to be a dangerous task the threat of information loss is immense. Here are some points I try to ensure to minimise this ever present risk.

Give it to an identifiable person – I always find it curious how top management consider it important that they know each other and feel comfortable in each others presence they carefully pick their colleagues and treat them with respect by ensuring accurate names are used and generally constantly talking to each other often informally and flexibly. However I have seen systems implemented by management which actively discourage these same forms of informal communication and actually discourage personal responsibility.

Give that person tasked responsibility for the whole task and allow them the flexibility to tackle it how they see fit. People adjust processes so that they are more productive and will use their personal skills in the workplace often when they were not hired on that basis. A regular example of this is probably individuals who can speak multiple languages. I have seen several situations where a customer from another nation arrives and someone in a team happens to speak their language in a flexible environment that person often gets involved in that task simply because good communication is the first step in tackling a problem. I was in the Premier Inn in London and I was pleased to note that the staff all had badges on them noting not just their names but the flags relating to languages they could speak. It was nice to see waiters with multiple flags on them. Gave you just a small bit of background on the person and encouraged customers of particular nationalities to seek out correct staff.

Give it to someone who is familiar with the domain (scope of work) complicated tasks need not just technical skill but domain specific skill. Most people know how a booking systems works as nearly everyone is familiar with booking things online. But would you expect a general programmer to be familiar with the process of registering births marriages and deaths or submitting tax returns for instance. If someone doesn’t have domain specific skills give them extra time.

Close physical proximity is a bonus – so called water cooler conversations are often about work even if it may stray into what might be considered gossip. For some even then gossip may inform you of particular tender subjects that allow you to better consider the respect of your colleagues and better conduct yourself in conversation to get the optimum of work between individuals. It also allows for constant review of process and answer of questions when they arise. I prefer in e-mails to ask single questions at a time. I find that if you store up questions in a massive e-mail the first two questions are asked and the others are just omitted. As far as I can tell this is a human trait. Its my experience that when I sit close to individuals I ask questions when they arise which when answered usually completely negate the need for further questions. This uses comparative advantage which is always a bonus.

Concentrate on the outcomes not the technology that they are using especially if there are no costs to something let them use what they want. Databases are databases – spreadsheets are spreadsheets – they prefer the dvorak keyboard to qwerty keyboard good for them. For some spreadsheets are better for them than databases others will always choose databases – Does it really matter. I have seen some really excellent spreadsheets for tasks that I would have preferred to use databases. You could argue one over the other for particular tasks and maybe even prove that one was better than the other on somekind of mathematical basis. But would SQL Server really be better for your granny than a spreadsheet. NO.

Ensure that the individual will have a continual link to the system post development – he should recognise that the stability and the continuation of the system is as important as simply pushing something out the door.

At least initially continually check up on the individual and ensure that things are progressing as intended. This requires time at the beginning but you should be able to wind it down after that.

Handovers are fraught with danger don’t make them be fraught with danger for you.

Posted in All, Configuration, Dev Ops, General Opinion | Comments Off on The Handover – where the dragons lie

Business Continuity

I see Business Continuity frequently evidenced as a reason by management to disallow staff responsibility for their business systems. The belief being that individuals will become too central to the operation of the organisation and will become somehow irreplaceable.

For the record I would like to state that I consider this to be failed logic.

Parents with children will encourage them to take responsibility by trusting them with tasks. Why is this important? Why? Because one day the parents won’t be there to support their children who will have to stand on their own two feet and it is better for the offspring to make mistakes with the parents there to catch them than to wait till the parents are gone and then for them to make the same mistakes. Many parents consider that their children do things in inefficient ways nonetheless they continually trust them. The world continues to progress and the human race continues to be the most successful species on the planet (hands down). It is the mechanism of passing knowledge down from one generation to another. So giving responsibility and trusting children far from decreasing continuity has a long history of ensuring it.

If anyone uses business continuity as a reason to prevent individuals from being given responsibility you might want to highlight the above point to them.

Posted in All, Continual Professional Development, Dev Ops, General Opinion | Comments Off on Business Continuity

International Domain Names

I was helping to set up a domain for a friend the other day and I wondered whether they would like a domain in Cyrillics. Led to the question was that even possible.

I try to be aware and present but things pass me by sometimes. Particularly because I don’t really watch television. I have already posted about Cyrillics not being supported in some visual basic editors well turns out Cyrillics were not supported for URLs until 6th of May 2010.

It would be interesting to see the take up of Cyrillic URLs I note that the Cyrillic domain of Yandex url яндекс.рф re-directs to a latin version

One interesting url is the Russian President
президент.рф

Some quick research has revealed that DNS convention is old (no real surprise there) and only supports the 26 Latin characters A through Z numbers and the dash. When a non-latin based URL is placed in the address line the alphabet is rencoded to a system called Punycode which is a way of representing a domain name with a non-DNS character set within the DNS character set. These domains are called International Domain Names (IDNs) If you wish to display IDNs properly within the address bar you need to go to Chrome settings and select the appropriate language relating to the character set of the IDN the url should appear correct otherwise you get a strange punycode translation that might be mistaken some weird non-base ten numerate system.

Doubtless Yandex did not consider this attractive as many peoples first reaction to that kind of URL would be to think that they had been re-directed to a dodgy website.

Posted in All, Configuration, Русский | Comments Off on International Domain Names

SQL Saturday coming to Edinburgh again 13 June 2015

SQL Saturday is coming to Edinburgh again Saturday 13 June 2015 I hope to attend. I have attended the two previous events I highly recommend it. Thanks must go to Jennifer Stirrup and others for taking time out to organise and speak at it.
Venue is Pollock Halls on the edge of Holyrood Park in the beautiful city of Edinburgh.

Edinburgh SQL Saturday event page

Posted in Continual Professional Development, SQL Server | Comments Off on SQL Saturday coming to Edinburgh again 13 June 2015

Methodology Compound Interest Calculation

A formula that can be used for calculating compound interest

A = P(1 + r/n)nt ; R = r * 100

Where:
A= Total Accrued Amount (principal + interest)
P = Principal Amount
I = Interest Amount
R = Rate of Interest per year in decimal ; r = R/100
t = number of periods
n = compounding period

UK Base Interest Rates source;
www.databank.rbs.com

So for example if we want to calculate interest on £100,000 over a period of 4 years and 8 months based on an interest rate of 4.0% over the base of 0.5% over differing compound periods;
Compounded annually;
A = 100,000 ( 1 + 0.045/1)^4.67 = £122,821.10
A = £122,821.10

Compounded Monthly;
A = 100,000 ( 1 + 0.045/12)^56 = £123,319.40
A = £123,319.40

Compounded Daily
A = 100,000 ( 1 + 0.045/365)^1704 = 123,376.30
A = £123,376.30
Please note : simplification this calculation leap years re-calculate if important
(^ indicates to the power of)

Posted in All, Applied Mathematics, Finance, Formula | Comments Off on Methodology Compound Interest Calculation

Project Management – Things that help lead to successful outcomes

FinishLine

Many roles within organisations now require good project management skills especially when it comes to implementing new IT systems and applications. But are there things that can be put in place at the beginning to improve your chance of success. I would say yes and if I am involved in a project my personal guidelines are as follows;

Step 1 : Get Stuck In
The benefit of computers is that manipulated electrons are essentially free and immortal. Try to rearrange a few. If you aren’t getting anywhere wipe them and then re-arrange them some more. Even if you are not successful you are successful in knowing that one particular arrangement cannot be achieved. You are creating a machine just like children do with Lego or engineers create with bricks and mortar except your bricks can immediately be removed and copied infinitely and each additional brick often costs nothing. In most organisations you will quickly come up against configuration and security problems. Configuration and security problems come out of nowhere often and can be project killers best to know about them up front.

Step 2 : Know your Technology
If you don’t know it at the beginning you better hope you know it at the end – go to step 1 if you are struggling with step 2 – That’s recursion for you.

Step 3 : Increment often and test constantly
Set short deadlines and try to regularly meet with client to show progress – can be frustrating if clients start going off on tangents

Step 4 : Know the Process
To date I haven’t been asked to design any systems that I have had particular difficulty in understanding the process. Undoubtedly I think this would be different if I was trying to create an application for geology exploration or for instance mapping or maybe translation. The mathematics behind those kind of applications are complicated. Most business processes tend to be remarkably simple and the simple act of normalizing the data is usually enough for me to get to grips on how the system will be used.

Step 5 : Build in redundancy
Properly normalize your data build in extra fields if you want even if they are not used – for example collecting information on individuals I always add a field for date of birth even if its not spec’d invariably someone comes along and says actually it would be useful to know what age our customers are.

Step 6 : Have privileges
There’s nothing that will slow down a project quicker if you have to hand over responsibility of tasks to uninterested individuals who are not part of the project team. Better to have those people in the team and make sure they are on board with the importance of following through with the project.

Good luck and happy hunting

Posted in All, Database Design, Dev Ops, General Opinion, Project Management | Comments Off on Project Management – Things that help lead to successful outcomes

Guidelines for E-mails

Not the most exciting of subjects but everyone is involved in communication whether its sorting out lunch with a friend through to organising massive engineering projects. Confusion and misinformation is always an ongoing risk resulting from bad communication which results in individuals either not getting on board with a topic or going off and doing needless work. Today I really rallied against the endless stream of emails I had been cc’d into. I realised that I am probably as guilty as others in doing this so I decided I should figure out some guidelines for tightening up my e-mail etiquette

My guidelines;

1)If you are replying delete generic titles and write a title which is more specific about what is contained in the e-mail. (Try not to use Re: if you have to state Reply )
2)Spend time making and crafting a really relevant reply which is as targeted as possible.
3)If possible delete the train wreck of replied e-mails – most of them are not read by anyone and quite often have all sorts of tags, addresses and generic images which are totally irrelevant.
4)Be economical in cc e-mails if you want to introduce others into a project – write a specific introduction e-mail for that individual explaining everything don’t just copy them into a massive e-mail and expect them to go back through and figure out what is going on.
5)If you have questions that you need answered really start with the question and explain the context.
6)Use pictures – They are useful and e-mails really support pictures very very well now.
7)If you have simple calculations from excel spreadsheets contain them as images in the e-mail attachments are annoying and take time to open up.
8)If you are working with people on a project you really need to have met the people. If they are in your building why not go and introduce yourself?
9)Hey use the phone its there it costs nothing again more important if you don’t know the person.
10)Don’t just write with questions – if you have an outcome that you think is important or you’ve received some positive feedback after project completion let the others on the project know about it.
11)Generally aim to be positive and supportive
12)If you make commitments try to remember them and stick to them.

Posted in All, E-mail, Productivity, Project Management | Comments Off on Guidelines for E-mails

Predictable URL design

Being-predictable-allows-others-to-control-youAs we move to more and more web based applications it is going to be more and more important for developers to really think about how users access their web applications. Individual pages or search options may be more important than the simple landing pages. Particularly if you are wanting to get customers and users to be continually coming back to your site. Often the home page is very much a preamble that is likely to be skipped as quickly as possible so maybe if you have a single purpose site you want to have the search right up at the front on that landing page – alternatively if you have multiple applications essentially combined as part of a domain each search page needs to have a lot of care and attention to allow the user to get to his required information as quickly as possible.

In many cases it is particularly useful to have URLs to which can be passed parameters – I have already talked about my use of Google Maps in this respect but the list of sites that have predictable URLs that can be manipulated objectively with outside non-key referenced attributes reads as a list of the A to Z of some of the best companies on the web.

Examples as follows.

AMAZON
Linlithgow can be swapped out for anything and you can go straight to a list of the books you are interested in. Here’s an example of three different search strings Linlithgow, Quantum Mechanics and Programming.
http://www.amazon.co.uk/s/ref=nb_sb_noss?url=search-alias%3Ddigital-text&field-keywords=Linlithgow

http://www.amazon.co.uk/s/ref=nb_sb_noss?url=search-alias%3Ddigital-text&field-keywords=Quantum_Mechanics

http://www.amazon.co.uk/s/ref=nb_sb_noss?url=search-alias%3Ddigital-text&field-keywords=Programming

EBAY
The structure can change which is fine, as long as it is predictable and regular, here swapping two locations of the parameter “television” returns items for sale.
http://www.ebay.co.uk/sch/i.html?_from=R40&_trksid=p2050601.m570.l1313.TR0.TRC0.H0.Xtelevision&_nkw=television&_sacat=0

S1 jobs
Some sites will require links that have some knowledge of the parameters before hand. S1 jobs usually build the search string from a dynamic drop down list.
http://www.s1jobs.com/jobs/aberdeen-aberdeenshire/find?a_or_e=employer&a_or_e=agency&publicsector=publicsector&onlyshowme=datesmart

http://www.s1jobs.com/jobs/edinburgh-and-lothians/find?a_or_e=employer&a_or_e=agency&publicsector=publicsector&onlyshowme=datesmart

In such circumstances a developer will be able to directly link by noting the parameters and making the same parameters available within their application that builds the link url. (example parameters include fife / edinburgh-and-lothians/ inverness /glasgow-dunbartonshire )

Train Line
Most search filters are time specific a good design default is to assume that the user is looking for current information.

http://www.thetrainline.com/live/departures/edinburgh-to-london-kings-cross

http://www.thetrainline.com/live/departures/london-kings-cross-to-edinburgh

Sky Scanner
Certain applications actively require dates – no problem.
http://www.skyscanner.net/transport/flights/edi/mosc/150101/150102/airfares-from-edinburgh-to-moscow-in-january-2015.html?rtn=1

http://www.skyscanner.net/transport/flights/mosc/edi/150101/140102/airfares-from-moscow-to-edinburgh-in-january-2015.html?rtn=1

Air B n B
Has a really nice implementation that is really easy to manipulate – Brevity in a URL is extremely nice thing to have.
https://www.airbnb.co.uk/s/Edinburgh–United-Kingdom?source=bb

https://www.airbnb.co.uk/s/St-Petersburg–Russia?source=bb

https://www.airbnb.co.uk/s/St-Petersburg–America?source=bb

Compare this with something like www.jobs.co.uk – a nice site which will not accept parameters from outside of it.
www.jobs.co.uk

Unfortunately within the UK public bodies just have not reached this level of complication possibly because they often have a much wider remit. I did a brief search and was unable to find an example although I expect there to be some out there.

Posted in All, My UI Design Patterns | Comments Off on Predictable URL design

Access Connection Strings – Link

Some notes on using SQL Server connection strings both using the default ODBC client drivers and also the native SQL Server driver.

Juan Soto of Access Experts explains connection strings from MS Access to SQL Server

Additionally I am hearing that it is better to use the SQL Server Native Client ODBC driver than the default SQL Server driver as a recent security update on SQL Server has caused issue with some applications using the default SQL driver.

Juan Soto of Access Experts explains issues with November 2014 SQL Server update for MS Access applications

Posted in All, Configuration, MS Access, SQL Server | Comments Off on Access Connection Strings – Link

Comparative Advantage

My opinion on fixing things is that if someone has to sit around and watch you fix something the cost of the job is not just your time in fixing it but it’s also the cost of the time of the person sitting around and watching you fix the thing and the unhappiness that they feel in being pretty helpless.

Hence another reason to empower users.

How do you truly asses the cost of a job – if you are in private business it tends to be everything that you do to undertake the work. How long it is going to take you to get to the location to do the work , how much it will cost you to get to that location. In some large businesses things like time to get to a location to fix the item tends to be totally overlooked – this can be a not insignificant amount of time even if there is no revenue cost. I don’t care who you are I’m of the opinion you want to be reducing wasted time as much as possible.

Posted in All, General Opinion, Problem Solving, Productivity | Comments Off on Comparative Advantage

Developer Operations and Empowering Users My Opinion

Recently I was listening to a Channel 9 Microsoft podcast talking about how their products could be used in developer operator environments. Interestingly the podcast talked about Developer Operations being a subdivision of the central IT department and how once an application was up and running the developer would never hand over support to a separate team to maintain the code. Regards this definition I would go further and suggest that organisations should encourage super users aka citizen programmers to develop processes and applications themselves. A mid-way strategy might be to station professional developers with the applications within the sections for whom they do the work and responsible to the divisional head and NOT the IT section. I suggest the benefits of this approach are as follows;

Geographical location – Not all users work in offices where you have access to 47 inch screens with instant Skype, team viewer access and 24/7 high bandwidth internet access. Those supporting an application sometimes end up talking to Betty the cleaner who is on a landline 20 metres from the computer with the issues, the only reason you are speaking to her is because she just happened to pick up the phone, on further questioning she does have an e-mail account but hasn’t used it for a couple of weeks, she politely tells you everyone else has gone home but is sure if you phone back on Monday someone will help out. You think damn if I could just sit at the machine and see what’s actually going on. Also describing what can be UI graphical issues in text or speech is incredibly inefficient 95% of the useful information is often lost in translation. It can be impossible to articulate or describe UI problems except by demonstration. A Developer operator that is local doesn’t have to rely on understanding the problem second hand their first experience of the problem is often first hand even if it’s not they often immediately familiarise themselves with it first hand. What’s better than having an excellent logging system for tracking problems? Yes you bet, not needing one in the first instance.

Aligning your staff with Corporate Objectives – there is a reason rental houses tend to be more run down than privately owned properties. If something goes wrong it its not going to be the occupiers cost to fix it – its not my job to make it work well. This is rarely the case with people who build and operate their own systems their motivation quickly aligns with the organisations objectives – if they can make it work better by predicting problems and fixing them before they arise, their life is made easier so, they tend to get in gear.

Motivation – if they can make it work better by predicting problems and fixing them before they arise their life is made easier, that is a powerful motivator to get things right and to predict problems before they arise. The people who fight for systems are usually the users. I’ve come across a lot of IT support where they get it to an “acceptable” level which usually means it gets a completion tick often the minimum is done to get the thing working. I was once on a project and was trying to get an icon working for the web application. I got the icon from the central team but wasn’t very happy with the look of it as I thought it looked bad. I asked the project manager he agreed it looked crap but his closing comment was “who cares it was authorised”

Education – Hey guess what you learn things about your work if you know how your tools work. You start making connections that feed back to improved practices. There’s an actual name for this Constructionism proposed by among others Seymour Papert – actually the ideas been around for about as long as people have been able to think.

A quote attributed to Aristotle ;

“For the things we have to learn before we can do them, we learn by doing them”

I believe people to be naturally intelligent with the ability to pick things up – especially if they are immersed in the problem – case in point learning a language. Everyone learns it by immersion and because they have to. I don’t like the implication that because most people aren’t going to be the next Einstein they can’t be taught things I have yet to come across a colleague, myself included who hasn’t been more useful because they know more rather than less.

Reaction Time – for insurance purposes people are duty bound to mitigate damage. Now they do better job at this if they are actually on site when things happen. Central developers often have very little visibility on some of their projects and it may be days before they hear about something. Often the time to reaction can be critical in reducing damage. A water leak spotted in five minutes is fixable – left for a day and its a disaster. Granted the majority of applications aren’t that important however the same principle still applies.

An on hand developer operator doesn’t preclude additional support whereas central support requires it – they do the easy stuff they call for help for the difficult stuff. Additionally they are reliable contact for accurate description and dissemination of important information. This can be very useful. It is easy to get outside help in when things are needed it is not easy to suddenly train up local staff to accurately describe a difficult problem.

Good pedigree – hang on here’s some famous developer operators – Mark Zuckerberg programmer entrepreneur / Jeff Bezos entrepreneur and computer scientist / Bill Gates entrepreneur and programmer / David Braben entrepreneur and programmer / Sergey Brin computer scientist and entrepreneur. You might know what companies they founded. Do they promote dev-ops at their companies – I think they do. Oh and I even left out Elon Musk

Prioritisation of Work Load – Local users are far better at judging importance. The importance of a job may not relate to its difficulty or complexity. This makes it a requirement to understand the domain when deciding on what problems need to be solved first. Here’s an example – a company is going to close on a deal if they can print out and deliver a tender document. The users were a bit rushed and left it to the last minute. IT support know roughly what is going on but aren’t completely ear to the ground on tendering so the main printer support guy is off on holiday. Besides he thinks in the normal course of events a dropped printer is not an emergency. Extreme example but this kind of reward / problem ranking happens all the time. Ok computer support systems allow you to rank problems – that kind of fuzzy logic is exactly the kind of things that never really works out well and usually doesn’t allow for altering of priorities after submission. This can lead to businesses adopting the everything is important or nothing is important default setting. The first has people shouting at each other for totally unimportant tasks while the latter leads to speed of a sloth.

Security is a phony excuse – you have no defense other than vigilance against a sustained attack from an internal person who is talented. Setting ridiculous security levels on everything to protect against a few valuable parts is totally counter productive. The de-motivation of staff and subsequent inefficiency of process is a far greater danger to the viability of your business than fraud is likely to be. Resulting reduced levels of visibility which ironically can increase the opportunity for fraud and likelihood of major errors.

Posted in All, General Opinion, Productivity | Comments Off on Developer Operations and Empowering Users My Opinion

Dealing with Dates can still catch you out

The other day I was dealing with dates and I found a cracking little gotcha that might just be catching a few people out.

When is 07/11/14 not 07/11/14

When one is 17 November 2014 and one is 17 November 1914

I’ve been dealing with legal dates and I needed to set dates some 25 years in the future for payments of moneys. That’s fine just typing in dates I’ve been putting dates in which turn out to be a century in the past. This led me to investigate.

Within windows we get very used to typing in dates by using 6 digits – 01/01/14 or 09/05/15 – most users computers would recognise these dates as 1st of January 2014 and 9th September 2015. What many users may not consider , myself included , is that the interpretation of the year in this scenario is relative. That is relative to a century. We happen to be in the 21st century but if you type in 99 most computers will interpret the year as 1999 and not 2099. Fine but at what time does it change the century. Well turns out there is a configuration setting.

Here’s how you get to the configuration settings in Win 8.1

Control Panel
Clock, language and Region
Set the date and time…
Change date and time

Should get you to the following;

Win8ControlPanelDateTime

Then hit Change calendar settings;

Win8CustomizeDateFormat

The danger is of course that users will type in 07/11/30 thinking this will be 2030 when the two digit interpretation configuration will interpret it as 7 November 1930. How it interprets this comes down to the above setting. I believe at present this is the default setting.

Its one to watch out for and doubtless it may be catching some organisations out.

Posted in All, Configuration, Database Design, Win 8.1 | Comments Off on Dealing with Dates can still catch you out