Video: Get started with databases

To keep your design focused, summarize the purpose of the database and refer to the summary often. If you want 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.” For an enterprise database, you might need multiple paragraphs to describe when and how people in different roles will use the database and its data. Create a specific and detailed mission statement to refer to throughout the design process.

Start with your existing information and tracking methods. For example, maybe you currently record purchase orders in a ledger or you keep customer information on paper forms. Use those sources to list the information you currently capture (for example, all the boxes on your forms). Where you don’t currently capture important information, think about what discrete information you need. Each individual data type becomes a field in your database.

Don’t worry about making your first list perfect—you can fine-tune it over time. But do consider all the people who use this information, and ask for their ideas.

Next, think about what you want out of the database and the types of reports or mailings you want to produce. Then, make sure you’re capturing the information required to meet those goals. For example, if you want a report that shows sales by region, you need to capture the sales data at the regional level. Try to sketch out the report with the actual information as you’d like to see it. Then, list the data that you need to create the report. Do the same thing for mailings or other outputs you want from the database.

Example

Suppose you give customers the opportunity to opt in to (or out of) periodic email updates, and you want to print a list of those who have opted in. You need a Send Email column in the Customer table, with allowable values of Yes and No.

For those willing to receive emails, you need an email address, which also requires a field. If you want to include a proper salutation (such as Mr., Mrs., or Ms.), include a Salutation field. If you want to address customers by their first name in emails, add a First Name field.

After you have your list of required information, determine the major entities (or subjects) you need to organize your data. Avoid duplicating data across entities. For example, the preliminary list for a product sales database might look like this:

The major entities are: customers, suppliers, products, and orders. So start out with those four tables: one for facts about customers, one for facts about suppliers, and so on. This may not be your final design, but it’s a good starting point.

To understand why more tables are better than fewer, consider the table shown here:

Each row contains information about both the product and its supplier. Because you may have many products from the same supplier, the supplier name and address information has to be repeated many times. This wastes disk space. Instead, record the supplier information only once in a separate Suppliers table and then link that table to the Products table.

A second problem with this design is evident when you need to modify information about the supplier. 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 that problem.

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. With this design, how would you delete the product record without also losing the supplier information? You can't. Because each record contains facts about a product in addition to facts about a supplier, it’s impossible to delete one without deleting the other. To keep these facts separate, split this table into two: the first for product information and the second for supplier information. Then, when you delete a product record, you delete only the facts about the product—not the facts about the supplier.

After you’ve chosen the subject for a database table, the columns in that table should only store facts about that single subject. For instance, a product table should store facts only about products—not about their suppliers.

To decide what information to track in the table, use the list you created earlier. For example, the Customers table might include: First Name, Last Name, Address, Send Email, Salutation, and Email Address. Each record (customer) in the table contains the same set of columns, so you store exactly the same information for each customer.

Create your first list, and then review and refine it. Remember to break information down into the smallest possible fields. For example, if your initial list has Address as a field, break that down into Street Address, City, State, and Zip Code—or, if your customers are global, into even more fields. That way, for example, you can do mailings in the proper format or report on orders by state.

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

You need a way to uniquely identify each row in each table. Remember the earlier example where two customers have the same name? Because they share a name, you need a way to separately identify each one.

So every table should include a column (or set of columns) that uniquely identifies each row. This is called the primary key and is often a unique number, such as an employee ID number or a serial number. Access uses primary keys to quickly associate data from multiple tables and to bring the data together for you.

Sometimes the primary key consists of two or more fields. For example, an Order Details table that stores line items for orders might 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.

If you already have a unique identifier for the information in a table, such as product numbers that uniquely identify each product in your catalog, use that, but only if the values meet these rules for primary keys:

• The identifier will always be different for each record. Duplicate values are not permitted in a primary key.

• There is always a value for the item. Every record in your table must have a primary key. If you are using multiple columns to create the key (like Part Family and Part Number), both values must always be present.

• The primary key is a value that doesn’t change. Because the keys are referenced by other tables, any change to a primary key in one table means a change to it everywhere it’s referenced. Frequent changes increase the risk of errors.

If you don’t have an obvious identifier, use an arbitrary, unique number as the primary key. For example, you might assign each order a unique order number for the sole purpose of identifying the order.