Introduction to database "Beginners Guide"

Introduction to database “Beginners Guide”

This article provides a brief overview of databases — what they are, why you might want to use one, and what the different parts of a database do. The terminology is geared toward Microsoft Office Access 2007 databases, but the concepts apply to all database products.

What is a database?

A database is a tool for collecting and organizing information. Databases can store information about people, products, orders, or anything else. Many databases start as a list in a word-processing program or spreadsheet. As the list grows bigger, redundancies and inconsistencies begin to appear in the data. The data becomes hard to understand in list form, and there are limited ways of searching or pulling subsets of data out for review. Once these problems start to appear, it’s a good idea to transfer the data to a database created by a database management system (DBMS), such as Office Access 2007.

A computerized database is a container of objects. One database can contain more than one table. For example, an inventory tracking system that uses three tables is not three databases, but one database that contains three tables. Unless it has been specifically designed to use data or code from another source, an Access database stores its tables in a single file, along with other objects, such as forms, reports, macros, and modules. Databases created in the Access 2007 format have the file extension .accdb, and databases created in earlier Access formats have the file extension .mdb. You can use Access 2007 to create files in earlier file formats (for example, Access 2000 and Access 2002-2003).

Using Access, you can:

Add new data to a database, such as a new item in an inventory
Edit existing data in the database, such as changing the current location of an item
Delete information, perhaps if an item is sold or discarded
Organize and view the data in different ways
Share the data with others via reports, e-mail messages, an intranet , or the Internet

The parts of an Access database

The following sections are short descriptions of the parts of a typical Access database.

Tables

A database table is similar in appearance to a spreadsheet, in that data is stored in rows and columns. As a result, it is usually quite easy to import a spreadsheet into a database table. The main difference between storing your data in a spreadsheet and storing it in a database is in how the data is organized.

To get the most flexibility out of a database, the data needs to be organized into tables so that redundancies don’t occur. For example, if you’re storing information about employees, each employee should only need to be entered once in a table that is set up just to hold employee data. Data about products will be stored in its own table, and data about branch offices will be stored in another table. This process is called normalization.

Each row in a table is referred to as a record. Records are where the individual pieces of information are stored. Each record consists of one or more fields. Fields correspond to the columns in the table. For example, you might have a table named “Employees” where each record (row) contains information about a different employee, and each field (column) contains a different type of information, such as first name, last name, address, and so on. Fields must be designated as a certain data type, whether it’s text, date or time, number, or some other type.

Another way to describe records and fields is to visualize a library’s old-style card catalog. Each card in the cabinet corresponds to a record in the database. Each piece of information on an individual card (author, title, and so on) corresponds to a field in the database.

Forms

Forms are sometimes referred to as “data entry screens.” They are the interfaces you use to work with your data, and they often contain command buttons that perform various commands. You can create a database without using forms by simply editing your data in the table datasheets. However, most database users prefer to use forms for viewing, entering, and editing data in the tables.

Forms provide an easy-to-use format for working with the data, and you can also add functional elements, such as command buttons, to them. You can program the buttons to determine which data appears on the form, open other forms or reports, or perform a variety of other tasks. For example, you might have a form named “Customer Form” in which you work with customer data. The customer form might have a button which opens an order form where you can enter a new order for that customer.

Forms also allow you to control how other users interact with the data in the database. For example, you can create a form that shows only certain fields and allows only certain operations to be performed. This helps protect data and to ensure that the data is entered properly.

Reports

Reports are what you use to summarize and present data in the tables. A report usually answers a specific question, such as “How much money did we receive from each customer this year?” or “What cities are our customers located in?” Each report can be formatted to present the information in the most readable way possible.

A report can be run at any time, and will always reflect the current data in the database. Reports are generally formatted to be printed out, but they can also be viewed on the screen, exported to another program, or sent as e-mail message.

Queries

Queries are the real workhorses in a database, and can perform many different functions. Their most common function is to retrieve specific data from the tables. The data you want to see is usually spread across several tables, and queries allow you to view it in a single datasheet. Also, since you usually don’t want to see all the records at once, queries let you add criteria to “filter” the data down to just the records you want. Queries often serve as the record source for forms and reports.

Certain queries are “updateable,” meaning you can edit the data in the underlying tables via the query datasheet. If you are working in an updateable query, remember that your changes are actually being made in the tables, not just in the query datasheet. Queries come in two basic varieties: select queries and action queries. A select query simply retrieves the data and makes it available for use. You can view the results of the query on the screen, print it out, or copy it to the clipboard. Or, you can use the output of the query as the record source for a form or report.

An action query, as the name implies, performs a task with the data. Action queries can be used to create new tables, add data to existing tables, update data, or delete data.

Macros

Macros in Access can be thought of as a simplified programming language which you can use to add functionality to your database. For example, you can attach a macro to a command button on a form so that the macro runs whenever the button is clicked. Macros contain actions that perform tasks, such as opening a report, running a query, or closing the database. Most database operations that
you do manually can be automated by using macros, so they can be great time-saving devices.

Modules

Modules, like macros, are objects you can use to add functionality to your database. Whereas you create macros in Access by choosing from a list of macro actions, you write modules in the Visual Basic for Applications (VBA) programming language. A module is a collection of declarations, statements, and procedures that are stored together as a unit. A module can be either a class module or a
standard module. Class modules are attached to forms or reports, and usually contain procedures that are specific to the form or report they’re attached to. Standard modules contain general procedures that aren’t associated with any other object. Standard modules are listed under Modules in the Navigation Pane, whereas class modules are not.

Database design basics

A properly designed database provides you with access to up-to-date, accurate information. Because a correct design is essential to achieving your goals in working with a database, investing the time required to learn the principles of good design makes sense. In the end, you are much more likely to end up with a database that meets your needs and can easily accommodate change. This article provides guidelines for planning a database. You will learn how to decide what information you need, how to divide that information into the appropriate tables and columns, and how those tables relate to each other. You should read this article before you create your first database.

Some database terms to know

Microsoft Office Access 2007 organizes your information into tables: lists of rows and columns reminiscent of an accountant’s pad or a Microsoft Office Excel 2007 worksheet. In a simple database, you might have only one table. For most databases you will need more than one. For example, you might have a table that stores information about products, another table that stores information about
orders, and another table with information about customers.

Each row is also called a record, and each column, is also called a field. A record is a meaningful and consistent way to combine information about something. A field is a single item of information — an item type that appears in every record. In the Products table, for instance, each row or record would hold information about one product. Each column or field holds some type of information about that product, such as its name or price.

What is a good database design?

Certain principles guide the database design process. The first principle is that duplicate information (also called redundant data) is bad, because it wastes space and increases the likelihood of errors and inconsistencies. The second principle is that the correctness and completeness of information is important. If your database contains incorrect information, any reports that pull information from the
database will also contain incorrect information. As a result, any decisions you make that are based on those reports will then be misinformed.

A good database design is, therefore, one that:

Divides your information into subject-based tables to reduce redundant data.
Provides Access with the information it requires to join the information in the tables together as needed.
Helps support and ensure the accuracy and integrity of your information.
Accommodates your data processing and reporting needs.

The design process

The design process consists of the following steps:

Determine the purpose of your database, this helps prepare you for the remaining steps.

Find and organize the information required, gather all of the types of information you might want to record in the database, such as product name and order number.
Divide the information into tables, divide your information items into major entities or subjects, such as Products or Orders. Each subject then becomes a table.
Turn information items into columns, Decide what information you want to store in each table. Each item becomes a field, and is displayed as a column in the table. For example, an Employees table might include fields such as Last Name and Hire Date.
Specify primary keys, choose each table’s primary key. The primary key is a column that is used to uniquely identify each row. An example might be Product ID or Order ID, set up the table relationships. Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.
Refine your design, analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed.
Apply the normalization rules, apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed.

Determining the purpose of your database

It is a good idea to write down the purpose of the database on paper — its purpose, how you expect to use it, and who will use it. For a small database for a home based business, for example, you might write something simple like “The customer database keeps a list of customer information for the purpose of producing mailings and reports.” If the database is more complex or is used by many people, as often occurs in a corporate setting, the purpose could easily be a paragraph or more and should include when and how each person will use the database. The idea is to have a well developed mission statement that can be referred to throughout the design process. Having such a statement helps you focus on your goals when you make decisions.

Finding and organizing the required information
To find and organize the information required, start with your existing information. For example, you might record purchase orders in a ledger or keep customer information on paper forms in a file cabinet. Gather those documents and list each type of information shown (for example, each box that you fill in on a form). If you don’t have any existing forms, imagine instead that you have to design a form to
record the customer information. What information would you put on the form? What fill-in boxes would you create? Identify and list each of these items. For example, suppose you currently keep the customer list on index cards. Examining these cards might show that each card holds a customers name, address, city, state, postal code and telephone number. Each of these items represents a potential column in a table.

As you prepare this list, don’t worry about getting it perfect at first. Instead, list each item that comes to mind. If someone else will be using the database, ask for their ideas, too. You can fine-tune the list later. Next, consider the types of reports or mailings you might want to produce from the database. For instance, you might want a product sales report to show sales by region, or an inventory summary report that shows product inventory levels. You might also want to
generate form letters to send to customers that announces a sale event or offers a premium. Design the report in your mind, and imagine what it would look like. What information would you place on the report? List each item. Do the same for the form letter and for any other report you anticipate creating.

Giving thought to the reports and mailings you might want to create helps you identify items you will need in your database. For example, suppose you give customers the opportunity to opt in to (or out of) periodic e-mail updates, and you want to print a listing of those who have opted in. To record that information, you add a ―Send e-mail‖ column to the customer table. For each customer, you can set the field to Yes or No. The requirement to send e-mail messages to customers suggests another item to record. Once you know that a customer wants to receive e-mail messages, you will also need to know the e-mail address to which to send them. Therefore you need to record an e-mail address for each customer.

It makes good sense to construct a prototype of each report or output listing and consider what items you will need to produce the report. For instance, when you examine a form letter, a few things might come to mind. If you want to include a proper salutation — for example, the “Mr.”, “Mrs.” or “Ms.” string that starts a greeting, you will have to create a salutation item. Also, you might typically start a
letter with ―Dear Mr. Smith‖, rather than ―Dear. Mr. Sylvester Smith‖. This suggests you would typically want to store the last name separate from the first name.

A key point to remember is that you should break each piece of information into its smallest useful parts. In the case of a name, to make the last name readily available, you will break the name into two parts — First Name and Last Name. To sort a report by last name, for example, it helps to have the customer’s last name stored separately. In general, if you want to sort, search, calculate, or report based on an item of information, you should put that item in its own field.

Think about the questions you might want the database to answer. For instance, how many sales of your featured product did you close last month? Where do your best customers live? Who is the supplier for your best-selling product? Anticipating these questions helps you zero in on additional items to record.

