Saturday 20 May 2023

25 Years of Late Nights and Learning

In the late 1990s my martial arts coach asked me to help keep track of the gym's members as his current way of doing it with a pen and paper was getting complicated as his business grew.

As I had been programming since I was 13 or 14, and had just left school and had begun working as a trainee programmer, I started developing a system to do it. 

Version 1 

It was first designed for Windows 3.1, written in VB5 and stored the data in a text file, one row for each person and all their details. 

The original name for it was Visual Gym Manager (partly because it was written in Visual Basic).

Version 2

Towards the end of the 90s it had progressed. It was now a VB6 WinForms app and I had migrated the backend to Microsoft Access, with a relational table structure. 

It was around this time too that I renamed the product to be Arnold - not directly because of the famous body-builder actor, but rather because that was one of the server names at the company I used to work at, and I thought it sounded cool as a name for software too. 

Then, in 2002 came the big launch of .Net! 

Version 3

Given that I had never gone to university, and was almost entirely self-taught … I went out and spent $2000 on Visual Basic .Net (remember those days? buying a box of CDs) .. I also bought a “teach yourself VB.Net” book and got to work re-writing the entire front end in VB.Net. 

It remained relatively stable in this format for a number of years. Regardless of where I was living (Sydney or Rio de Janeiro), I would use my spare time away from work (which was mainly bouncing at this time) and jiu-jitsu training developing the software. 

Version 3.5

When I returned to Sydney full-time in 2006, and resumed working 40 hours a week as an IT professional, I gained more exposure to SQL Server (which I had first touched whilst working in Rio). So it was in circa-2012 that I migrated the backend from Access to SQL Server. The front end still remained a WinForms VB.Net app but the backend was now using SQL Server Express. 

This was obviously a step up but I still had the problem of having to install it on location at Boxing Works’ reception PC and at my own gym’s premises, and also at a 3rd location that I had picked up. 

And everytime I made a change, I had to roll out the executables and go physically to each gym and do SQL. 

Version 3.6

The next logical step then was to move the database to an online server, so I did that. And this made doing back end support a lot simpler … but I still had the problem of installing the front end on each PC which became a nightmare, especially as I had now picked up clients in New Zealand and Darwin!

Anyone know the pain of DLL Hell ???

Different versions of Windows, and time zones to support … not to mention I was working 40 hours a week programming, and teaching my own jiu-jitsu classes … I didn’t have the time to do the support. 

Version 4 

There was only one logical step to do if I wanted to keep my software alive - make it a web app. But I had never written a web application before. Up until now I had only written millions of lines of Visual Basic for windows, and become very good at SQL Server. 

So I googled how to write a web application in Visual Basic, and found some tutorials on ASP WebForms. Keeping it in VB allowed me to use most of the .Net Classes and libraries I’d already coded and so I just had to redo the screens, which was still a lot to learn. 

But, after a few months of late nights I got it done and working. It looked really ugly … but it worked! 

Version 4.5 

It was now 2020 and I had become a Microsoft Certified Expert in SQL Server (I somehow found time to study too) and the software was working … but I wanted to integrate with a 3rd party who could do the direct debits in a PCI compliant way. I spent hours going through their documentation but couldn’t figure it out. I remember speaking to their support guy and I said something like “I’m a SQL Server developer and I don’t know how to do web programming and your documentation sucks” … his reply was “well mate, you’re trying to do web development so go and learn it!” … it was the blunt truth. 

So I phoned an old colleague I used to work with and paid him some cash to help me with the integration. He got that working, and I mentioned how the thing looked ugly, and he said “oh that’s easy, just find a bootstrap template and use that”. He was right. I’d never heard of bootstrap until then … so another $20 for an online course which I watched on a trip to the city and back (I was working as a DBA at PwC at Barangaroo by this stage) and I got the hang of it … and yeah, it now looked presentable! 

Version 4.6 

Just after I got it looking better, the webhost where I had it hosted suffered a massive DDOS attack which took the application offline for almost 2 days. And as much I like supporting small businesses (I still use the same company for my sales websites), I lost 2 clients as a result of this downtime and so it was time to scale. 

Today both the web application and the sql database are hosted on Microsoft Azure. 

Version 5

