Computers of
South Dublin
Wellington Lane
Ballsbridge, Dublin 4
phone: 087 917 1580
email:
White Paper — Why is a MySQL database design better than Excel for sharing data?
Late last year I was approached in Dublin about converting a complicated financial spreadsheet into a web-based application that would reproduce the data entry forms and formulae of the spreadsheet, allowing each logged-in user to modify only data that they “owned.” I responded with a proposal to implement this bespoke web design using the PHP programming language for the visual interface and mathematical calculations, with the “cell” data stored as tables in the MySQL database.
The project investors, being cautious, were interested in a layman's explanation of why a database implementation (and why MySQL in particular) would be desirable when it seemed like their current implementation with Excel should have been sufficient. The answer to this question became an explanation of why distributed databases are necessary, particularly as the single-source data model fails to grow across a network.
My response is presented, unedited, as the following white paper.
Robert Phair, Director
Computers of South Dublin
Why is using a database better than Excel?
Excel was developed to allow a single computer user to maintain data in one file at a time on one computer. It has no built-in ability to share its data with other computers or programs. In order for Excel data to be modified, a computer operator would have to be continuously present at the computer where the spreadsheet file was accessible. Using a database for storage eliminates this problem because data in the database can be modified automatically (by programs) as well as manually.
Ensuring that the Excel file is accessible across a computer network, in order to eliminate the dependence upon a single computer, does not provide a complete solution. For instance, any time a spreadsheet file is opened over the network, the file appears “locked“ with respect to all other computers to avoid possible conflicts or data loss due to concurrent changes. A database implementation, since all pieces of data can be modified independently, does not have this limitation.
Database systems containing the acronym SQL are designed for accessibility over a network, so a database set up on an Internet-accessible machine could be accessed and modified by any machine on the Internet with the correct access information (i.e., the server password). Systems exist to provide interaction between Excel files and network-based applications, but they are expensive, inherently unreliable, and notoriously difficult to implement… as opposed to a system like MySQL, which is only a single software component containing all database and network features.
Finally, databases have capabilities that do not exist in Excel: for instance, they provide rapid search capabilities through large amounts of data, returning records that meet potentially any criteria, and can create new databases (such as summaries) from the contents of existing databases, simplifiying the generation of human-readable queries and reports.
Why is MySQL the best database software to use?
The two most popular choices for SQL implementations are Microsoft SQL Server and MySQL. Microsoft SQL Server is only supported and improved upon by a single company, while MySQL was developed by a consortium of software developers and is guided in its ongoing development by both industry requirements and compliance with open standards.
This results in MySQL being an overwhelming favourite for application developers, with the following benefits:
- MySQL is provided free on all UNIX web servers and is intelligently integrated with other tools, such as Perl and PHP (also free), that can represent its data in a web-based environment.
- MySQL therefore involves no overhead in installation time, and very little ongoing maintenance time relative to Microsoft SQL Server.
- MySQL is simple to program, having both the best online documentation of any available database system and also discussion forums where developers can obtain quick solutions to problems.
- MySQL is the most reliable database, both in the author's experience and as evidenced in its preferred use by Google, Yahoo, NASA, and much of the worldwide financial community.
- MySQL is available free of charge (only the Enterprise version requires the payment of licensing fees).
If you can see how an Internet database application would enhance your enterprise, please contact COSD about your requirement to refine your idea and begin creating a plan for its implementation.
Please read terms of use
© 2008 Robert Phair
Wellington Lane
Ballsbridge, Dublin 4
IRELAND
phone: 087 917 1580
office: 01 669 7023
email: