Proper Table Creation
for a Relational Microsoft Access Database
Provided by: 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 on table design and normalization
Return to the tips page
|
