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