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, Русский | Leave a comment

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.

Schedule of Potential Talks tbc

Posted in Continual Professional Development, SQL Server | Leave a comment

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 | Leave a comment

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 | Leave a comment

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 | Leave a comment

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 | Leave a comment

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 | Leave a comment

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 | Leave a comment

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 | Leave a comment

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 | Leave a comment

Trusting Your Gut or When Not to Use Computers.

wargamesAs good as computers are at calculating numbers they are really really bad at doing somethings that humans are just great at like making decisions based on fuzzy logic or translating things between languages. In such situations always go for the human at least until C3P0 comes along. Watson cost $3 million to build, good if you want to play trivial pursuit but ask it to choose between competing software vendors and well you will be nowhere.

That’s you numerical based ranking systems for software purchase!!!

Posted in All, General Opinion, Personal Development, Problem Solving, Software Purchase | Leave a comment

Graphics vs Text

khmerconSo today it is a bit slow for me so I answered a question from a lad in Phnom Phen in Stack Overflow. He was looking for support for Khmer in MS Access 2013, the official language of Cambodia. He has a database but unfortunately he can’t get records to sort alphabetically for Khmer text. I’m not sure I found a solution I made some suggestions. I’m always really interested in questions like that because a solution for him would be the kind of thing that would be useful to anyone able to write Khmer which is presumably millions. I had a look at the alphabet – wow – computer fonts seem to do a terrible job of displaying it reducing it to the point where at a similar scale to other fonts I need a magnifying glass to distinguish differing letters. I guess you just have to choose larger font sizes but it was tiny at the scales I was seeing examples. It made me wonder how much cultures with difficult or tricky languages are disadvantaged in day to day commerce. Obviously to me their alphabet is only pictures.

But then in reality that is only what all alphabets are.

I always think on this when people say we need more pictures and icons in the UI

Posted in All, Configuration, Database Design, My UI Design Patterns | Leave a comment

Caspol.exe

Never heard of it?

I hadn’t either – it is however a small exe that stands for Code Access Security Policy Tool which enables users and administrators to modify security policy for the client machine to .NET framework.

This tool is automatically installed with .NET frameork.
The following alters privileges of websites to allow them access to the .net framework on the client machine.

The following is the full parameters associated with a .bat file that passes parameters to caspol.exe
C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\caspol -m -ag 1 -url http://servername/* FullTrust
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\caspol -m -ag 1 -url http://servername/* FullTrust
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\caspol -m -ag 1 -url http://servername/* FullTrust
C:\Windows\Microsoft.NET\Framework64\v2.0.50727\caspol -m -ag 1 -url http://servername/* FullTrust

Posted in All, Configuration, dot NET framework | Leave a comment

OBJECT NAMING CONVENTIONS – My UI Design Patterns

I like to do several things.

Name my tables T001TextVariableInCamelCase incrementing the number
Name my queries Q001TextVariableInCamelCase incrementing the number
Name my forms F001TextVariableInCamelCase incrementing the number

This works well for me – some of my first applications only had 2 numeral positions. I now give new projects 3 digits as the more successful applications generally have more than 100 forms and queries and I like to have everything in order. I have yet to come close to more than 200 forms in an application so I feel confident that 999 is satisfactory. Of course its not a major problem if I go over 999 forms it just doesn’t order so nicely in the database window which tweaks my OCD.

All names have no spaces and are in camel case.

I like to have the forms tagged with this incremental Form Prefix – eg F001 or in the picture below F103. When you have lots of forms it is a lot easier to refer to forms by this unique key. Additionally if you are speaking to a user over the phone you can immediately identify the form that they are on that they may wish additional features on or on which they have found bugs.

These pre-fix conventions are only an implementation of the principles of database design. They are effectively primary keys for differing objects. If I was in the access team I would probably hard wire this feature into the IDE so that code referred to the primary key and text descriptions could be changed retrospectively without breaking links and if possible forms would start with the PKID placed on them. (maybe with the feature available to switch this off)