After gathering this information, you are ready for the next step.

Dividing the information into tables

To divide the information into tables, choose the major entities, or subjects. For example, after finding and organizing information for a product sales database, the preliminary list might look like the picture below.

The major entities shown here are the products, the suppliers, the customers, and the orders. Therefore, it makes sense to start out with these four tables: one for facts about products, one for facts about suppliers, one for facts about customers, and one for facts about orders. Although this doesn’t complete the list, it is a good starting point. You can continue to refine this list until you have a
design that works well.

When you first review the preliminary list of items, you might be tempted to place them all in a single table, instead of the four shown in the preceding illustration. You will learn here why that is a bad idea. Consider for a moment, the table shown here:

In this case, each row contains information about both the product and its supplier. Because you can have many products from the same supplier, the supplier name and address information has to be repeated many times. This wastes disk space. Recording the supplier information only once in a separate Suppliers table, and then linking that table to the Products table, is a much better solution.

A second problem with this design comes about when you need to modify information about the supplier. For example, suppose you need to change a supplier’s address. Because it appears in many places, you might accidentally change the address in one place but forget to change it in the others. Recording the supplier’s address in only one place solves the problem. When you design your database, always try to record each fact just once. If you find yourself repeating the same information in more than one place, such as the address for a particular supplier, place that information in a separate table.

Finally, suppose there is only one product supplied by Coho Winery, and you want to delete the product, but retain the supplier name and address information. How would you delete the product record without also losing the supplier information? You can’t. Because each record contains facts about a product, as well as facts about a supplier, you cannot delete one without deleting the other. To keep
these facts separate, you must split the one table into two: one table for product information, and another table for supplier information.

Deleting a product record should delete only the facts about the product, not the facts about the supplier. Once you have chosen the subject that is represented by a table, columns in that table should store facts only about the subject. For
instance, the product table should store facts only about products. Because the supplier address is a fact about the supplier, and not a fact about the product, it belongs in the supplier table.

Turning information items into columns

To determine the columns in a table, decide what information you need to track about the subject recorded in the table. For example, for the Customers table, Name, Address, City-State-Zip, Send e-mail, Salutation and E-mail address comprise a good starting list of columns. Each record in the table contains the same set of columns, so you can store Name, Address, City-State-Zip, Send e-mail, Salutation and E-mail address information for each record. For example, the address column contains customers’ addresses. Each record contains data about one customer, and the address field contains the address for that customer.

Once you have determined the initial set of columns for each table, you can further refine the columns. For example, it makes sense to store the customer name as two separate columns: first name and last name, so that you can sort, search, and index on just those columns. Similarly, the address actually consists of five separate components, address, city, state, postal code, and country/region, and
it also makes sense to store them in separate columns. If you want to perform a search, filter or sort operation by state, for example, you need the state information stored in a separate column.

You should also consider whether the database will hold information that is of domestic origin only, or international, as well. For instance, if you plan to store international addresses, it is better to have a Region column instead of State, because such a column can accommodate both domestic states and the regions of other countries/regions. Similarly, Postal Code makes more sense than Zip
Code if you are going to store international addresses.

The following list shows a few tips for determining your columns.

Don’t include calculated data

In most cases, you should not store the result of calculations in tables. Instead, you can have Access perform the calculations when you want to see the result. For example, suppose there is a Products On Order report that displays the subtotal of units on order for each category of product in the database. However, there is no Units On Order subtotal column in any table. Instead, the Products table includes a Units On Order column that stores the units on order for each product. Using that data, Access calculates the subtotal each time you print the report. The subtotal itself should not be stored in a table.

Store information in its smallest logical parts

You may be tempted to have a single field for full names, or for product names along with product descriptions. If you combine more than one kind of information in a field, it is difficult to retrieve individual facts later. Try to break down
information into logical parts; for example, create separate fields for first and last name, or for product name, category, and description.

Once you have refined the data columns in each table, you are ready to choose each table’s primary key.

Specifying primary keys

Each table should include a column or set of columns that uniquely identifies each row stored in the table. This is often a unique identification number, such as an employee ID number or a serial number. In database terminology, this information is called the primary key of the table. Access uses primary key fields to quickly associate data from multiple tables and bring the data together for you.

If you already have a unique identifier for a table, such as a product number that uniquely identifies each product in your catalog, you can use that identifier as the table’s primary key — but only if the values in this column will always be different for each record. You cannot have duplicate values in a primary key. For example, don’t use people’s names as a primary key, because names are not unique. You could easily have two people with the same name in the same table.

A primary key must always have a value. If a column’s value can become unassigned or unknown (a missing value) at some point, it can’t be used as a component in a primary key. You should always choose a primary key whose value will not change. In a database that uses more than one table, a table’s primary
key can be used as a reference in other tables. If the primary key changes, the change must also be applied everywhere the key is referenced. Using a primary key that will not change reduces the chance that the primary key might become out of sync with other tables that reference it.

Often, an arbitrary unique number is used as the primary key. For example, you might assign each order a unique order number. The order number’s only purpose is to identify an order. Once assigned, it never changes.

If you don’t have in mind a column or set of columns that might make a good primary key, consider using a column that has the AutoNumber data type. When you use the AutoNumber data type, Access automatically assigns a value for you. Such an identifier is factless; it contains no factual information describing the row that it represents. Factless identifiers are ideal for use as a primary key because they do not change. A primary key that contains facts about a row — a telephone number or a customer name, for example — is more likely to change, because the factual information itself might change.

