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

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



You did not know that nearly 100 cable and satellite tv installers use our software to manage their device inventory. They never lose track of an expensive device.

We have expert enterprise-level database development capabilities for cloud/web-based databases through our partnership with Jay McCormick and his team.


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:

Computer Disaster Recovery Planning
 







Blue Claw Database Design Downloadable Tutorial:
Choose Command Dynamic SQL Order By  







A Blue Claw Software Design Template:

Asbestos Survey Project Database
 











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


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, Access 2010, MS Access 2013