This database tracks customers and their orders in two separate but related tables.
Sketch out a diagram of your database, including its tables and how they relate to each other.
There are two basic types of databases:
-
Flat File: Think of a Rolodex when you think of a flat-file database. A flat-file database stores all of its information names, addresses, etc.in the same place, just like addresses are stored on a Rolodex card. Flat-file databases are incredibly simple to create and use, but they're not very powerful or well suited to many business tasks.
-
Relational: A relational database contains multiple tables that are related through matching fields. Figure illustrates the design of a relational database. The database in Figure has two tables one that stores customer names and addresses, and another that stores customer orders. The two tables are related or linked by a common field. Relational databases are very powerful, but developing one takes a lot of skill, a lot of practice, and a strong understanding of tables and fields.
Microsoft Access can create either type of database flat file or relational. However, most Access databases tend to be of the relational type.
Still fuzzy about how relational databases work? To see an example of a relational database, look at the diagram illustrated in Figure. This database tracks customers and their orders. To store this information, the database uses two tables: Customers and Orders. Each table contains fields that store similar information. The Customers table contains only information about customers and their addresses. The Orders table contains only information about any orders that were placed it doesn't contain any information about the customers. The two tables both have an ID field, and it's this ID field that relates or links the two tables.
Relational databases save storage space by cutting down on duplicate data. For example, the relational database shown in Figure stores information in two related tables and eliminates the need to reenter a customer's name and address each time that customer places a new order.
Relational databases require lots of planning ahead. Before you attempt to create your database, you should sit down with a trusty pencil and a pad of paper and walk through the following steps:
-
Determine the Purpose of the Database: Write down a list of the reports and lists that you want to come out of the database. This may seem a little backward at first, but these reports are the reason you're creating the database. Make a list of the reports and lists you want to see and then sketch some samples of these reports and lists be as detailed as possible. This will help determine the tables and fields to include in your database.
-
Write Down the Fields You Need: This should be an easy step once you have determined the purpose of your database and have sketched some sample reports and lists.
-
Organize and Group Related Fields into Separate Tables: Each table in the database should be based on only one subject. By breaking each subject into its own table, you avoid redundant information and make the database more organized. The database in Figure is broken down into two tables, Customers and Orders, so there isn't any duplicated data. When you brainstorm, try to break down your information as much as possible. If your table contains fields like Item 1, Item 2, Item 3, Item 4, and so on, you should probably break each item up into its own table.
-
Identify and Add the Fields Common to Each Table: In Figure the Customers table's ID field links to the Orders table's ID field. One of the linked fields should be the table's . See Adding A Primary Key to a Table for more information about primary keys.
-
Sketch a Diagram of Your Database: Create a diagram of your database similar to the one shown in Figure. Draw a box for each of your tables and write the table's field names inside that box. Draw a line between the related fields. Most table relationships are a one-to-many relationship. This means that a record in one table may be related to one or more records in another table. For example, in Figure, each record in the Customers table is related to one or more records in the Orders table. This makes sense since, hopefully, most customers will place more than one order. You should indicate the two sides of the relationship by drawing a "1" on the "one" side of the relationship line and an "
" (infinity symbol) on the "many" side of the relationship line.
All this writing and planning may seem like a lot of work, but they're both critical steps in creating a sound database. Carpenters wouldn't start building a house without their blueprints, would they? No and it's no different if you're going to create a good relational database.