A column set to the AutoNumber data type often makes a good primary key. No two product IDs are the same. In some cases, you may want to use two or more fields that, together, provide the primary key of a table. For example, an Order Details table that stores line items for orders would use two columns in its primary key: Order ID and Product ID. When a primary key employs more than one column, it is also called a composite key.

For the product sales database, you can create an AutoNumber column for each of the tables to serve as primary key: ProductID for the Products table, OrderID for the Orders table, CustomerID for the Customers table, and SupplierID for the Suppliers table.

Creating the table relationships

Now that you have divided your information into tables, you need a way to bring the information together again in meaningful ways. For example, the following form includes information from several tables.

Information in this form comes from the Customers table…
…the Employees table…
..the Orders table…
…the Products table…
…and the Order Details table.

Access is a relational database management system. In a relational database, you divide your information into separate, subject-based tables. You then use table relationships to bring the information together as needed.

Creating a one-to-many relationship

Consider this example: the Suppliers and Products tables in the product orders database. A supplier can supply any number of products. It follows that for any supplier represented in the Suppliers table, there can be many products represented in the Products table. The relationship between the Suppliers table and the Products table is, therefore, a one-to-many relationship.

To represent a one-to-many relationship in your database design, take the primary key on the “one” side of the relationship and add it as an additional column or columns to the table on the “many” side of the relationship. In this case, for example, you add the Supplier ID column from the Suppliers table to the Products table. Access can then use the supplier ID number in the Products table to locate
the correct supplier for each product.

The Supplier ID column in the Products table is called a foreign key. A foreign key is another table’s primary key. The Supplier ID column in the Products table is a foreign key because it is also the primary key in the Suppliers table. You provide the basis for joining related tables by establishing pairings of primary keys and foreign keys. If you are not sure which tables should share a common column, identifying a one-to-many relationship ensures that the two tables involved will, indeed, require a shared column.

Creating a many-to-many relationship

Consider the relationship between the Products table and Orders table.

A single order can include more than one product. On the other hand, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. And for each record in the Products table, there can be many records in the Orders table. This type of relationship is called a many-to-many relationship because for any product, there can be many orders; and for any order, there can be many products. Note that to detect many-to-many relationships between your tables, it is important that you consider both sides of the relationship.

The subjects of the two tables — orders and products — have a many-to-many relationship. This presents a problem. To understand the problem, imagine what would happen if you tried to create the relationship between the two tables by adding the Product ID field to the Orders table. To have more than one product per order, you need more than one record in the Orders table per order. You would be
repeating order information for each row that relates to a single order — resulting in an inefficient design that could lead to inaccurate data. You run into the same problem if you put the Order ID field in the Products table — you would have more than one record in the Products table for each product. How do you solve this problem?

The answer is to create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-tomany relationships. You insert the primary key from each of the two tables into the third table. As a result, the third table records each occurrence or instance of the relationship.

Each record in the Order Details table represents one line item on an order. The Order Details table’s primary key consists of two fields — the foreign keys from the Orders and the Products tables. Using the Order ID field alone doesn’t work as the primary key for this table, because one order can have many line items. The Order ID is repeated for each line item on an order, so the field doesn’t contain unique values. Using the Product ID field alone doesn’t work either, because one product can appear on many different orders.

But together, the two fields always produce a unique value for each record.
In the product sales database, the Orders table and the Products table are not related to each other directly. Instead, they are related indirectly through the Order Details table. The many-to-many relationship between orders and products is represented in the database by using two one-to-many relationships:

The Orders table and Order Details table have a one-to-many relationship. Each order can have more than one line item, but each line item is connected to only one order.
The Products table and Order Details table have a one-to-many relationship. Each product can have many line items associated with it, but each line item refers to only one product.

From the Order Details table, you can determine all of the products on a particular order. You can also determine all of the orders for a particular product. After incorporating the Order Details table, the list of tables and fields might look something like this:

Creating a one-to-one relationship

Another type of relationship is the one-to-one relationship. For instance, suppose you need to record some special supplementary product information that you will need rarely or that only applies to a few products. Because you don’t need the information often, and because storing the information in the Products table would result in empty space for every product to which it doesn’t apply, you place
it in a separate table. Like the Products table, you use the ProductID as the primary key. The relationship between this supplemental table and the Product table is a one-to-one relationship. For each record in the Product table, there exists a single matching record in the supplemental table. When you do identify such a relationship, both tables must share a common field.

When you detect the need for a one-to-one relationship in your database, consider whether you can put the information from the two tables together in one table. If you don’t want to do that for some reason, perhaps because it would result in a lot of empty space, the following list shows how you would represent the relationship in your design:

If the two tables have the same subject, you can probably set up the relationship by using the same primary key in both tables.
If the two tables have different subjects with different primary keys, choose one of the tables (either one) and insert its primary key in the other table as a foreign key.

Determining the relationships between tables helps you ensure that you have the right tables and columns. When a one-to-one or one-to-many relationship exists, the tables involved need to share a common column or columns. When a many-to-many relationship exists, a third table is needed to represent the relationship.

Refining the design

Once you have the tables, fields, and relationships you need, you should create and populate your tables with sample data and try working with the information: creating queries, adding new records, and so on. Doing this helps highlight potential problems — for example, you might need to add a column that you forgot to insert during your design phase, or you may have a table that you should split into two tables to remove duplication. See if you can use the database to get the answers you want. Create rough drafts of your forms and reports and see if they show the
data you expect. Look for unnecessary duplication of data and, when you find any, alter your design to eliminate it.

