1.1.3: Database Design and Four Main Objects

📌 Click here to view Microsoft Access - Setting a Foundation Chapter Sections

---

Database Design

A properly designed database will divide data into tables to eliminate duplicate information (also known as data redundancy). Redundancy occurs when data is repeated several times in a database. Redundant data is bad, because it wastes space and increases the likelihood of errors and inconsistencies. A good database prevents redundant data when, for example, customer data (ID, Name, Address, etc) is stored in one table and then other tables store just the customer ID to refer to the customers. Another important design consideration to maintain is data accuracy. A common motto when performing data entry is “garbage in, garbage out”. The correctness and completeness of information is important if the user wants to rely on their data. 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.

Creating a new database involves determining the database schema, which is a description of the data and the organization of the data into tables. Designing a schema consists of the following steps:

1. Determine the purpose of the database and which data to include in the 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.
2. Divide information into related groups to create tables and then give each table a descriptive name. Data should be grouped to ensure there is little to no redundancy. Each table must have data related to at least one other table through a table relationship.
3. Describe the fields and determine the primary key for each table.

The Four Main Objects in a Database

While Microsoft Access is made up of seven components, this text will focus on the main objects: tables, forms, queries and reports. Together, these objects allow users to enter, store, analyze and compile data in various ways.

Navigation Pane

When an Access database is opened, the objects are illustrated in the Navigation Pane on the left side of the window. The standard view in the Navigation Pane displays all objects in the database organized by object type. The Navigation Pane is used to run, open, and manage the objects of a database. Double-clicking an object will open the object in a document window.

A table is the database object that stores data organized in an arrangement of columns and rows. Each row is a record and each column is a field within each record. A record is all of the data pertaining to a person, place, thing or event. Each record will have one or more fields and every database must have at least one table. A relational database must have more than one table.

The other three objects are typically based off of the tables, so take the time to set up the tables correctly, the first time! A form object is typically used for entering new records into a table, or edit/deleting/display existing records in a table. Forms are the primary interface that most users of a database enter and display data. A query object retrieves specific data from other database objects and then displays only the data that the user specifies. Queries are the equivalent of asking questions about the data. The report object summarizes the records from a table or query into an easy-to-read format suitable for printing or exporting to other software programs.

Access objects have several views. Each view gives users different perspectives and offer different functionality. The two most important views are Datasheet view and Design view. Datasheet view displays a table in a datasheet where fields are in columns and records are in rows.

Datasheet view of a Customer table

This view is useful for comparing records and is often used when sorting or filtering data. This view is also practical for printing data since many of the records can fit on a single sheet of paper. Records can be added in datasheet view, but forms are a more typical object for data entry. Nonetheless, the asterisk (*) that appears to the left of the row below the last record indicates where a new record will appear. The grey box to the left of each record is a record selector. Clicking a record selector will make the record active. Right-clicking the record selector will allow users to delete the selector record or add a new record to the table. Design view allows users to setup and edit the fields in an object.