Flat Files (Spreadsheets) Versus Relational
Databases
The most basic form of a database is a flat file; all of the information is stored
in a single file. There is a field for each item of data that you need to store.
Spreadsheets are one of the most common examples of a flat file database. Flat file
databases are easy to create and can be useful in certain situations, however data
stored in flat files are not very efficient. There can be a lot of duplicated
information, there can be an increase in errors in the data, valuable disk storage
space can be wasted, and they can make information more difficult to retrieve and
maintain.
In the sample spreadsheet below, notice how the customer name and address is duplicated, if John Doe moves there can be numerous places the address must be updated, and if he is a good customer, there could be hundreds of updates that must be made. If his address were stored in only one place, then it would only have to be updated in one place.
The solution to the shortcomings of using a flat file database is a relational database. A relational database is a collection of related information broken down into different groups, which are then related to each other. In a relational database, these groups are called tables. The spreadsheet above stored information about Customers and Orders. Each order is placed by a single customer and contains several items. A relational database might be split into four tables: Customers, Orders, Order Details, and Products as shown in the diagram below.
The Customers information is stored separately from the Orders, the Order Details are stored independently from the Orders, and the Product information is stored separately from the Order Details.
- The Customers table contains a single entry for each customer.
- The Orders table contains a single entry for each order, and the Order Details table contains a single entry for each item in each order.
- The Products table contains one entry for each item that the Merchant has available to order and is matched by the Product ID to the Order Details only for the items on a particular order.
- The Order ID matches the Order Details to the Orders table, and the Customer ID matches the Customers table to the Orders table.
By creating the separate tables for specific information in the relational database, this will reduce the duplicate/redundant data, and improve efficiency in storing, reporting, and utilizing the information in the database. The Customers in the Customer table only need to be entered one time, and there will only be one place to go if you need to change/update information.
If this all sounds too complicated for you, don’t worry, DB-Guru will take care of all of these details and more. Your custom database application will have one form to enter/edit all of your customer information one time at the click of a button. When John Doe places an order, if he has ordered from you before, his information will be automatically entered into the order, you simply would enter the items ordered and print the invoice with the touch of a button.
See the sample forms below for the order database examples.
Please also see the Services page for more information and the Portfolio page for more sample forms and reports.