By Ian MacOlive.
In our previous article, we looked at the spreadsheet in some detail and considered the limitations that they can present when a project gets too large. The upsizing of a spreadsheet to a database is the next logical step.
Since a database management system, or DBMS, contains the data in a series of related tables, the record-keeping and adding is dynamic and fluid. Records are simply added, much the same way as in a spreadsheet. The main difference is that the added records in a DBMS do not interfere with formulae and named ranges.
Databases do, however, have some disadvantages:
Difficulty in construction
Databases require a degree of knowledge and skill, which involves either training or self-study. Since the concepts are somewhat different to that of the spreadsheet, the knowledge required is often time-consuming and difficult to grasp, especially for the layman.
Knowledge of Structured Query Language (SQL)
SQL has become the standard for database querying and creation of record sets. It is a time-consuming exercise to learn and implement, through trial and error, effective queries.
While these disadvantages may seem somewhat daunting, don’t despair. Consider the advantages, bearing in mind the limitations of the spreadsheet:
Size
There is no real upper limit on the size of a database for a vast majority of business applications. Different tools have different limits, but there are many ways around this, should those limits be reached.
Protection of calculations
Since most calculations, or formulae (known as expressions) are contained in locked objects, queries or code, they cannot be meddled with or accidently deleted.
Multi-user
Databases offer a true multi-user environment, with secure record-locking. Many users can capture data simultaneously.
Data validation
Data types and other tools, such as input masks and drop-downs, force the correct type of entry. In the case of drop-downs, or combo boxes, the user can be forced to select an item in the list. This avoids those erroneous spaces and punctuation marks from creeping in and messing up the data.
Data relationships
With data tables being joined, forming a relationship, rules can be applied to ensure integrity. An example would be that a sales order cannot exist without a customer.
Customised capture screens and reports
Some people say that a computer program is only as good as what goes in and what comes out. What goes in is represented by the capture screens, or forms. These can be customised to suit your requirements, with ease of use in mind. What comes out is in the form of the reports the system can generate. Using powerful queries, the reporting ability of database systems is extremely powerful.
Data movement
Data can be easily imported or exported in a variety of formats, from queries or reports as the data source. Many people like to manipulate records in a report by sorting or grouping easily. Reports are effortlessly exported to spreadsheets, where this can take place.
User-level security
Users can be allocated to groups to allow access to only certain areas of the database, thereby maintaining information confidentiality, when required.
The most effective way to overcome the disadvantages listed above is to employ the services of a consultant database programmer. Such a person would be able to analyse your existing systems and offer a viable and affordable solution.
Currently, this industry is highly competitive and this places you, the client, in the ideal position to explore your options.