Excellent question! What are relational databases and why are they so important for small businesses? Many people in an office environment are familiar with Excel but are not sure what Access is or how it’s used. Suppose you’re a business owner. You’re an expert on, say, coffee, or construction, the law, or sales and marketing (in which case I’d definitely like to speak with you :)). But data bores you. Let’s face it, math isn’t exactly people’s #1 favorite subject. You DREAD the day each month when you have to go over spreadsheets of customers, clients, supplies, employees. Perhaps you have a lot of old data in manual Excel spreadsheets. Maybe it’s messy or your data spreadsheet has a lot of wrong or mis-typed entries. The same city entered five times with five different spellings. Phone numbers where emails should be, emails where ZIP codes should be. States that don’t exist. You don’t have time to deal with that. You’d like to fix and prevent these issues.Access is a sophisticated application, so I will limit my discussion to relational databases, using Access as a tool.
Suppose you have a business selling various types of tablets and devices. Here is part of your orders spreadsheet:
Organized, right? But look at all the repeated information? Suppose Ray Smith had 25 other orders… and then changed his phone number! You’d have to make 27 changes, hoping you didn’t miss any or mis-type. Not to mention the wasted space! Do you need to have Harry Potter’s email address in there three times?
Suppose we separate into two tables, a process called normalization. What this means is that we break up the larger table into two smaller tables to avoid repeated data. Now if, say, Julia Perez gets married and her new name is Santos, you only need to change it once, instead of searching the entire gigantic orders table for every instance and hope you don’t miss any.
In this case, the two tables are related. The ID in the customers is a primary key. What
this means is that it is the column or set of columns needed to uniquely identify one customer. Multiple customers will likely have a same last name, and if your business is large, you could have two different people with the same first and last name. Having a single
number “ID” field is a common remedy for this. Then in the orders table, you only need a customer ID as a foreign key to link the two tables. If you need to know the name and address of the person who ordered the 5 wants, your relational database will say, “Hmm, that’s from customer 3.
We’ll look up Customer ID 3 in the customers table since the foreign key CustomerID in the orders table matches the primary key customer ID in the customers table. Of course, you’re not manually looking up the orders table. The database queries and forms would be programmed to do that automatically. Here is a view of the resulting “nested” tables in Access: Now if Ray Smith changes his address, you change it once here instead of for each order (think of the time and aggravation this would save if he had hundreds of orders). Neat, huh?
Here is a look at what the table structure of such a database might look like. The lines are relationships showing how tables relate to each other.
Watch the quick video below to see normalization in action.