As you try out your initial database, you will probably discover room for improvement. Here are a few things to check for:

Did you forget any columns? If so, does the information belong in the existing tables? If it is information about something else,you may need to create another table. Create a column for every information item you need to track. If the information can’t be calculated from other columns, it is likely that you will need a new column for it.
Are any columns unnecessary because they can be calculated from existing fields? If an information item can be calculated from other existing columns — a discounted price calculated from the retail price, for example — it is usually better to do just that, and avoid creating new column.
Are you repeatedly entering duplicate information in one of your tables? If so, you probably need to divide the table into two tables that have a one-to-many relationship.
Do you have tables with many fields, a limited number of records, and many empty fields in individual records? If so, think about redesigning the table so it has fewer fields and more records.
Has each information item been broken into its smallest useful parts? If you need to report, sort, search, or calculate on an item of information, put that item in its own column.
Does each column contain a fact about the table’s subject? If a column does not contain information about the table’s subject, it belongs in a different table.
Are all relationships between tables represented, either by common fields or by a third table? One-to-one and one-to- many relationships require common columns. Many-to-many relationships require a third table.

Refining the Products table

Suppose that each product in the product sales database falls under a general category, such as beverages, condiments, or seafood.

The Products table could include a field that shows the category of each product.

Suppose that after examining and refining the design of the database, you decide to store a description of the category along with its name. If you add a Category Description field to the Products table, you have to repeat each category description for each product that falls under the category — this is not a good solution.

A better solution is to make Categories a new subject for the database to track, with its own table and its own primary key. You can then add the primary key from the Categories table to the Products table as a foreign key.

The Categories and Products tables have a one-to-many relationship: a category can include more than one product, but a product can belong to only one category.

When you review your table structures, be on the lookout for repeating groups. For example, consider a table containing the following columns:

Product ID
Name
Product ID1
Name1
Product ID2
Name2
Product ID3
Name3

Here, each product is a repeating group of columns that differs from the others only by adding a number to the end of the column name. When you see columns numbered this way, you should revisit your design. Such a design has several flaws. For starters, it forces you to place an upper limit on the number of products. As soon as you exceed that limit, you must add a new group of columns to the table structure, which is a major administrative task.

Another problem is that those suppliers that have fewer than the maximum number of products will waste some space, since the additional columns will be blank. The most serious flaw with such a design is that it makes many tasks difficult to perform, such as sorting or indexing the table by product ID or name.

Whenever you see repeating groups review the design closely with an eye on splitting the table in two. In the above example it is better to use two tables, one for suppliers and one for products, linked by supplier ID.

Applying the normalization rules

You can apply the data normalization rules (sometimes just called normalization rules) as the next step in your design. You use these rules to see if your tables are structured correctly. The process of applying the rules to your database design is called normalizing the database, or just normalization.

Normalization is most useful after you have represented all of the information items and have arrived at a preliminary design. The idea is to help you ensure that you have divided your information items into the appropriate tables. What normalization cannot do is ensure that you have all the correct data items to begin with. You apply the rules in succession, at each step ensuring that your design arrives at one of what is known as the “normal forms.”

Five normal forms are widely accepted — the first normal form through the fifth normal form. This article expands on the first three, because they are all that is required for the majority of database designs.

First normal form

First normal form states that at every row and column intersection in the table there, exists a single value, and never a list of values. For example, you cannot have a field named Price in which you place more than one Price. If you think of each intersection of rows and columns as a cell, each cell can hold only one value.

Second normal form

Second normal form requires that each non-key column be fully dependent on the entire primary key, not on just part of the key. This rule applies when you have a primary key that consists of more than one column. For example, suppose you have a table containing the following columns, where Order ID and Product ID form the primary key:

Order ID (primary key)
Product ID (primary key)
Product Name

This design violates second normal form, because Product Name is dependent on Product ID, but not on Order ID, so it is not dependent on the entire primary key. You must remove Product Name from the table. It belongs in a different table (Products).

Third normal form

Third normal form requires that not only every non-key column be dependent on the entire primary key, but that non-key columns be independent of each other.
Another way of saying this is that each non-key column must be dependent on the primary key and nothing but the primary key. For example, suppose you have a table containing the following columns:

ProductID (primary key)
Name
SRP
Discount

Assume that Discount depends on the suggested retail price (SRP). This table violates third normal form because a non-key column, Discount, depends on another non-key column, SRP. Column independence means that you should be able to change any non-key column without affecting any other column. If you change a value in the SRP field, the Discount would change accordingly, thus
violating that rule. In this case Discount should be moved to another table that is keyed on SRP.

Create tables in a database

When you create a database, you store your data in tables — subject-based lists of rows and columns. For instance, you might create a Contacts table to store a list of names, addresses and telephone numbers, or a Products table to store information about products. You should always start the design of a database by first creating its tables — even before you create any other database objects.
This article explains how to create tables. You will learn how to create a table, add fields to the table, and set a table’s primary key. You will also learn about data types, and how to set field and table properties.

Before you create tables, you should carefully examine your requirements and plan your database to discover what tables you need.

What is a table?

A table contains data about a particular subject, such as employees or products. Each record in a table contains information about one item, such as a particular employee. A record is made up of fields, such as name, address and telephone number. A record is also commonly called a row, and a field is also commonly called a column.

Record or row
Field or column

