Most organisations recognise at some point that they need to store data. Often data storage starts off as an ad-hoc arrangement in a spreadsheet, until problems start to emerge with data integrity. Some people with enough skill may then try to move the data into a more structured system like Microsoft Access, however as an organisation grows, this too has limitations, especially in multi-user scenarios, and the need to step up to a database server like SQL Server or mySQL may be necessary. Finally, as the need to access data anywhere, anytime becomes critical, data needs to migrate to the cloud. In this article, I'll look at the progression of data management from ad-hoc to the cloud.
In the Beginning - Excel
At some point, usually fairly early on in an organisation's life cycle, there's a recognition that data needs to be stored in an orderly way. Most people know a bit about Microsoft Excel, and it's tabular format provides a quick and easy way to store simple records like a customer list, or payments and so on. The great strength of Excel is its extreme flexibility, and low barrier to entry. It seems wonderful to be able to have a list of data at your fingertips, and this seems great to begin with.
Excel's strengths are also its weaknesses. It doesn't do structured data, and won't complain it you store text in a date field or a date in a currrency field. It's also not designed for multiple users editing the same document simultaneously. Excel can also very quickly get repetitive entering data. If you want to enter multiple invoices to the same person, you've got to enter their details each time, rather than just looking them up. In terms of backup, it's only as good as your overall backup strategy. If you use something like Dropbox, Google Drive, or Microsoft One Drive to store your Excel files, at least you have a backup in the cloud, that can also help provide multiple user access to files, although it doesn't solve simultaneous editing by multiple users. Backup depends very much on individual users storing the files in a location where they'll be backed up.
Stepping Up - Microsoft Access
Microsoft Access is something of the ugly duckling in the Microsoft Office suite. It doesn't get the love that Word, Excel and Powerpoint get, with beginners often finding it hard to get their heads around, and IT departments disliking it due to the risk of users creating ad-hoc databases without proper management. Ironically, the reason IT departments dislike Access, is the same reason Excel is not a great choice for data storage in terms of risks of data not being backed up, or poor multi-user editing, yet Access can do both of these far better than Excel. The key point about Access is that it is a relational database management system, albeit one with some fairly significant limitations. The relational bit is quite cool, as it means you can have a table of customers, and a table of invoices for example, and when you enter a new invoice, instead of entering all the customer details each time, the database can simply look them up from the related customer table.
Access is designed for data integrity, so if you say a column has to contain dates, then it won't let you put anything else in that column. It also has nice form and report building functionality, so you can make data entry attractive and intuitive, especially when a tabular format might not be the best way to enter data. Reporting allows you to generate nicely formatted things like labels or invoices for example, from data stored in your database.
Though not strong on multi-user management, Access does allow limited numbers of multiple users on a network to access the same data simultaneously, and because it's records based rather than file based, several users can edit different records in the same database simultaneously, whereas with Excel this doesn't work well.
Where Access falls down, is although it allows multi-user editing, it doesn't allow multi-user management so anyone who can access a database can edit anything in it. As an organisation grows, you may want to restrict what different users can do with different data, possibly giving some read-only access to some data, and full editing access to other data. With Access, data access is all or nothing. In order to be able to interact with Access data, you need to be able to access the access data files on disk, and a malicious user could delete the whole database, (just as they also could do with Excel).
Big Brother - SQL Server
Microsoft Access has a big brother called SQL Server. SQL server can create tables just like Access, but at first glance it seems a bit unfriendly. Partly that's because it's intended as a tool that will generally be managed by IT professionals. It doesn't come with any tools to build pretty data entry forms, and it takes some technical knowledge to properly install and configure. The strength of SQL Server is that you can control exactly who has access to what, you can set up automated backups, and you can prevent end users messing directly with the database files. In the old days, SQL Server was really expensive, but Microsoft offers a variety of editions, with SQL Express being free. SQL Server Express has a limit of 10GB per database, but Microsoft Access has a limit of only 2GB, so anything that will run on Access will run perfectly happily on SQL Server Express. If you really need more capability than what SQL Server Express offers, you'll probably have to bite the bullet and spend some serious money, but if you have that much data, then you can probably afford it anyway. If the price ticket is scary, there are other free alternative like mySQL that offer some similar functionality for free. MySQL is free, and has no database size limits, but it doesn't have all the features of SQL Server either, so it pays to evaluate whether 'free' is actually better.
SQL Server and Access actually work quite nicely together, as Access doesn't care where it gets its data from. It can be from a text file, an Excel spreadsheet, or SQL Server. By using Access to build the data entry forms and reports, but let SQL Server do the heavy lifting, you get the best of both worlds, a nice easy user interface to interact with data, and a powerful server database that supports large numbers of simultaneous users while preventing them from doing anything they shouldn't.
The big drawback with SQL Server is that you need a server computer to install it on, and access to data will typically only available to people on site.
The Cloud
Unless you've been living in a cave, you've probably heard of the cloud. Basically it's a cool marketing term for the internet, since many people tend to think the internet is just the world wide web. In a sense you could say the web was the first mass cloud app, although email probably preceded it. The thing is, you can put anything in the cloud, databases included. Behind pretty much every good website these days is some sort of database. The widely used Wordpress blogging platform uses mySQL as its database. Of course how you'd structure your data for a blog is completely different to how you'd structure it for something like financial data, which is why Wordpress isn't necessarily the best choice for every web based solution if a blog like structure isn't at least part of what you need.
SQL Azure - SQL Server for the Cloud
Microsoft has been busy in the cloud space too, and they now offer something called SQL Azure. It's basically like SQL Server, but in the cloud, with automated backups. It's actually not all that expensive either. Their entry level offering for 'development and testing', is more than adequate to run anything that would run in Microsoft Access, or modest SQL Express databases, but with automated backup included, and the data in the cloud, all for under $10 NZD per month. One thing to be aware of is that you pay per database, rather than per server with SQL Azure databases, unless you go for a much bigger, more expensive option, however if you only have one main database you want to move to the cloud, it can easily be done at modest cost. One of the nice things is SQL Azure is based on SQL Server, so just as Access can be made to talk to SQL Server, it can also be made to talk to SQL Azure, so end users don't need to change the way they interact with the data.
mySQL - Cloud Databases for free - or almost
If you pay for web hosting for your website, the chances are it will include mySQL support. As already mentioned, Many CMSs including the popular Wordpress use mySQL, and frequently the web hosting plan will allow you to create multiple databases, so it's quite possible you can create a database to store organisational data in the cloud, without paying anything more to host it than you already do for web hosting. MySQL doesn't have all the capabilities of SQL Server, so sometimes it can be worth paying that bit extra for SQL Azure, but much of the time, mySQL is more than adequate. Some CMSs have a specfic focus on building cloud based databases which simplify the process of moving your business processes to the cloud. Of course, you'll probably need to hire a developer to provide a way to interact with the data, and this can cost a substantial amount, but with a growing organisation, the convenience of having access to data anywhere can be worth it.
Putting it all together
Starting off in business or any organisation with your data in a few text or Excel files, as you grow, you'll typically find that you need to develop better, more consistent data management stratagies. Fortunately there's a path all the way from simple single user spreadsheets, all the way up to complex multi-user cloud based relational database systems. Sometimes off-the-shelf systems like cloud accounting package Xero can meet needs for common business processes, but it's amazing how many processes unique to individual businesses there are, and all of these need some sort of reliable data management system. I've been working with database systems now for over 20 years, and I've seen a lot of changes in computing over that time, but the basic requirement to store well structured data hasn't changed, just there are more tools available, and more opportunities to make data available anywhere. I've worked with all kinds of businesses and non-profit organisations, and it's often quite a learning process for everyone involved as organisations document their internal processes so that they can work towards automation and reliable storage of data.