Video: Get started with databases

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 databasesA 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 fieldsStorage 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 keyA 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 relationshipsCommon 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

Introduction to tables

Excel training

Outlook training

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: