FB

So, I’ve decided to create a web-based application. I’m announcing it on Database Star for a few reasons. Read on to find out what it is and why I’m doing it.

What Am I Creating?

Yes, I’m creating a web app.

I’m creating an online reporting tool for an organisation that a friend is part of. The tool will allow my friend to log in and view data in a report format.

It’s not a full application in the traditional sense – it’s a pretty simple tool – but it’s a web-based tool nonetheless.

The tool will allow users to log in, select a pre-defined data report, select the parameters, and generate the report from the database. They will also be able to upload a data file in a specific format that their organisation produces.

 

Why Am I Creating This?

Why would I spend my time doing this, when I have a full-time job and a website to run?

Well, I’m creating this web-based tool for several reasons.

Helping Out a Friend

I’m doing this to help out a friend. They have a desire to see the data they generate in a different way to help their organisation run better.

Improve the Organisation

Another reason why I’m doing it is so that I can help out the organisation who will use the tool. I’m a big fan of making things easier for people with the use of software, and it’s one of the reasons I got into IT in the first place. Using this tool will make life easier for the people in the organisation.

Practice and Enhance my Development Skills

I’m also using it as a learning exercise. It’s been a while since I’ve done any web development like this, so I’m using it as an opportunity to learn and develop my skills.

Article Ideas and Tips for You

Another reason I’m doing it is so that I can provide more articles and tips to you, the reader, on this site. I plan on writing articles about the issues I face and the things I discover when developing this tool, in the hope that it will benefit you.

 

What Are The Technical Details?

I’m building it using PHP and MySQL.

Why PHP? Well, it’s the web-development language that I’m most familiar with. As I said earlier, it’s been a while since I’ve done web development and I didn’t learn .NET in university.

Also, the web server that I have has PHP and MySQL installed already, so I won’t need to configure my own server.

I’m also using a pre-built framework to handle all of the basics, such as page navigation and database access. I don’t want to have to build that from scratch, and there are many PHP libraries around these days for that.

So, as I mentioned in a recent post, I’m creating a web-based reporting tool. I’m building in PHP and basing it on the CodeIgniter framework.

I thought I’d list the web development tools that I use in this article, so you can get an idea of what I use.

 

What Tools Am I Using?

I’m using a few tools for creating this web app.

 

PHP Development – Zend Studio

I’ve recently downloaded the trial version of Zend Studio to use for my PHP development, and it will be the main web development tool that I use.

I’ve chosen Zend Studio as my IDE as I’ve heard great reviews about it and found on many Stack Overflow posts that it is one of the preferred web development tools for PHP developers.

Zend

I started doing this work in Notepad++. I was using that for a while, but it got to a point I needed something with more features. I was losing my place in files in Notepad++ and also needed a few advanced features that a text editor couldn’t offer me.

The features I’m most interested in with Zend are:

  • Debugging – it’s a basic IDE feature that you obviously don’t get in Zend
  • Publish to Server – much easier with an IDE than without.
  • Code autocompletion – a massive time saver

Publishing to a server at the moment involves using FileZilla to upload files to my webserver. Ideally, I would like to use the publish feature within Zend to update files on the server.

I plan on upgrading to the full version of Zend Studio at the end of the trial. I’d just like to make sure that it works for me before I go ahead and purchase it. I’ll still be using Notepad++ for other work, just not for this project.

 

Feature and Bug Management – Trello

I’ve been using Trello to manage the different features and bugs that are to be developed for this solution. I’m using it like a Kanban board and have made different statuses for Backlog all the way up to Release.

It contains features, which are different areas that I want to add to the reporting app, and bugs that are found during the user demonstrations and general use.

TrelloI use Trello at the moment for personal project management, and it’s very good for that. Adding cards and dragging them to different columns is very easy. I can also see easily how many tasks are in each column so I don’t get too overloaded.

We use Jira at my current client site for this purpose, but I don’t need the advanced functionality of Jira, so decided to stick with Trello.

 

Source Code Management – Github

There isn’t really any question here. I use an account I have on Github for source code control.

Github

I upload my changes at the end of each day that I’m working on my code, or at certain intervals, such as after I finish a feature or a bug.

