Microsoft Access Table Design
Access Relational Table Design is the most important phase of a relational database. A professional table design will result in a database that will hold more data, last longer and support more concurrent users than a database with an inefficient table design.
Whether you are creating a simple database or a complex database – a professional relational table design doesn’t take much more time and will result in a resilient database. To use SQL table queries to their fullest extent you will need properly normalized Access table design.
Sometimes a picture is worth 1000 words. At the bottom of this page there is an image of Microsoft Access Table Relations. Take a quick look so you have in mind the perfect table design layout.
Numeric index pointers to lookup lists are a way to increase the performance of Access tables with many data fields. Another design technique is the use of autonumber fields to link tables to related tables. Historically, a primary key was created based on one or several data fields in a table. These primary key fields were duplicated in subordinate Access tables. Or worse, a new field was created to hold the actual concatenated values of the multiple-field primary key resulting in multiple copies of multiple fields of data throughout the database. If ever one field in the Microsoft Access primary key changed the result was a time consuming and dangerous cascading update – often requiring extreme normalization techniques to ensure that the proper foreign keys get updated.
Microsoft Access Primary Key Preferred Method: In Access table design current techniques we don’t get rid of the primary key we just create a more efficient alternate (or pseudo) key with the autonumber field. This field is independent of the actual data in the table and therefore never requires a cascading update – when the primary key field data changes it only changes in one place since it only exists in a single table. The autonumber field is often set as the table’s primary key and therefore is indexed, no duplicates. Alternatively, real user-data field(s) is used to create the Access database primary key in the access tables.
Getting back to lookup lists – storing a numeric pointer to the list item works in the same way as the indexed linking field discussed above. For instance, if you have a lookup list of shipping methods and use the numeric pointer to the lookup list then you don’t have to worry about cascading updates of all your shipping records when FedEx changes its name to Ace Delivery.
Follow these links to details on the topic of Access relational database design examples:
Linking Database Tables Tutorial
Definition Examples and Recommendations
Zero Normal Form ( 0NF )
Spreadsheet layout for tables
1st Normal Form ( 1NF )
First normal form analysis and discussion
2nd Normal Form ( 2NF )
Second Normal Form discussion
3rd Normal Form ( 3NF ) Tutorial
Rules for third normal form tables – the ultimate design goal.
Our Access Table Design Rules
Blue Claw Design Guidelines
Access Table Design Tutorial Summary
Summary overview of key design guidelines.
For more formal discussion of The Standard in database table Normalisation techniques visit About.Com’s Boyce-Codd Normal Form (BCNF)discussion.
To create tables, modify tables and update the design elements of database tables using SQL or VBA code then see ourData Definition Language Table Management tutorial.
Note: Below is a complete Microsoft Access relational table design. The diagram shows primary key indexes and understand the ID fields in their originating tables are indexed (no duplicates) which makes them functionally equivalent to a primary key. We did not create relationship to the static lookup tables ( L_xxx) to make the diagram easier to read.
Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016