The shiny “new” version 4 did really well. But … there is a reason that ASP WebForms applications are almost extinct. The whole postback thing where the entire page gets reloaded almost every time gets annoying, especially if the user navigates via the browser buttons which then can cause multiple actions of the same thing happening … 

I knew what I had to do. I’d been putting it off long enough … rewrite it as an MVC App. 

It’s taken me the best part of 6 months worth of late nights and weekends, but I’ve finally finished converting 90% of the code from Visual Basic Webforms to C# and Javascript in a new .Net Core 6 web application. I’ve had to learn so much more for this too (authentication and authorisation for example) that it has been a long road, but very rewarding. In fact 3 years ago I’d never written a line of javascript in my life, and now I have this application which is probably 25% js. 

There is still some VB code there, the nightly jobs and things like that which don’t involve user interaction will stay for a while. But the user experience is so much nicer and faster. 


The main thing I am super proud of is the underlying database structure. It’s remained almost unchanged from the original design I did in Access. With one click of the button, the business user can enrol the member into the class, and it verifies their payments are up to date and calculates all other stats like classes for grading and sale expiry etc. 

It’s proof that if you build the engine of the system correctly, then everything else is just window dressing. It’s like the chassis of a car … do a google search for "cars with the same chassis" and you'd be amazed at how many different cars actually use the same basic blocks! 

I don’t know how to do a lot of things … but one thing I know how to do is build a database. One that’s fast and works. 


Coming Up Next 

It’s now time to go back to market and focus on getting some new subscribers, and in the meantime my development time will be returning the Apps … yes, I also wrote an Android and iOS app for mobile phones too which allows members of the gyms to check in and receive push notifications of alerts. But it’s clunky and also needs a re-write. Will need to find a new course to take and start the process again. 

It’s been 25 years of development, and I reckon I’ll do another 25. 

I’m super proud of this product and I honestly couldn’t run my gym without it. 

If you know someone who runs a martial arts, dance or yoga studio .. or even a small PT style gym, and needs some simple software to help run their business, please do me a favour and share the link with them. My software is free for businesses with less than 25 members and I know it’ll make a difference. 

Thank you.

Rodney Ellis

ps. Link to trial can be found here: https://gymdatabase.co

Tuesday 18 October 2022