It’s still something I’m learning, as I haven’t used Github a lot before this project. But I’m finding it very useful.

Last week it saved me. My desktop PC had decided to crash and needed a reinstall of Windows to be recovered. Luckily I was using Github so I had a copy of all my source code which I easily downloaded again.

 

SQL Development – MySQL Workbench

The database that is used for this project is MySQL, and as a result, I’m using MySQL Workbench for the SQL work.

MySQL Workbench

There are going to be some pretty complicated queries involved in this project. It’s primarily a reporting tool, so most of the queries involve selecting data from a database.

However, it’s a bit more complex than just running a SELECT statement on a table with an index.

For this project, I’m importing a spreadsheet file in a standard format, which will then import data into a staging table. This data will then go through an ETL process to transform it into several tables which can be queried for each report.

This is done for performance reasons. If I was to query from the standard table, the queries would be even more complicated and take a long time to run. I don’t really want the users having to wait several minutes for the page to load.

So, I’m using MySQL workbench for creating database tables, and writing the SELECT queries before I add them to my code. It’s one of the web development tools that I haven’t use a lot of either.

 

What I’ve Learned So Far

If you’re looking to create some kind of application or a project on the side, these tips can help. The app is only for a small group, and while I’m a software consultant in my day job, I learnt quite a few things from this process that I thought I’d share on this site.

 

1 – Stay Organised

The first thing that I realised when I was working on this project was to stay organised. I’m normally an organised person anyway, so this was easy to do for this project, but it’s still important.

I used a couple of tools to help with this.

First, I needed a way to track the different features and defect fixes that were needed as part of this project. I’m a big fan of Agile after the last few projects I’ve worked on, so I decided to use a Scrum board to track the progress of these.

I decided to use Trello for this. I created “cards” for each feature (and later on, defects) that were needed and moved them from Backlog to In Progress to Testing and then to other stages. This worked quite well for my project.

I’m also a big Evernote user. I kept all of my notes in Evernote for the project, such as requirements notes, design ideas, links to code on other websites.

So, staying organised was a big help for this project.

 

2 – Keep the Client Up to Date

It’s now March 2016, and I’ve gotten the web app to a stage where it can be used. It has a minimal set of features and meets the client’s requirements.

Starting this in mid-2015 means that it took about 8-9 months to build. This can be quite normal for a project of this size and only working on it part-time.

During this period, it’s important to keep the client up to date. I was having the occasional phone call with them, with more phone calls at the start when I was gathering requirements.

I was also sending regular emails to them, updating them on the progress and asking for feedback. In the emails, I would include a few bullet points on what had been done and what was still outstanding.

This was important, I felt, so they knew that progress was being made and I wasn’t wasting their time.

When I was on the call with them, I would also use Skype to provide a screencast of my computer, so they could see the site and we were both speaking about the same thing.

This might be something you do as part of your day job, and it’s just important during side projects.

 

3 – Document the Design

When I was designing the application, I thought of a few different ways I could do it. I had a few ideas on some of the more technical aspects, such as database structures and function design.

Sometimes I would document my thought process, and other times I wouldn’t.

This was especially helpful when I was away from my desk. I would come up with an idea while I was at work, or when I was cooking dinner or something. I would then add it to Evernote for something to consider when I actually sat down and worked on the project.

When I was at the desk and working on it, I would document how I was going to develop it.

I found that when I did document it, it helped when I came back to that same area later. It was easier to resolve defects because I knew what I was trying to do with the design.

 

4 – Take Notes on Requirements

This was one of the most important points during the process.

I realised that the notes that I took on the requirements were some of the most helpful notes that I took.

They allowed me to go back to them at a later time and remember what actually needed to be done.

I took the notes while I was on the phone with the client, and expanded them later when I was off the phone. I wrote down what they wanted, what the business rules were, and how they expected things to be shown.

As it was a reporting tool, there were a lot of notes on the different reports, what was to be shown, the formatting, and so on.

My current role is a business analyst, so this is what I do for my day job, but it’s good to remember that it applies to other areas as well.

 

5 – Use Source Control

I had my GitHub account set up, the project created, and the application installed on my computer.

Years ago, I might not have considered using source control, seeing it as an extra task and having an attitude of “I’ll be OK”.

