Broadly, a database is a collection of data records. Databases are capable of holding and processing very large volumes of data. The database in Facebook holds records for well over a billion users for example.
The core of a database is a table. A table is a bit like a spreadsheet: it contains rows and columns of data. Unlike a spreadsheet, a database column contains a single type of data, perhaps a 6 digit number, a date, or a text field.
Typically a database consists of a set of tables. For example, a company might have a table of products, one of customers and another table of employees.
What are the advantages of databases over spreadsheets?
Firstly, spreadsheets are only suitable for a few thousand records at most. For larger volumes, databases are essential. It simply isn’t feasible to manage and keep track of large volumes of data in a spreadsheet.
Secondly, a spreadsheet is basically a stand alone application. Whilst they are good for some calculations, these calculations are contained within the spreadsheet. Databases do not have to be stand alone, they can be accessed by programs and usually are. This makes all sorts of processing possible. The main advantages of databases are:
searching for data is fast and easy
processing and calculations can be done
data can be displayed better, and viewed and input in forms
web sites can use databases
data can be grouped into classes and subclasses
selection and sorting are easy.
Classes and subclasses
With a database, it is easy to extract data for a class of record. It is also easy to get sub totals for each class of data. Consider the example of a sales table that records sales made by type of product (say hardware, software or accessories) and by salesman. You may want a monthly report of the total sales for each type of product and another report for the totals for each salesman. These figures can be produced by single calls to a database.
Single commands to the database can extract all records for a type of data. For example the web site for a music shop might want to display:
All blues albums
All Pink Floyd albums
All albums by artists beginning with B
Albums whose title contains “holiday”
These can be done directly
In a large system, there can be a hierarchy of classes. You can see this in web sites like Amazon, Argos or DIY shops.
Often data is needed from more than one table. Consider an invoicing program. An invoice needs data for:
A database engine can extract all this information with a single command.
In conclusion: using databases
Most business applications are built around databases. Databases provide ways to store data and this data can be analysed over time to provide valuable information about trends in a business.
About the author
John McMillan is a software developer based on the Essex Suffolk border in England, close to Cambridge and North East London. He carries out computer programming services to provide bespoke solutions to business needs. He has written software for a wide range of clients from the small SMEs up to blue chip companies.
Would you like to receive John’s occasional newsletter?
Click here to sign up for the newsletter.