Infographic of N-Tiered Application

 I was recently meeting with a client who needed to replace their multi-worksheet-excel workbook (that they're using as a database) with a real world database. 

Of course they wanted to not just replace the main worksheet (with complicated formulas and fancy charts) with a web-based application, but they then asked about apps for phones and tables. 

In order to explain to them how I would replace this Excel workbook with a real system, I went to google and tried to find a decent looking infographic of  a 3-tiered or n-tiered application design. 

Unfortunately, there were no real good ones. They looked like they were done by computer programmers, not graphical designers! 

Therefore ... I sourced a designer to create one! 

It's not too fancy, but I think it does a good job of explaining the basics of linking together a cloud-based database, with an API and then as many different presentation layers as possible. 

What do you think?

Monday 10 January 2022

Documenting the System with Database Diagrams

As a database consultant, it is not uncommon for me to walk into a new business and be handed a task of untangling some "spaghetti".

What do I mean by that? Well, when I used the term spaghetti in the context of my IT work, I refer to a database that is such a mess that it’s impossible to work out the relationships. And this is especially true as most of my clients never created foreign keys within the database and so the SQL Server Management Studio (SSMS) Database Diagram designer can’t map it out.

This is where a tool such as Quick Database Diagrams comes in. This online tool (available with a free option), allows you to map out and document the database as you go. This is something that I find really helps me to learn a new system quickly, as I am very visual and seeing the database relationships at a high level really helps.

There are a few of these free tools available (see this link here for a review on some of them, including QuickDBD -  https://chartio.com/learn/databases/7-free-database-diagramming-tools-for-busy-data-folks/ ) … but what I find makes QuickDBD the best one is that I can directly copy & paste the table fields from SQL Server into QuickDBD

With some of the other tools mentioned there, I had to do it by copying and pasting each field by field … and since tables can have hundreds of fields (and yes, I’ve seen that plenty of times unfortunately) … copying and pasting it one by one can be extremely time consuming!

Here is how to do it:

(Note: for this demo I am using the Northwind database which can be downloaded from here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases)

Firstly, go to https://app.quickdatabasediagrams.com/  and begin your first diagram.

On the left-hand side there is the schema as a text editor, and the map is on the right-hand side. If you’re experienced with databases (and I assume you are to be reading this article of mine in the first place 😊) then you would understand how the text schema creates the diagram - the capital PK and FK keywords being short for Primary Key and Foreign Key respectively. 

So you can type them in yourself, or …. 
    • Open SSMS and go into the database that you would like to map

    • Type the following into a New Query Editor:
USE Northwind
GO
sp_help Employees

 This will produce a result in the bottom tab. What you need to do now is grab the following section (two columns only):



And then copy/paste that into QuickDBD here like this:



You’ll see the red Xs. This is just because in SSMS there is a Tab character there and you need to replace it with a Space. You should now have this:


No go ahead and run sp_help on these two tables: 

sp_help Territories

sp_help EmployeeTerritories

Then like you did above, copy/paste those two columns (name and type) into QuickDBD. And again replace the Tabs with Spaces so that you have this: 


Finally, we need to do the relationships. 

In the sp_help results, you would have a list of Keys that will help you do this. For our demo we are only going to map these three tables (to save typing) but obviously you can replicate this for all foreign keys that this table relates to. 

Firstly, add the characters PK against each of the Primary Keys. If you have done this correctly, a little image of a Key will appear next to them in the diagram. 

And now to add the relationship, add in the FK characters, followed by a >- and then the target field. If you’ve done it correctly, then you should end up with the following: 



That’s it … you can just keep going now copying and pasting the fields from SSMS using the sp_help stored procedure and QuickDBD will do the rest for you!

Please reach out to me if you need any help!

Rodney 

www.rodneyellis.com.au 









Tuesday 21 September 2021

What am I paying you for?

Once upon a time ... 

I had a job as a DBA. It was your typical Monday to Friday, business hours gig. 

When I started there, the system was a mess. Constantly going down, slow, no decent backup/restores happening, nor indexing, check DBs etc ... 

After a few months, I got it all under control, and even scored a payrise ... the kind where the boss calls you in and just tells you you're getting a raise because your efforts have been noticed. 

Fast forward a few months ...

The boss comes down to the IT room (on the ground floor, of course ... how stereotypical) ... 

I'm standing around doing nothing, except shooting hoops with the toy basketball hoop. 

He looks at me and says "what am I paying you for?" 

I look at the SQL CPU .. it's low. Backups worked, everything is humming. Did he want me to break something just so that I had something to do. 

That was when I really came to the realisation that DBA work was not for me. I did my job perfectly, but still had to turn up every Monday at 9am to do nothing!

Saturday 10 July 2021

Import Excel into SQL Server


Currently a Covid Lockdown in Sydney and so I'm bored. Therefore, I decided I'd share some knowledge from all the years of ETL work I've done. 

In this video I show a simple way to get around an annoying "Microsoftism" when doing what should be a straightforward import of data from Microsoft Excel in SQL Server. 

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

If you've ever tried using the SQL Server Import and Export Wizard, and received that error, you'll know how frustrating it is ... 

Here's the solution: 


Tuesday 1 June 2021

Can you tell your customers if you moved premises?


In the building next to us is a mechanic. He used to be in a different factory unit, but due to that owner selling, he was forced to move into a less desirable factory out the back. 

When I asked him why he chose this factory to rent, and not one with more exposure down the road, he said it was because all his clients knew that he was in this block of units. 


It still amazes me, and will until the day I drop dead, that people don’t keep a database of their customers.


I know my mechanic has never emailed me. If he moved, I wouldn’t know where to find him! He would lose my family as customers and have to find new ones.


If your business has all its clients’ information stored, even just the basics, then you can email or ring them when you have important changes - like changing address! 


You probably worked really, really hard to build your clientele. Don’t leave them behind when you move. 


Build a database … or better yet, contact me and ask me to build it for you!


Friday 21 May 2021

Excel <> Database!

Please STOP using Excel for managing your customer data. 

Excel has its place, but that place is not being a surrogate database! 


Last week we took on a new client in Cronulla (a small community based sporting club) who had been managing their attendances with spreadsheets. The poor girl whose job it is to work out who's coming and who isn't was struggling big time. 


We were able to take that data and import it into our Arnold Gym Management System, and now her workload has been trimmed by over 90%! 

Help us rid the world of Excel Spreadsheets being used as databases!