Au revoir, spreadsheets
You want to migrate your business data from Excel or Google Sheets into a brand, spanking new database. You’re unsure how to do it, so what do you need to do?
Whilst in a spreadsheet, your data is optimised for well... spreadsheets. Spreadsheets make data easily viewable and amendable, without requiring any deep technical knowledge or code.
Databases aren’t concerned with making your data easily viewable or editable: the work of a database is to maintain and store data efficiently and accurately. Therefore, database design requires technical know-how, and you’ll need to work with a database developer to get it right.
You’ll have to do some pre-migration analysis so moving your data is as smooth as possible, and you get all the benefits of your investment.
This should go without saying – but I’m going to saying it anyway! Make sure your spreadsheets are backed up and continue to be backed up during the transition.
Okay, let’s start:
1. Think about your "entities"
What is an entity? Simply put it is the 'thing' you are recording in your data. An example of an entity might be a customer, an invoice, or appointment.
Make a list of all the things you're maintaining in your spreadsheets: customers, orders, purchases, suppliers etc. You should also detail the key “attributes” of each entity. An attribute is something that describes a particular aspect of an entity. A customer may have attributes such as company name, contact person, address, email address, phone number.
Attributes may be shared between entities. For example, a customer number might be shared with your order entity and the order number might be shared with your customer entity. You use this to track orders by customer, or customer by orders.
2. Look for duplication
Now look closely at your data. Are you duplicating information either in the same worksheet or in other worksheets? One clue here is if you routinely find yourself changing information in multiple locations to keep your information consistent and synchronised.
If this is true, there may be opportunities in tackling what is called data redundancy. Redundant data is problematic because it introduces ambiguity to your data. Say you record a customer phone number in two locations and accidentally forget to update it in both locations. Next time you look it up, perhaps months later, you have two phone numbers - which one is correct?
In databases it is generally better to have one representation or “source of truth”.
3. Check your rules
Now look at how you maintain your spreadsheet data. What rules are you applying to your data? Say you want to ensure that every customer has a unique email address. In a large spreadsheet, by default you can easily duplicate email addresses and be none the wiser, until you look for duplicates.
With databases though, you can immediately raise an error if an existing customer already uses the same email. The duplicate simply won’t be saved.
You may have other such rules in your spreadsheet, so it’s good time to note these, and consider new or enhanced rules.
4. Simplify
Consider the data in your spreadsheet some more. Can you spot room for simplification? Are you maintaining data you’ll never use? Do you really need all those columns - or do you find that most of them are in fact blank or ignored?
Secondly, are you manually maintaining values in columns which could be automated? At this stage you may be unclear exactly what could be done but making a note will help your developer.
5. Check external links
Some spreadsheets are linked to other databases and even online services. If you have a spreadsheet where you must occasionally click 'Refresh' to get updated data, this is likely to be the case.
You should tell your developer about this, and if you can, provide details of where this data comes from. It will need to be integrated into your database solution.
6. Reports and charts
If your spreadsheet contains reports or charts, you should discuss how you will get these reports and visualisations after migration. This is also a good time to consider updates or enhancements, and how you want them presented. (There may yet be a role for spreadsheets working with your new database!)
7. Think about the past
If you have an archive of spreadsheets (perhaps you create fresh ones every year) you may want to have a look through them too.
A key advantage of a database is that you can finally consolidate all your information into a single repository of your business data. A developer will be able to migrate not just your current working data, but also historical data into your new database. This historical data then becomes as easy to view and work with as your current data.
8. Pick your technology
If you are technically inclined, (or just vaguely interested!) you may also want to take the opportunity to consider a suitable database platform from the myriad available.
Here at Crucis we're specialists with Microsoft technologies so our database of choice is SQL Server.
MySQL is a popular open-source database and for small businesses is certainly not something to be overlooked, because it is free to use. However, it lacks some of the powerful integrations to Azure (Microsoft's cloud platform), Microsoft 365 and Office 365. This is not generally a significant issue for most users, however.
I also want to mention SQLite. Open-source (and free), it is only suitable for very small database applications and should only be used for non-critical, low concurrency use cases where just one or two users will be connecting at a time.
9. Where to host?
Just as you might do for a website, consider where you're going to host your database. You can use your own database server, a hosting service, or the cloud.
Depending upon your budget, volume of data, data growth rate, and usage pattern there are pros and cons to all these options.
10. Backup strategy
Even databases need to be backed up. Talk to your developer about this!
That’s it! Follow this process step-by-step, fully engage with your developer, discuss your ideas and concerns in as much detail as you can, and there’s no reason why your data migration should not be a total success.
A version of this article first appeared in The Business Bulletin, with the title Say goodbye to your spreadsheets.
ASP.Net
BlogEngine.NET
Azure DevOps