• Search

  
  

• Recently Written

  • Tracking Hours
  • Bento Sweepstakes
  • Not Abandoned, Just Busy
  • New Bento Videos
  • An Introduction to Databases
  • Fields, Records, Tables & Forms
  • Fixing CSV Exports
  • Importing Related Data Into Bento
  • Field Orders In CSV Exports
  • The Bento User Guide

• Categories

  • Bento Basics
  • Bento Examples
  • Bento News
  • Bento Tips
  • Site Notes
  • Tutorials

• RSS Feed

  • Subscribe to our RSS feed

Bento is essentially a flat-file database application although it does contain some basic relational functionality despite great efforts having been made to hiding the inner complexities from the user.

If the above sentence has left you scratching your head then you need to read this article because it will explain what a database is, what a flat-file database is and what relational functionality is.

Flat-File Databases
A database is a store of information. The data is stored in tables and categorised by fields. Each group of information is a record. Consider an address book. The address book itself is a table. The categories in the address book are fields (name, address, phone number, etc.). Each entry in the address book is a record.

An address book may have tabs for each letter of the alphabet so that you can find the person you are looking for quickly and easily. A database can be searched or queried to find the data required. Because data is generally retrieved via a query, the data itself does not need to be entered in any particular order.

The type of database described above is called a flat-file database. This is because it is like a single sheet of paper with all the information on it.

Relational Databases
Explaining what a relational database is is best done by example so assume that you are running a mail-order service and you want to keep track of sales to customers. Essentially you want to store the following information:

• Customer Information: name, address, phone number
• Orders: product, quantity, price, customer
• Product Information: product, description, price, supplier
• Suppliers: name, address, phone number, products supplied

In a flat file database you would need to have a number of tables (two to be exact), each of which holds one set of information. One table would hold information about customers and their orders whilst the second would hold information about suppliers and their products. This is viable if each customer only orders one item and each supplier only makes one product which is a very unlikely scenario.

In reality each table would store a large amount of duplicate information, each order would entail the re-entry of all of the customer’s details. At some point a typing error is likely to occur, for example a customer’s name is incorrectly typed. Now, if you try to look at all the products ordered by a customer you will miss some because of the typing error. In addition, if a customer moves house then you will have to face a lot of typing to update all of the records. Finally, the huge amount of duplicate information takes up disk space, computer memory and will slow down the system.

Now consider the situation where you have a table containing each customer’s details and only one record for each customer. You also have a table containing each order made. Assume that you give each customer a reference number (because customers could share the same surname) and against each order you enter that reference number. You can now relate a customer to an order and if a customer’s details change then you only have to change one record in the customers’ table. Much neater and much more efficient.

A relational database will provide a link between these two table so that you can create a query which will show you the products ordered by each customer.

The same principle can be applied to suppliers and products.

Finally, a link can be made between the orders and the products (via a product reference number) so that a change in the details of a product will be picked up in the orders.

Therefore a relational database saves typing, reduces the potential for errors, reduces the size of a database and makes for much more flexible data management.

Bento: Sitting In The Middle
Bento sits somewhere between a flat-file and a relational database. All of the databases, or Libraries as they are called in Bento, are really flat-file databases and they use one table of data. However it is possible to create some basic relational database functionality via the Related Records Field which is covered in its own article.

Comments