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.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.