Updated On 2/28/2017 10:30:57 AM
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.
Blue Claw Database Design is your premier source of Microsoft Access programming and database support. Both small business and department-level databases. Desktop installation as well as cloud/internet based solutions.
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:
1st Normal Form ( 1NF )
2nd Normal Form ( 2NF )
3rd Normal Form ( 3NF ) Tutorial
Our Access Table Design Rules
Access Table Design Tutorial Summary
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 our Data 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
Copyright 2000-2017 Blue Claw Database Design
Microsoft Access 2007, Access 2010, MS Access 2013