Your database can contain many tables, each storing information about a different subject. Each table can contain many fields of different types, including text, numbers, dates, and pictures.

The following list shows some common examples of tables you might create.

A customers table that lists your company’s customers and their addresses
A catalog of products you sell, including prices and pictures for each item
A tasks table that tracks tasks and due dates
An inventory of equipment or stock on hand

You should plan and design your database carefully to ensure its correctness and to avoid having to make too many changes later.

Create a new table

A simple database, such as a contact list, might use only a single table. Many databases, however, use several tables. When you create a new database, you create a new file on your computer that acts as a container for all of the objects in your database, including your tables.

You can create a table by creating a new database, by inserting a table into an existing database, or by importing or linking to a table from another data source — such as a Microsoft Office Excel 2007 workbook, a Microsoft Office Word 2007 document, a text file, or another database. When you create a new blank database, a new empty table is automatically inserted for you. You can then enter
data to start defining your fields.

Create a new table in a new database

Click the Microsoft Office Button , and then click New.
In the File Name box, type a file name. To change the location, click the folder icon to browse.
Click Create.

The new database is opened, and a new table named Table1 is created and opened in Datasheet view.

Create a new table in an existing database

Click the Microsoft Office Button , and then click Open.
In the Open dialog box, select and open the database

Import or link to create a table

You can create a table by importing or linking to information stored elsewhere. For instance, you can import or link to information in an Excel worksheet, SharePoint list, an XML file, another Access database, a Microsoft Office Outlook 2007 folder, and a number of other sources. When you import the information, you create a copy of the information in a new table in the current database. Conversely, when you link to the information, you create a linked table in the current database that represents a live link to the existing information that is stored elsewhere. Thus, when you change data in the linked table, you also change it in the original source too (with some exceptions — see the note below). When information is changed in the original source by using another program, that change is visible in the linked table.

Note: In some cases, you cannot make changes to your data source through a linked table, most notably when the data source is an Excel worksheet.

Create a new table by importing or linking to external data

1. To use an existing database, on the File menu, click Open.
2. In the Open dialog box, select and open the database.
3. To create a new database, on the File menu, click New.

In the File Name text box, type a file name. To change the location, click the folder icon.
Click Create.

The new database is opened, and a new table named Table1 is created and opened in Datasheet view.

4. On the External Data tab, in the Import group, click one of the available data sources.

5. Follow the instructions in the dialog boxes.

Access creates the new table and displays it in the Navigation Pane.

Create, edit or delete a relationship

After you have created a table for each subject in your database, you must provide Office Access 2007 with the means by which to bring that information back together again when needed. You do this by placing common fields in tables that are related, and by defining table relationships between your tables. You can then create queries, forms, and reports that display information from several tables at once.

This article provides step-by-step procedures for creating, editing and deleting a table relationship. You will learn how to enforce referential integrity to prevent the creation of orphan records, how to set the join type to determine which records from each side of a relationships are included in a query result, and how to set the cascade options to keep references synchronized.

Create a table relationship

You can create a table relationship in the Relationships window, or by dragging a field on to a datasheet from the Field List pane. When you create a relationship between tables, the common fields are not required to have the same names, although it is often the case that they do. Rather, the common fields must have the same data type. If the primary key field is an AutoNumber field, however, the foreign key field can also be a Number field if the FieldSize property of both fields is the same. For example, you can match an AutoNumber field and a Number field if the FieldSize property of both fields is Long Integer. When both common fields are Number fields, they must have the same FieldSize property setting.

Create a table relationship by using the Relationships document tab

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. On the Database Tools tab, in the Show/Hide group, click Relationships.

4. If you have not yet defined any relationships, the Show Table dialog box automatically appears. If it does not appear, on the Design tab, in the Relationships group, click Show Table.

The Show Table dialog box displays all of the tables and queries in the database.

To see only tables, click Tables. To see only queries, click Queries. To see both, click Both.

5. Select one or more tables or queries and then click Add. After you have finished adding tables and queries to the Relationships document tab, click Close.

6. Drag a field (typically the primary key) from one table to the common field (the foreign key) in the other table. To drag multiple fields, press the CTRL key, click each field, and then drag them.

The Edit Relationships dialog box appears.

7. Verify that the field names shown are the common fields for the relationship. If a field name is incorrect, click on the field name and select the appropriate field from the list.

To enforce referential integrity for this relationship, select the Enforce Referential Integrity check box.

8. Click Create.

Access draws a relationship line between the two tables. If you selected the Enforce Referential Integrity check box, the line appears thicker at each end. In addition, again only if you selected the Enforce Referential Integrity check box, the number 1 appears over the thick portion on one side of the relationship line, and the infinity symbol (∞) appears over the thick portion on the on the other side of the line, as shown in the following figure.

Notes:

To create a one-to-one relationship Both of the common fields (typically the primary key and foreign key fields) must have a unique index. This means that the Indexed property for these fields should be set to Yes (No Duplicates). If both fields have a unique index, Access creates a one-to-one relationship.
To create a one-to-many relationship The field on the one side (typically the primary key) of the relationship must have a unique index. This means that the Indexed property for this field should be set to Yes (No Duplicates). The field on the many side should not have a unique index. It can have an index, but it must allow duplicates. This means that the Indexed property for this field should be set to either No or Yes (Duplicates OK). When one field has a unique index, and the other does not, Access creates a one-to-many relationship.

Create a table relationship by using the Field List pane

