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:

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.