Proper Table Creation for a Relational Microsoft Access Database

Provided by the FMS Development Team

Do you have trouble developing tables for your relational database? It can be confusing to determine which data should be in its own table, and what fields should be grouped together. To help sort out this challenge, consider each of the tables as nouns (Person, Place, or Thing) and each of the fields as adjectives (words that describe nouns).

Anything that is a noun should be its own table. For example, such typical tables are Customers, Orders, Products, Employees, etc. For those fields that describe these nouns, the fields should be included in the respective table.

Here are some examples of adjectives:

Adjective Field Name
Phone Number of the Customer Customer Phone Number
Date of the Order Order Date
Category of the Product Product Category

If the noun has many of the same adjectives, then you should consider making a table that can hold all of those similar adjectives.

Multiple Adjective Examples:

  1. Products may fall into many categories, such as an XBox, which could be considered: Electronics, Game, and DVD. A table to hold the ProductID and Category Code is better used by forms and queries than Category1, Category2, and Category3.
  2. Customer Phone Numbers can be of type fax, mobile, home, or work, and a customer many have many of each. So, creating a table to hold the CustomerID, PhoneType, and PhoneNumber can be used, rather than fax1, fax2, mobile1, mobile2, home1, home2, work1, and work2.

For more information, visit our page Microsoft Access and SQL Server Database Normalization Tips.

Additional Resources

 

 

Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities.

Darren D.


View all FMS products for Microsoft Access All Our Microsoft Access Products

 

 

Free Product Catalog from FMS