Now, though, I wouldn’t start a project without it.

This was my first use of the GitHub application and process on a regular basis, and it just made life so much easier.

My PC even died during the project. I got some kind of Windows system error and had to reinstall Windows 8. This took several hours of my time, but luckily I was using GitHub and my source code was all backed up. A simple download has saved me hours of time trying to reassemble the code.

So, if you’re looking to start your own side project, use source control. It’s great. But you probably knew that already.

 

6 – Ask Questions If You’re Stuck

I’m a big fan of asking questions of other people if you’re stuck on something. There are so many other people out there that can help you with problems that you’re having.

One example is Stack Overflow.

Now, there’s a bit of CSS involved in the site I was building. Overall the CSS was pretty simple, but one of the requirements was to have the column headers on a table rotated 90 degrees and be fixed to the top of the window when scrolling.

I’m not a CSS developer. I know the basics but not enough to do something like this.

I used some sample code that I found and got this kind of working, but was still having problems. The scrolling worked, but the rotation just wouldn’t look right in Chrome and IE.

After spending more time than I should have trying to figure this out, I thought, “what about Stack Overflow?”.

I’ve answered a bunch of questions on there before but never asked anything. So, I thought, I’d ask my question on there and see if I could get help.

Within hours, I had a few responses to the question from some CSS experts. I added some missing lines of CSS code into my file, and the page was working.

The point of the story is to ask questions if you’re stuck. Try it yourself first, but don’t waste a lot of time if you don’t quite know how to finish it.

 

7 – Look for Ways to Improve Productivity

Improving the way that I work is something I’m always looking to do.

I brought this approach to this project as well. There were several things that I did to improve the way that I work:

  • Use an IDE instead of Notepad++ as I originally planned, for source code completion, function navigation, and many other time-saving features.
  • Debug issues on localhost instead of the remote server as it’s faster.
  • Develop a “debug mode” flag which can be toggled to enable output, instead of commenting out every “test” output line in my code.

There’s probably a few other features that I developed in the code that took some time up front but paid off in time savings and code quality later on, but they escape me at the moment.

If there are ways that you can improve your productivity, either by using a better tool or improving your code, then try to do it.

 

8 – Don’t Skimp on Quality

There are many reasons to have good quality code – improved maintainability, faster execution, save time with future development.

If it’s your own side project, it can be tempting to skip a little on quality and best practice. Just like this XKCD comic: https://xkcd.com/292/

But, it will be something that will pay dividends later. When you come back to fix a bug or make an improvement, you’ll be glad that you did it the right way.

I won’t detail all of the good coding practices here, but some of the things I noticed myself doing were:

  • Setting intuitive variable names and table names
  • Avoiding many layers of nested loops
  • Using objects where appropriate

So, if you’re developing your own web app or side project, create a high-quality solution. You’ll thank yourself for it later.

 

One Year On

So, how has it gone? Where’s the app at now?

I’d like this article to serve as a kind of “retrospective”. For those unfamiliar with Scrum practices, a retro is a meeting at the end of each iteration where the team discusses three things:

  • What went well
  • What didn’t go well
  • What can be improved

 

What’s The Status Of This App?

I can say that the app is functional, and meets all of the original requirements.

It’s being used every week by people in the organisation that I developed it for.

The friend of mine who I’ve been working with to gather requirements and test features is happy with it and has mentioned a lot of benefits it has given the organisation.

Overall, I’d call it a success.

There are some features that were requested, and some improvements that I’d like to make, that have not been developed. I’d like to get to them eventually, but they’re in the backlog at the moment.

 

What Went Well?

Let’s start with the positives. A few things went well during the first year.

Client satisfaction. Overall, the client was happy with the application and the benefits it provided. It was far better than what they were using currently, and many users were able to access it at once.

Self-service. I build the app so that each week when new data was available, the administrative users can upload the data and run the reports they need, without my involvement. This was something that needed a bit of work up front and a lot of testing, but in the end, it’s been helpful.

Trello. I’m a fan of Trello and I’ve been using it as a personal project management tool for a while. I decided to use it for one of its common uses – as a Scrum board. This had worked really well. I put features and defects onto the boards, and it was easy to keep track of where things were up to. It was also helpful to make sure I wasn’t working on too many things at once.