NamingConventions

Posted in All, Database Design, MS Access, My UI Design Patterns | Leave a comment

HIDE MENUS – My UI Design Patterns

Really simple – create a new module and create the following simple functions.

I usually name them TurnMenuOn and TurnMenuOff.

Public Function TurnMenuOn()

Application.CommandBars("Menu Bar").Enabled = True

End Function

Public Function TurnMenuOff()

Application.CommandBars("Menu Bar).Enabled = False

End Function

Then you can run at start up by creating a macro that is titled autoexec
And using the Runcode action call the TurnMenuOn() function alternatively you can call it from the form opening.

It is often useful to create a couple of straight Macros that run these functions as well so that when you are in design mode you can quickly run the functions.

MenuBarAlteration

Posted in All, Database Design, MS Access, My UI Design Patterns, VBA Code MS Access | Leave a comment

SEARCHING RECORDS – My UI Design Patterns

Well if it is good enough for Google

One single variable field with filtering only occurring after return has been hit. Search is across complete range of fields and searches for a string within all of the fields. Search string is maintained after search and results shown below. Don’t make the search case sensitive unless there is a good reason for doing so.

Below is an example within a development site database with Breich being used as the search item.
Searching

Posted in All, Database Design, General Opinion, My UI Design Patterns | Leave a comment

COLOUR – My UI Design Patterns

Use it – for those things just built for me I have arguable gone a bit mental before.

Conditional formatting is particularly good for dates and allowing people to quickly see what is coming up and what is past. I tend to use the traffic light analogy for this.

Icons and splash screens really add polish as well.

Example : This is a booking system for an outdoor centre and this is the opening course list on which people can book places. Dates are listed down the right and colour coded – as is the state of the course / full or cancelled. You don’t want users booking people onto these courses.
UseofColour

Posted in All, Database Design, MS Access, My UI Design Patterns | Leave a comment

COMBO BOXES / DROP DOWN LISTS – My UI Design Patterns

Controls referred to differently in alternate design environments – Drop down lists / boxes or combo boxes – are fields that typically have a down arrow next to them and afer users have selected the control a selection of values are displayed.

Make the list shown as big as possible. Generally when a person is using a drop down list their concentration is fully on that field and they will not be halting their action to concentrate on something else therefore make it easy for them if there are 10 options try and show all 10 on screen don’t have 5 options and ask them to scroll down.

Think about allowing multiple columns. Don’t necessarily sort that list according to the unintuitive subjective column. Costcodes are a good example – accounting codes are often completely arbitrary and few people can remember them better then to sort by the textual description of the code rather than the code itself, this is always good when the drop down is selecting a code that has been subjectively set.

Choose columns to show that are generally complete. Please don’t show a column if there is hardly ever any information in it. Reading from left I will usually give a maximum of 4 columns in a drop down and ensure that there is a value in every entry for the foreign key field with a lesser requirement for information as you read to the right. Addresses can be a good example of this why use district or county when they are not officially part of the Postal address better to use town and postcode.

For the values try to always use related tables and store the primary key number as an associated foreign key value rather than the value itself. This has massive benefits when you realise you have made spelling mistakes and or you just want to change things.

Ensure that the columns are sufficiently wide enough to show the full values within the columns.

Example : Enterprise application in which users can store the primary catchments associated with a development site – the associated secondary is listed along with the town , denomination and the authority that administers the establishment. In this case the schools are sorted alphabetically by the primary name.
DropDownList

Posted in All, Database Design, General Opinion, MS Access, My UI Design Patterns | Leave a comment

Enabling Geospatial integration in applications.

Despite the fact that spatially enabled databases have been around pretty much everywhere for quite sometime there’s still a heck a lot of enterprise applications out there that are not using the feature even though their backends support it.

SQL server has had the facility since 2008 , Oracle has it as well although it is with the expensive Oracle 11g Enterprise edition.

So if its available why are so many applications not using it?

Well one of the reasons is that many of the applications which would benefit from introduction are central to organisations and were in existence long before the feature was available in backend databases. So why not introduce it as an update? Well the problem is a geospatial attribute is a form of primary key more accurate than the often completely arbitrary primary keys that most tables will take as their reference. Adding it is likely to require not just the addition of a geospatial attribute which will be a defacto primary but potentially adding a full table not as a child but as a parent to the previous parent records – the former parent records requiring the addition of foreign keys that relate to their parents.

As most of you know altering primary keys in tables is pretty much equivalent to transplant surgery for a database.
Totally wiping a primary key and starting with a different primary key that needs to then be captured for itself and related back to its children is if anything several orders of magnitude worse than that..

Doesn’t sound good does it.

This is another case where if you have paying clients or you have a purchased product that isn’t going to happen until there is an outside force from a competitor. But the benefits are legion. As it stands most information in geographical systems is flat files that has to be updated directly within either the web gis or a gis desktop. These programs are really terrible making an application fully geospatial by design allows the UI you to display the geographical information in the GIS – web or desktop and related information in forms which often have vastly improved searching / linking to other systems drop down boxes well pretty much everything.

Leave plenty of time for it but would be proper automation. Too many GIS systems are mirrored copies of a database that periodically have to be updated. This is not the long term optimum.

Posted in All, Database Design, Geographical Information Systems, SQL 08 R2, SQL Server | Leave a comment

A detailed explanation of the Algebraic modeling using SQL for a Race Timing System

Running-a-Race
I know I’ve given an overview of what I did to create the timing system but here is the kernel explanation of the algebra modeled in SQL that does the real heavy lifting in my system and probably most other timing systems as well.

A review of the problem firstly;
The data works on the principle of a chip passing a matt. As the chip passes the matt times are recorded in a timing box which will then be passed through to a database.

Problem is that the timing matt will give multiple readings every time a runner runs across the matt and he or she may be running across multiple times the results only need the first of those passes and not any of the other reads as they are duplicates of a single pass.

If we take a simple example.
Imagine a race with two runners Runner 1 and Runner 2.

Runner 1 and Runner 2 start at the same time 01:00:00 by both passing a matt

Runner 1 finishes across the matt first at 01:30:00
Runner 2 finishes second at 01:35:00

The timing box picks up all following times
ID RFIDtag Time
1 Runner1 01:00:00
2 Runner1 01:00:07
10 Runner2 01:00:00
11 Runner2 01:00:05
15 Runner1 01:30:00
16 Runner1 01:30:10
17 Runner2 01:35:00
18 Runner2 01:35:01
19 Runner2 01:35:02

The first step would be to sort these by the Runner

So that we get
MyTable (sorted first by RFIDtag then by time.
RFIDtag Time
Runner1 01:00:00
Runner1 01:00:07
Runner1 01:30:00
Runner1 01:30:10
Runner2 01:00:00
Runner2 01:00:05
Runner2 01:35:00
Runner2 01:35:01
Runner2 01:35:0

We then need to somehow programmatically or via a query identify times that are relevant against times that are not relevant. So how is this done? Well the trick here is to algebraically model this in SQL using alias tables. You compare the table above against itself and only select for those times where the runner is the same. Here we use a 20 second window or gate and ID field is added back into MyTable (ID field MUST be automatically incremented and unique ie a primary key)

SELECT T1.*
FROM MyTable T1
WHERE EXISTS
(SELECT T2.*
FROM MyTable T2
WHERE T2.RFIDtag = T1.RFIDtag
AND T2.ID < T1.ID
AND T2.Time <= T1.Time
AND T2.Time >= T1.Time - TimeSerial(0, 0, 20));

In my database application I create a variable for the 20 number which in this example equates to 20 seconds and I have made it easy to alter this period through a simple user interface. I call this period the GATE and it means that for a given race an appropriate gate can be set. In this way the application easily copes with lapped races.

A really beautiful and simple pattern which must be used very widely.

Posted in All, Applied Mathematics, MS Access, SQL MS Access | Leave a comment