Advice on Ordering and Managing Software

What is a Database?

Broadly, a database is a collection of data records. Databases are capable of holding and processing very large volumes of data. The database in Facebook holds records for well over a billion users for example.

The core of a database is a table. A table is a bit like a spreadsheet: it contains rows and columns of data. Unlike a spreadsheet, a database column contains a single type of data, perhaps a 6 digit number, a date, or a text field.

Typically a database consists of a set of tables. For example, a company might have a table of products, one of customers and another table of employees.

What are the advantages of databases over spreadsheets?

Firstly, spreadsheets are only suitable for a few thousand records at most. For larger volumes, databases are essential. It simply isn’t feasible to manage and keep track of large volumes of data in a spreadsheet.

Secondly, a spreadsheet is basically a stand alone application. Whilst they are good for some calculations, these calculations are contained within the spreadsheet. Databases do not have to be stand alone, they can be accessed by programs and usually are. This makes all sorts of processing possible. The main advantages of databases are:

  • searching for data is fast and easy

  • processing and calculations can be done

  • data can be displayed better, and viewed and input in forms

  • web sites can use databases

  • data can be grouped into classes and subclasses

  • selection and sorting are easy.

Classes and subclasses

With a database, it is easy to extract data for a class of record. It is also easy to get sub totals for each class of data. Consider the example of a sales table that records sales made by type of product (say hardware, software or accessories) and by salesman. You may want a monthly report of the total sales for each type of product and another report for the totals for each salesman. These figures can be produced by single calls to a database.

Single commands to the database can extract all records for a type of data. For example the web site for a music shop might want to display:

All blues albums

All Pink Floyd albums

All albums by artists beginning with B

Albums whose title contains “holiday”

These can be done directly

In a large system, there can be a hierarchy of classes. You can see this in web sites like Amazon, Argos or DIY shops.

Combined data

Often data is needed from more than one table. Consider an invoicing program. An invoice needs data for:

  • a customer

  • an order

  • several products

A database engine can extract all this information with a single command.

In conclusion: using databases

Most business applications are built around databases. Databases provide ways to store data and this data can be analysed over time to provide valuable information about trends in a business.

About the author

John McMillan is a software developer based on the Essex Suffolk border in England, close to Cambridge and North East London. He carries out computer programming services to provide bespoke solutions to business needs. He has written software for a wide range of clients from the small SMEs up to blue chip companies.

Would you like to receive John’s occasional newsletter?

Click here to sign up for the newsletter.

Have Companies Become too Reluctant to use Bespoke Software?

I was recently chatting to Colm Coyle of RoIT about bespoke software and the way in which few companies now use bespoke software. I made the point that companies largely stopped commissioning bespoke software after the year 2000. We came to the conclusion that it is time they restarted.

The year 2000 forced many computer departments to do what they should have done years before – ditch their mainframes and install networks of PCs. Suddenly, when staff needed new reports, instead of having to incur weeks of Cobol programming, they could create a simple solution on a spread sheet. This has transformed the use of IT but has the process now gone too far? Colm and I think it has.

Competitive advantage

Firstly, spreadsheets are a generic tool. They can provide basic reporting but do have major limitations. Applications built around spreadsheets are rarely easy to use.

To be successful, an organisation needs a competitive advantage. It needs to do something differently and better. This will be constrained by off the shelf software. There are many opportunities from creative use of processes and customer interfacing. Organisations do not become great by following everybody else.

Spreadsheets will normally be the best way to provide a one-off solution but they are not convenient. They are also very error prone (see below). However, for any application that has to be run regularly they are inefficient. The staff costs in using a spreadsheet do need to be balanced against the cost of bespoke software. In the long term, a bespoke solution will almost always be better value.

Spreadsheets are error prone

A year or so ago, I advised a software company who are spreadsheet experts. It was interesting to say the least. Studies from academics and the European Spreadsheets Risks Interest Group (http://www.eusprig.org/) have found that most spreadsheets contain at least one error. Problems occur when rows are inserted or deleted and formulae are not adjusted properly. How do you check the results of a spreadsheet calculation? One man told me he checks by adding up the column with a calculator. You have to wonder about the point.

It’s become economic

One thing that emerged in the run up to the year 2000 was that software had a much longer life than was realised – we discovered that an awful lot of software had its origins in the 1970s. The world of IT has changed out of recognition since then. Development costs have plummeted but I don’t think some IT managers have understood this.

In the 1970s I had to add some fields to a database and change the length of the main key field. It took a team of programmers six months. A similar exercise last year took about 3 hours. In trying to remember why, I realise we didn’t even have databases in those days, never mind SQL. A simple extraction and sort of data required a program. Today it’s one line of SQL. Files were essentially flat, so the length and position of each field had to be specified. Incredibly, programmers rarely employed reusable code. Object orientation did not exist.

Legacy systems often had their origins in this environment which is why, even in 1999, it would cost thousands just to generate a report.

These are not the only reasons why development is cheaper. Modern programmers use integrated design environments (IDEs) which speed up the development process. Object orientated techniques have slashed debugging times. And programmers’ wages have not kept pace with inflation.

Software that does exactly what a user wants is now affordable. In fact, I’m sure it can often be provided with less effort that a spreadsheet solution.