Databases and web apps can yield big business advantages. Database design is critical to achieving your goals, whether you want to manage employee information, provide weekly reports against data, or track customer orders. Investing the time to understand database design will help you build databases that work right the first time and that accommodate changing needs.
Important: Access web apps are different from desktop databases. This article doesn’t discuss web app design.
Concepts and terms
Let’s start by learning some basic terms and concepts. To design a useful database, you create tables that focus on one subject. In your tables, you capture all the data needed for that subject in fields, which hold the smallest possible unit of data.
| Relational databases | A database in which data is divided into tables, which are kind of like spreadsheets. Each table has just one subject, such as customers (one table) or products (another table). |
| Records and fields | Storage for the discrete data in a table. Rows (or records) store each unique data point, such as the name of a customer. Columns (or fields) isolate the information being captured about each data point into the smallest possible unit—first name might be one column and last name might be another. |
| Primary key | A value that ensures each record is unique. For example, there might be two customers with the same name, Elizabeth Andersen. But one of the Elizabeth Andersen records has the number 12 as its primary key and the other has a primary key of 58. |
| Parent-child relationships | Common relationships between tables. For example, a single customer may have multiple orders. Parent tables have primary keys. Child tables have foreign keys, which are values from the primary key that show how the child table records are linked to the parent table. These keys are linked by a relationship. |
What is good database design?
Two principles are fundamental to good database design:
- Avoid duplicate information (also called redundant data). It wastes space and increases the likelihood of errors.
- Ensure that data is correct and complete. Incomplete or erroneous information flows through in queries and reports and may ultimately lead to misinformed decisions.
To help with these issues:
- Divide database information into subject-based tables with a narrow focus. Avoid duplicating information in multiple tables. (For example, customer names should go in only one table.)
- Join the tables together using keys instead of duplicating data.
- Include processes that support and ensure the accuracy and integrity of database information.
- Design your database with your data processing and reporting needs in mind.
To improve the long-term usefulness of your databases, follow these five design steps:
Step 1: Determine the purpose of your database
Before you start, have a goal for your database.
More details
Step 2: Find and organize required information
Gather all of the types of information you want to record, such as your product names and order numbers.
More details
Step 3: Divide information into tables
Divide your information items into major entities or subjects, such as products, customers, and orders. Each subject becomes a table.
More details
Step 4: Turn information items into columns
Decide what information you need to store in each table. These discrete pieces of data become fields in the table. For example, an Employees table might include fields such as Last Name, First Name, and Hire Date.
More details
Step 5: Specify primary keys
Choose each table’s primary key. The primary key, such as Product ID or Order ID, uniquely identifies each record. If you don’t have an obvious, unique identifier, use Access to create one for you.
More details
Want more?
Guidelines for naming fields, controls, and objects
This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.