Home  Fees/Services  Access Examples  Tutorials  Access Downloads  Articles  Search  Contact  Privacy  Links
Table Design Examples

Access Table Design Guidelines		 Microsoft Access Table Design

Access Program Tutorials> Table Design Access >


 

Link Database Tables
0th Normal Form (0nf)
First Normal Form
Second Normal Form
Third Normal Form
Table Design Summary
Table Design Tips

Repair Corrupted Table


Microsoft Access Table Design

Table Design in Microsoft Access 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 an resilient database.

Numeric index pointers to lookup lists is a way to increase the performance of tables with many data fields.  Another table 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 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 through out 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 records get updated.

In 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 primary key field data changes it only changes in one place since it only exists in a single table.  The auto number field is often set as the table's primary key and therefore is indexed, no duplicates.  Alternatively, real user-data field(s) are used to create the primary key in the table design.

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 it's name to FUDex.

Follow these links to details on the topic of Access table design examples:

Linking Database Tables
Definition Examples and Recommendations

Zero Normal Form (0nf)
Spreadsheet layout for tables

1st Normal Form (1nf)
First normal form table design

2nd Normal Form (2nf)
Second Normal Form discussion

3rd Normal Form (3nf)
Rules for third normal form tables

Our Table Design Rules
Blue Claw Design Guidelines

Table Design Help Summary

Specific Topic Pages:

Primary Keys

Database Table Design: How to Link Tables in a Relational Database

Employee_Name (SSN and Employee_Name together create a primary key) ... The primary key of the Employee_Hours table is the combination for the foreign key ...
www.blueclaw-db.com/database_link_tables.htm

Second Normal Form Table Design for Access Databases: Second ...

2NF only comes into play when the primary key of the table is comprised of two or more fields and each of these fields is not a candidate to be a primary ...
www.blueclaw-db.com/database_2nd_normal_form.htm

Composite Keys

Create Table in Access

The next example creates a new employee table with two Text fields, a Date/Time field, and a multi-field (composite) primary key index using the three ...
www.blueclaw-db.com/create_table_access_sql.htm

Second Normal Form Table Design for Access Databases: Second ...

2NF only comes into play when the primary key of the table is comprised of ... case that is designed to deal with tables that have composite primary keys. ...
www.blueclaw-db.com/database_2nd_normal_form.htm

Table Design Tutorial for MS Access

1) Each field within a table should be dependent on the primary key field(s) and ... tables using dates, long character fields, or composite primary keys. ...
www.blueclaw-db.com/helpmicrosoftaccess.htm

Indexes

Microsoft Access Table Management: Create Table, Create Index ...

Data Definition Language (DDL): Create, Alter Update tables using SQL statements in MS Access Database.
www.blueclaw-db.com/database_table_sql/

Create Index in Microsoft Access

Create Index Create Table Alter Table Add User DDL Table Management Examples ... table, The name of the existing table that will contain the index. ...
www.blueclaw-db.com/create_index_sql_ddl.htm

Alter Table in Microsoft Access

The DDL Alter Table statement provides you with the ability to ADD/DROP table fields and to change data types as well as modify or create indexes. ...
www.blueclaw-db.com/alter_table_ddl.htm

Joins

Access Programmer Answer FAQs

Inner Join vs Outer Join. Database becoming too large. Database has lost a report or form. Developer left your company. Database consultant won't finish ...
 

MS Access Tutorials | Database Programmer Training

Write SQL queries and learn query table join techniques (inner join outer joins). Update dependent combo box and implement form/recordset filters ...
 

Table Design Tutorial for MS Access

Referential Integrity. Cascade Delete. Cascade Update. Microsoft Access Join Types. Discussion. Inner Joins. De-normalization. Outer Joins. Reverse Join ...
 

Relationship Diagram

Table Design Tutorial for MS Access

Database Relationship Diagram. Discussion. Referential Integrity ... In the diagram (above) there is a parent/child relationship between M_Job_Lots and ...
 

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.

 

Contact Information

Table Design Guidelines
Microsoft Access 2003 2000 2002(XP) All Windows Versions