Introducing Databases and Web Applications
When Excel files store critical data or business process logic, they need to be treated with care. Likewise, the loss of a document, data corruption, or data entry errors could all be extremely damaging. From my experience, corporations typically do not have thorough processes in place to protect data stored in Excel.
In contrast, web application and database technologies were designed to scale to heavy usage. The web application contains the code, including business process logic, while the database stores the data. The separation of data and business process logic is essential for:
- Performance – databases can use indexes to rapidly access information that is stored within extremely large datasets. Also, databases are stored centrally and can send small amounts of data at a time as opposed to Excel which often requires transfer of the entire large file.
- Backups & Rollouts – sure, Excel files can be easily backed up. However, changes in business logic can be difficult to roll out due to the code being packaged within the same file as the data. Developers of web applications use Source Control Software to store individual revisions of code and most database packages can be backed up even while in use. This separation makes it easier and more flexible to roll out business process changes.
- Reduction of Redundant Data – databases are designed to benormalized with the intent of reducing redundant data.
- Reduction of Data Entry Errors – a normalized database also helps to enforce data integrity. In other words, lookup-type information (department names, branches, etc.) only needs to be entered once, which means it will not be repeatedly (mis)typed. Additionally, the web application can have logic to validate manually entered data before it is ever stored to the database.
- Collaboration – web applications enable users to access data from anywhere with an Internet connection. Similarly, because databases are stored centrally, there is no transfer of individual files back and forth between users as in Excel. This means the chances of data being lost due to confusion about which file version to use are eliminated. It also means there are fewer bottlenecks in work transfer due to “handing off” the file to the next person who needs it.
Conclusion
The key to avoiding maintenance headaches with Microsoft Excel is to understand its limitations. It is a great tool for displaying manageable sets of data if only one user requires access to it. However, once the scope of data or business logic included in an Excel spreadsheet becomes significant, I recommend switching to the implementation of a web application and a database. Once the custom software application is completed, rapid growth of the application’s use will become a small concern and, ultimately, employees will become more efficient while experiencing less pain.
