Database Design with Microsoft Access: How to Set Up Relationships Between Tables

Database Design with Microsoft Access: How to Set Up Relationships Between Tables
Page content

Primary Keys

A primary key is a field that is used to uniquely identify records within a table. An example of a primary key would be your unique Social Security Number (US) or National Insurance Number (UK). Such a unique field that can ONLY be attached to you would act as a useful primary key within a personnel or contacts database. It can be used to link data from your individual record to other tables where you have linked data items. Once assigned, a primary key within a table cannot be amended or left blank.

Foreign Keys

A foreign key (secondary key) is used to link data items from one table to another where the second table (child) has a foreign key that corresponds to the primary key of the first table (parent).

There are three kinds of primary keys:

AutoNumber is an automatically generated number which is sequentially added to each record as one is added to a table. AutoNumber fields can be generated either incrementally or randomly. Increment creates a record with the next value in sequence, random will assign a random long integer value. Being automatically generated means that they always remain unique.

Single-field contains a unique value such as an personal or product identifier. As previously mentioned an example of a single-field primary key would be your Social Security Number as it can be assigned with confidence that it is unique to all others. Single-fields make excellent primary keys as they are both unique and relevant data items (as opposed to an automatically generated number) but you must be aware that they cannot contain duplicate or null values.

Multiple-field is used when it is not possible to guarantee a single-field is going to be unique and therefore by combining two or more fields you can create a primary key. This is most common where tables have many-to-many relationships. For example an Order Details table could link Order and Product tables together using a primary key that consists of two fields, the Order ID and Product ID. Thus the Order Details table can list many products and many orders but each product can only be listed once per order so combining the relevant ID’s produces the appropriate primary key.

Setting up a Primary Key

1. In table design view, select the field you wish to set as the primary key. Hold the Ctrl key and click both field selectors.

2. Click the Primary Key button on the toolbar, or choose Edit – Primary Key.

3. Having finished designing the table and setting the primary key, be aware that you must save it before you can add any further records. Thus provide the table with a relevant name that describes its function or the records to be stored within it. Table names can be up to 64 characters in length and it is preferable to give them a prefix of ‘tbl’ or ‘_table’.

4. Click the tables tab on the toolbar and your freshly saved table with your naming convention will appear in the database window.

Note: Naming conventions are crucial to the cohesion of your database structure. Queries, tables, reports, forms, fields and controls all should have uniquely identifiable names that are relevant and can be understood quickly. It is important to remember these three rules when assigning naming conventions to each object of your database.

  • Names cannot be longer than 64 characters.
  • Names cannot include full stops.
  • Names cannot begin with a space.

It is a good idea to always provide objects with a descriptive name and prefix that is relevant to the task being performed. For example;

  • Forms = ‘_frm’ or ‘frm’
  • Queries = ‘_qry’ or ‘qry’
  • Reports = ‘_rpt’ or ‘rpt’
  • Tables = ‘_tbl’ or ‘tbl’

In the following part we will discuss setting up Relationship Types and how the use of ‘one to one’, ‘one to many’ and ‘many to many’ relationships can be achieved to optimize relational database table interoperability.

>» Microsoft Access: Designing a Relational Database (Part 1)

>» Microsoft Access: Designing a Relational Database (Part 2)

>» Microsoft Access: Understanding Data Types

>» Microsoft Access: Understanding Field Properties

>» Microsoft Access: Understanding Lookup Fields

This post is part of the series: Designing a Database with Microsoft Access

Need some help navigating Microsoft Access? This tutorial shows you how to design a database that works smoothly for all your projects.

  1. Microsoft Access: Designing a Relational Database (Part 1)
  2. Microsoft Access: Understanding Data Types
  3. Microsoft Access: Understanding Field Properties
  4. Microsoft Access: Understanding Lookup Fields
  5. Microsoft Access: Join Properties and Join Types
  6. Microsoft Access: Defining Relationship Types
  7. Microsoft Access: Setting Up Relationships Between Tables
  8. Microsoft Access: Designing a Relational Database (Part 2)