Github. Using Github as a source code repository was a huge help. It was easy to sync and check in my changes, and even get my code back after my desktop crashed and needed reformatting at one stage.

Zend Studio IDE. Originally, I decided I was going to use Notepad++ as my editing tool, as I didn’t think I’d need the full features of an IDE. But, I decided to use Zend Studio and don’t regret that decision. Using Zend made it a lot easier to browse and edit code, and search for what I needed.

 

What Didn’t Go Well?

A few things didn’t go as well as planned.

Learning the framework. I decided to use the CodeIgniter PHP framework as a base for my application. This was great, as it included a lot of functionality built in. But, I had trouble learning how to use the framework at the start. I eventually picked it up, though. Also, structuring the code using the framework and being aware of case-sensitive issues in filenames caused a lot of issues. “Why isn’t the live version picking up my changes? Oh, because it’s case sensitive and it’s looking at the old file.”

Formatting with CSS. I know a bit of basic CSS but I’m no expert. I needed to add formatting to my report to make column headings have vertically rotated text, and stick to the top of the page when scrolling. This was a real challenge until I asked for some assistance on StackOverflow and got an answer in minutes. There’s a reason Stack Overflow is so popular – it’s amazing. It’s one of the top tools I recommend for developers.

Exporting to PDF. Another technical limitation I had was generating to PDF. There are a few frameworks out there, but nothing was perfect. The most advanced one had performance issues with some of my reports and it took me a long time to resolve.

So, it seems most of the issues I had were technical in nature.

 

What Could Be Improved?

Looking back, I think there are a few things that could have been improved, or that I could have done better during the past year.

Time allocation. Sometimes I don’t feel I allocated enough time to doing work on this project. While I have a day job, this site, and a couple of other clients, it was hard to balance them all and get enough time to work on this project. The more time I spent on it, the more I can get done, which is obvious. So, if I made more time for the project, I could have gotten the work done quicker.

Communication with the client. Sometimes I communicated a lot with the client, especially for new features. Other times, a couple of weeks went by without a message or an email. This probably showed that I was not working on the project – especially when there were some defects that needed fixing. In the future, I’d like to communicate more often to keep the client updated.

Better design at the start to reduce refactoring. A couple of areas of the application needed some redesign as they didn’t quite handle what I needed to be able to do. This is because I didn’t do enough of the design at the start. In one of the most recent features I built (user permissions), I designed this at the start a lot better, and when I built it, it was a lot easier. Changes made to areas designed like this were a lot easier.

Testing. Most of the defects I had with the tool could have been picked up if I did better testing. Most of the defects were data related and came from the ETL process, where the report data didn’t match the file upload. If I did better testing before announcing that changes were made, instead of just uploading the files to the server, a lot of defects could have been avoided. I did end up building a script to test the before and after data, which solved a lot of issues, but more testing would still have been a good idea.

 

What Else Did I Learn?

I’ve mentioned a few things that went well and some that didn’t go well.

What else did I learn from this project? I’ve written a post on this before, but there are a few extra things:

Time management is important. I have had a tendency to say yes to a lot of things, which leaves me with too much to do and not enough time. I’ve enjoyed this project so far, but I need to be better at managing my time and priorities.

Web development is fun. I’ve forgotten how much I enjoyed web development. I haven’t done any for a while, and on many occasions, while developing, I found myself losing track of time. That’s not something that happens very often!

Well-designed code is great to work with. I’m still learning how to properly design and structure code, but seeing examples and reading books on it really makes you appreciate the power and advantages of good design.

 

What’s Next?

So, that’s my experience from the first year of this project.

What do I plan on doing next?

Well, there are a few features that have been requested that I’d like to add. One of which is user management, which is only partially done at the moment.

I’d like to make a test area of the site and make it available for demos, so that other people in other organisations, or the public, can view it without seeing the real organisation’s data.

That’s probably about it. It’s not something I want to monetise anytime soon, so I don’t plan on putting any more development into it other than features that are requested by the client.

So, I hope you found this article valuable and can learn something from my experience creating a web app.

 

Image courtesy of tiramisustudio / FreeDigitalPhotos.net

Get Your SQL Function Cheat Sheet Now: