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 an 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 is 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 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 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 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 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 it's name to FUDex.
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
2nd Normal Form (2nf)
Second Normal Form discussion
3rd Normal Form (3nf) Tutorial
Rules for third normal form tables
Our Access Table Design Rules
Blue Claw Design Guidelines
Access Table Design Tutorial Summary
Specific Topic Pages:
Primary Keys
| 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 |
| 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 key |
Composite Keys
| 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 |
| 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. ... |
| 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. ... |
Indexes
| Data Definition Language (DDL): Create,
Alter Update tables using SQL statements in MS Access Database. |
| Create Index Create Table
Alter Table Add User DDL Table Management Examples ... table, The name of the existing table that will contain the index. |
| 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. |
Joins
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 ...
|
| Write SQL queries and learn query table
join techniques (inner join outer joins). Update dependent
combo box and implement form/recordset filters ... |
Referential Integrity. Cascade Delete.
Cascade Update. Microsoft Access Join Types. Discussion. Inner
Joins. De-normalization. Outer Joins. Reverse Join ...
|
Relationship Diagram
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.
Note: Below is a great Microsoft Access relational
table design. Shows primary key indexes and understand the 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.