In Office Access 2007, you can add a field to an existing table that is open in Datasheet view by dragging it from the Field List pane. The Field List pane shows fields available in related tables and also fields available in other tables in the database. When you drag a field from an “other” (unrelated) table and then complete the Lookup Wizard, a new one-to-many relationship is automatically created between the table in the Field List pane and the table to which you dragged the field. This relationship, created by Access, does not enforce referential integrity by default. To enforce referential integrity, you must edit the relationship.

Open a table in Datasheet view

Click the Microsoft Office Button , and then click Open.
In the Open dialog box, select and open the database.
In the Navigation Pane, right-click the table to which you want to add the field and create the relationship, and then clickDatasheet View on the shortcut menu.

Open the Field List pane

On the Datasheet tab, in the Fields & Columns group, click Add Existing Fields.

The Field List pane appears.

The Field List pane shows all of the other tables in your database, grouped into categories. When you work with a table in Datasheet view, Access displays fields in either of two categories in the Field List pane: Fields available in related tables and Fields available in other tables. The first category lists all of the tables that have a relationship with the table with which you are currently working. The
second category lists all of the tables with which your table does not have a relationship.

In the Field List pane, when you click the plus sign (+) next to a table name, you see a list of all the fields available in that table. To add a field to your table, drag the field that you want from the Field List pane to the table in Datasheet view.

Add a field and create a relationship from the Field List pane

1. On the Datasheet tab, in the Fields & Columns group, click Add Existing Fields.

The Field List pane appears.

2. Under Fields available in other tables, click the plus sign (+) next to a table name to display the list of fields in that table.

3. Drag the field that you want from the Field List pane to the table that is open in Datasheet view.

4. When the insertion line appears, drop the field into position. The Lookup Wizard starts.

5. Follow the instructions to complete the Lookup Wizard. The field appears in the table in Datasheet view.

When you drag a field from an “other” (unrelated) table and then complete the Lookup Wizard, a new one-to-many relationship is automatically created between the table in the Field List and the table to which you dragged the field. This relationship, created by Access, does not enforce referential integrity by default. To enforce referential integrity, you must edit the relationship.

Edit a table relationship

You change a table relationship by selecting it in the Relationships document tab and then editing it.

1. Carefully position the cursor so that it points to the relationship line, and then click the line to select it.

The relationship line appears thicker when it is selected.

2. With the relationship line selected, double-click it.

–or–

On the Design tab, in the Tools group, click Edit Relationships.

The Edit Relationships dialog box appears.

Open the Edit Relationships dialog box

1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.

3. On the Database Tools tab, in the Show/Hide group, click Relationships.

The Relationships document tab appears.

If you have not yet defined any relationships and this is the first time you are opening the Relationships document tab, the Show Table dialog box appears. If the dialog box appears, click Close.

4. On the Design tab, in the Relationships group, click All Relationships.

All tables with relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table’s Properties dialog box is selected) and their relationships will not be shown unless Show Hidden
Objects is selected in the Navigation Options dialog box.

5. Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.

6. Double-click the relationship line.

-or-

On the Design tab, in the Tools group, click Edit Relationships.

The Edit Relationships dialog box appears.

7. Make your changes, and then click OK.

The Edit Relationships dialog box allows you to change a table relationship. Specifically, you can change the tables or queries on either side of the relationship, or the fields on either side. You can also set the join type, or enforce referential
integrity and choose a cascade option.

Set the join type

When you define a table relationship, the facts about the relationship inform your query designs. For example, if you define a relationship between two tables, and you then create a query that employs the two tables, Access automatically selects the default matching fields based upon the fields specified in the relationship. You can override these initial default values in your query, but the values supplied by the relationship will often prove to be the correct ones. Because matching and bringing together data from more than one table is something you will do frequently in all but the most simple databases, setting defaults by creating relationships can be time saving and beneficial.

A multiple table query combines information from more than one table by matching the values in common fields. The operation that does the matching and combining is called a join. For instance, suppose you want to display customer orders. You create a query that joins the Customers table and the Orders table on the Customer ID field. The query result contains customer information and order
information for only those rows where a corresponding match was found.

One of the values you can specify for each relationship is the join type. The join type tells Access which records to include in a query result. For example, consider again a query that joins the Customers table and the Orders table on the common fields that represents the Customer ID. Using the default join type (called an inner join), the query returns only the Customer rows and the Order rows where the common fields (also called the joined fields) are equal.

However, suppose you want to include all Customers — even those who have not yet placed any orders. To accomplish this, you have to change the join type from an inner join to what is known as a left outer join. A left outer join returns all of the rows in the table on the left side of the relationship and only those that match in the table on the right. A right outer join returns all of the rows on the right and
only those that match on the left.

Note: In this case, “left” and “right” refer to the position of the tables in the Edit Relationships dialog box, not the Relationships document tab.

You should think about the result you will most often want from a query that joins the tables in this relationship, and then set the join type accordingly.

Set the join type

1. In the Edit Relationships dialog box, click Join Type.

The Join Properties dialog box appears.

2. Click your choice, and then click OK.

The following table (using the Customers and Orders tables) shows the three choices that are displayed in the Join Properties dialog box, the type of join they use, and whether all rows or matching rows are returned for each table.

When you choose option 2 or option 3, an arrow is shown on the relationship line. This arrow points to the side of the relationship that shows only matching rows.

Make changes in the Join Properties dialog box

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. On the Database Tools tab, in the Show/Hide group, click Relationships.

The Relationships document tab appears.

