Home  Fees/Services  Access Examples  Tutorials  Access Downloads  Articles  Search  Contact  Privacy  Links
Access Table Design Tips & Tricks

Link Database Tables
0th Normal Form (0nf)
First Normal Form
Second Normal Form
3rd Normal Form
Table Design Summary
Table Design Tutorial
Access Join Tables

Table Level Events

Repair Corrupted Table

Access Table Design Tips & Tricks

The following tips and tricks will make creating and maintaining your databases simpler for you and for other developers who may support your databases in the future.  Some of the tips mentioned below are my own and some are industry standard.

We have dozens of downloadable Access small business databases for you to learn and examine.

With our new partnership with Jay McCormick we have extensive programming support in SQL/Server database, MS Access software, Power BI applications, .Net web development, PHP, Excel & Outlook customization.

Table & Field Names - These names should never contain special characters and should never be longer than the minimum amount of characters to accurately describe the data contained in the table of field

Parallel Table Names - Subordinate tables in a master detail relationship should take on the main table name followed by a suffix for the subject of the detail table.  Note the table M_Job_Lots is subordinate to the master table M_Jobs.  Following this concept further note the table M_Job_Lot_Items which is a detail table of M_Job_Lots.

Parallel Index Field Names - All tables which will be used as masters in a master detail relationship should contain an autonumber field.  This field should be indexed with no duplicates allowed. It may or may not be the primary key of the table.  Name this field as the table name (leaving off the M_ or L_ prefix and appending _ID to the end.  Also place this field first in the list of fields in table design view.  When this field is used as a foreign key in a subordinate table keep the name the same.

Join Type Options - When joining tables in a master detail relationship use 'Cascade Delete Related Records' thereby preventing orphaned records occurring in the detail table.  A properly design table relationship should never require 'Cascade Update Related Fields' - if you need this option checked then it is likely you have duplicate data in more than one table.  Data should only be stored once in a relational database.

Access Database Design Examples

A Blue Claw Database Design Article:

MS Access Versus Other Systems

Blue Claw Database Design Downloadable Tutorial:
Send Email Microsoft Access Tutorial Download (Advanced)  

A Blue Claw Software Design Template:

Personnel Agency Access Template

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint

Contact Information

Copyright 2000-2018 Blue Claw Database Design

Microsoft Access 2007, Access 2010, MS Access 2013