If you have not yet defined any relationships and this is the first time you are opening the Relationships document tab, the

Show Table dialog box appears. If the dialog box appears, click Close.

4. On the Design tab, in the Relationships group, click All Relationships.

All tables that have relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table’s Properties dialog box is selected) and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.

5. Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.

6. Double-click the relationship line.

-or-

On the Design tab, in the Tools group, click Edit Relationships.

The Edit Relationships dialog box appears.

7. Click Join Type

8. In the Join Properties dialog box, click an option, and then click OK.

9. Make any additional changes to the relationship, and then click OK.

Enforce referential integrity

The purpose of referential integrity is to to prevent orphan records and to keep references synchronized so that you don’t have any records that reference other records that no longer exist. You enforce referential integrity by enabling it for a table relationship. Once enforced, Access rejects any operation that would violate referential integrity for that table relationship. This means that Access will reject both updates that change the target of a reference and deletions that remove the target of a reference. To have Access propagate referential updates and deletions so that all related rows are changed accordingly, see the Set the cascade options section.

Turn referential integrity on or off

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. On the Database Tools tab, in the Show/Hide group, click Relationships.

The Relationships document tab appears.

If you have not yet defined any relationships and this is the first time you are opening the Relationships document tab, the Show Table dialog box appears. If the dialog box appears, click Close.

For more information about the Show Hidden Objects option, see the article Guide to the Navigation Pane.

5. Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.

6. Double-click the relationship line.

-or-

On the Design tab, in the Tools group, click Edit Relationships.

The Edit Relationships dialog box appears.

7. Check Enforce Referential Integrity.

8. Make any additional changes to the relationship, and then click OK.

After you have enforced referential integrity, the following rules apply:

You cannot enter a value in the foreign key field of a related table if that value doesn’t exist in the primary key field of the primary table — doing so creates orphan records.
You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to that employee in the Orders table. You can, however, choose to delete a primary record and all related records in one operation by selecting the Cascade Delete Related Records check box.
You cannot change a primary key value in the primary table if doing so would create orphan records. For example, you cannot change an order number in the Orders table if there are line items assigned to that order in the Order Details table. You can, however, choose to update a primary record and all related records in one operation by selecting the Cascade Update Related Fields check box.

Notes: If you have difficulty enabling referential integrity, note that the following conditions are required to enforce referential integrity:

The common field from the primary table must be a primary key or have a unique index.
The common fields must have the same data type. The one exception is that an Auto Number field can be related to a Number field that has a Field Size property setting of Long Integer.
Both tables exist in the same Access database. Referential integrity cannot be enforced on linked tables. However, if the source tables are in Access format, you can open the database in which they are stored and enable referential integrity in that database.

Set the cascade options

You might encounter a situation where you have a valid need to change the value on the “one” side of a relationship. In such a case, you need Access to automatically update all of the affected rows as part of a single operation. That way the update is completed in full so that your database is not left in an inconsistent state — with some rows updated and some not. Access helps you avoid this problem by supporting the Cascade Update Related Fields option. When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key.

You might also need to delete a row and all related records — for instance, a Shipper record and all related orders for that shipper. For this reason, Access supports the Cascade Delete Related Records option. When you enforce referential integrity and select the Cascade Delete Related Records check box, Access automatically deletes all records that reference the primary key when you delete
the record that contains the primary key.

Turn cascade update and/or cascade delete on or off

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. On the Database Tools tab, in the Show/Hide group, click Relationships.

The Relationships document tab appears.

If you have not yet defined any relationships and this is the first time you are opening the Relationships document tab, the Show Table dialog box appears. If the dialog box appears, click Close.

4. On the Design tab, in the Relationships group, click All Relationships.

For more information about the Show Hidden Objects option, see the article Guide to the Navigation Pane.

5. Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.

6. Double-click the relationship line.

-or-

On the Design tab, in the Tools group, click Edit Relationships.

The Edit Relationships dialog box appears.

7. Select the Enforce Referential Integrity check box.

8. Select either the Cascade Update Related Fields or the Cascade Delete Related Records check box, or select both.

9. Make any additional changes to the relationship, and then click OK.

Note: If the primary key is an AutoNumber field, selecting the Cascade Update Related Fields check box will have no effect, because you cannot change the value in an AutoNumber field.

Delete a table relationship

To remove a table relationship, you must delete the relationship line in the Relationships document tab. Carefully position the cursor so that it points to the relationship line, and then click the line. The relationship line appears thicker when it is selected. With the relationship line selected, press DELETE. Note that when you remove a relationship, you also remove referential integrity support for that relationship, if it is enabled. As a result, Access will no longer automatically prevent the creation of orphan records on the “many” side of a relationship.

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. On the Database Tools tab, in the Show/Hide group, click Relationships.

The Relationships document tab appears.

If you have not yet defined any relationships and this is the first time you are opening the Relationships document tab, the Show Table dialog box appears. If the dialog box appears, click Close.

4. On the Design tab, in the Relationships group, click All Relationships.

5. Click the relationship line for the relationship that you want to delete. The relationship line appears thicker when it is selected.

6. Press the DELETE key.

-or-

Right-click and then click DELETE.

7. Access might display the message Are you sure you want to permanently delete the selected relationship from your database?. If this confirmation message appears, click Yes.

Note: If either of the tables employed in the table relationship are in use — perhaps by another person or process, or in an open database object, such as a form — you will not be able to delete the relationship. You must close any open objects that employ these tables before you try to remove the relationship.