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






Link Database Tables
0th Normal Form (0nf)
First Normal Form
Second Normal Form
3rd Normal Form
Table Design Summary
Table Design Tutorial
Access Join Tables

Table Level Events

Article:
Repair Corrupted Table


Table Design in Microsoft Access

Table Design Examples & Recommendations

Table Design examples for all aspects of Access databases can be found in this area of our website.

The goal of this table design tutorial is to provide information for beginning Microsoft Access programmers and future customers so that they will gain a basic understanding of relational databases.



We have dozens of downloadable Access small business databases for you to learn and examine.

With our new partnership with Jay McCormick we have extensive programming support in SQL/Server database, MS Access software, Power BI applications, .Net web development, PHP, Excel & Outlook customization.


These table design guidelines are a requirement of a flexible and efficient database.  Without these conventions the database can become bulky and difficult to support and maintain.  For multi-user databases proper design is important to reduce the probability of database corruption which is a main concern for Microsoft Access programs.
Access Database Table Design Experts

These table design guidelines can be useful to most relational database programs.  After you have read the information below your are then ready to see how we exercise these designs in our Access query examples.

Microsoft Access Relational Database Table Design Rules

Some of the concepts and recommendations presented in this discussion are my own and may not conform to 'text book instructions'.   These are the techniques I have used successfully, some of them for the past 25 years.  Feel free to use this information if you think it will help you to develop your Access database.

Table of Contents

Microsoft Access Table Normalization help

Table and Database

 

Rules

Discussion

Linking Access Database Tables

Access Table Relationships Rules

Discussion

Foreign Keys

Database Object Naming help

Discussion

General Guidelines

Tables Names

Form Names

Query Names

Macro Names

Module Names

Field Names

Nth_Degree_Naming

Unique Numeric ID Fields

Lookup List help in Microsoft Access

Discussion

To Sum or Not to Sum

Discussion

To Index or Not to Index

Discussion

Slow Database

Macros

Discussion

Access Table Relationship Diagram

Discussion

Referential Integrity

Cascade Delete

Cascade Update

Microsoft Access Join Types

Discussion

Inner Joins

De-normalization

Outer Joins

Reverse Join

 

 


Back to Top

Table and Database 
'Normalization'

What is database normalization?  The text book definition will not help you much so I won't go into it.  You may of heard of 3rd normal form - this statement can be broken down into three simple rules:

1) Each field within a table should be dependent on the primary key field(s) and should be independent of all other fields in the table (see below).

2)  A field of data should only appear ONCE in a table.  The most common way this rule is broken is to have repeating groups  and calculated fields (see below).

3) There needs to be a primary key, uniquely identifying each record in a table (see below).

 

Discussion

From (1) above - Each field within a table should be dependent on the primary key field(s) and should be independent of all other fields in the table.  A generic example of a field that does not meet this definition is - when a field in your table contains values only some of the time based on some other field in the table or some event external to the table, then this field is not solely dependent on the main subject of the table.   Real life example:  Say you have a table tracking shipments of widgets and you have a field in your table called 'Date_Returned'.  This field is dependent on an external event to this table and would have a value in it occasionally.  This field should probably go into a new table called M_Returns.  Where you would store information specific to that returned item - like reason, etc.  M_Returns would have a link (foreign key) back to the M_Shipments table to maintain a table relationship.

Avoid repeating groups.  If you feel the need to append a number to a field name, such as: Fax_Date1, Fax_Date2...  Then you should probably have Fax_Dates in a separate table.

 

 

From 2 & 3 above - There must be a one-to-one table relationship between the primary key field(s) and any other field in the table.  Example: SSN field has a one-to-one Access table relationship to the employee.  A question to ask yourself is: 'can this employee (or part, or invoice, etc.) have more than one of these?'.  For SSN the answer is likely 'no'.  For childrens' names the answer may be 'yes'.  Therefore, consider putting employee children in a separate table linked by  Employee_ID.

 

 

Back to Top

 

Using field names and table names to store data is a common mistake.  Rarely, if ever, should you have field names or table names such as: Sales_1999, Inactive_Clients, MSFT_Contracts.  Each of the bold words should be fields in a table (ie. Year, Status, Company)

 

 

Use Pivot queries (crosstab) to extract data out of the normalized Sales table where the Year is a column heading (1999, 2000,2001,etc) and Sales is a row heading.

 

 

To extend this concept further - no field name or table name should contain data.  They are names - not data storage structures.

Lastly, almost never store calculated values in a table since the calculated value is typically not solely dependent on the primary key of the table.  It also wastes space in the table. Use queries, or form  and report fields to do the calculations on the fly (that's one thing computers are good at).

To see a more complete discussion on each normal form see these pages:

First Normal Form
Second Normal Form
Third Normal Form
Blue Claw Normal Form (a summary of parts of the page)

Linking Tables

Rules

Microsoft Access linked tables:  Linking tables is why you are creating this database and not using a spreadsheet.

 

 

Linking two or more tables together makes your database 'relational'. 

 

 

How you link tables together determines many things about your database.  In particular: maintainability, flexibility, usability, and longevity.

 

 

Many of the rules associated with linking tables are defined in other areas of this page.  But it is important to put all this together so you can see why some of the rules and recommendations exist.

 

Discussion

Most books tell you to migrate the primary key of the master table to the subordinate table so as to establish a relationship.  However, my cardinal rule is that generally no actual 'data' be used to link two tables together.

In rare cases, the primary key field is a unique numeric identifier equivalent to an autonumber field.  In this case it is ok to use this field to create the link between tables (example: a unique integer Part_ID).

The result of this rule is that the backbone of the database is independent of the data contained in the database.  There are typically two problems with using actual data to link tables: 

1) Data changes and therefore updates would need to be propagated beyond the primary table and requiring indexes to be changed which can potentially cause fragmented index space as well as a large database until it is compacted. 

2) Linking tables using integers is efficient when compared to linking tables using dates, long character fields, or composite primary keys. 

There is a 3rd, and overriding reason - generally, no data should appear in the database more than once.

Back to Top

Foreign Keys

When you put a Unique Numeric ID from a master table into a subordinate table then the ID field in the subordinate table is called a 'foreign key' to the master table.

 

 

Should you keep the name of the foreign key field the same in both tables?  Many people say NO!   I say YES!  Some programmers think it is confusing to see fields with the same name in different tables.  I think keeping the name the same tells you that these fields come from the same table. 

This rule must be relaxed when you need two instances of an ID field in the same table - in this case prefix each ID field name to distinguish them.

 

 

The Unique Numeric ID field should be first in all tables.  If any other field in a table ends in _ID then it must be a foreign key pointing to a table by the same name (with an 's' on the end maybe).   Example:  Employee_ID field points to M_Employees.Employee_ID.

Database Object Naming Conventions

Discussion

Someone (Red... somebody) thought up the current naming convention for tables, queries, etc.  Examples are: tblEmployee, qryEmployee, rptEmp_Summary.

 

 

My thoughts on this convention? What a waste!  This naming convention wastes some space and more importantly makes reading a list of objects difficult.

 

General Guidelines

Don't use all CAPS - hard to read.  Use something like M_Employees

 

 

Some people say never use plurals in table names - it is a waste of space.  I like them.

 

 

Don't use s p a c e s in any names.  It makes programming with these names in Visual Basic slightly annoying.

Back to Top

 

Never use special characters !@#$%^&*()  or numbers.  Only use letters.

 

 

Never use a reserved word by itself.  (Use help in Access to see a list of reserved words).  Examples: Date, Query, Table, Number, Sum.

 

 

Be specific but keep object names reasonably short.

Keep your object names in parallel when possible.  Examples are - tablename: M_Employees, formname: F_Employees.  If you have two employee forms then append a qualifying word to each form.  Example - F_Employees_Summary, F_Employees_Detail.

 

Tables Names

Here are my Microsoft Access naming conventions - use them if you like them.

 

 

Three identifiers for tables:
M_Tablename
L_Tablename
T_Tablename

 

 

'M_' is the prefix for the Main data tables.  These tables contain the real data in your database.

 

 

'L_' is the prefix for all static or semi static tables typically used for Lookups.

 

 

'T_' is the prefix for Temporary tables.

 

 

Why? It is easy to see the important tables (M_) and the object names are easier to read.

 

 

When splitting the database into a frontend/backend database combination - only the M_ tables would go to the backend.  If you have any L_ tables that change frequently then these must go in the backend with the M_ tables.

 Back to Top

Form Names

F_Formname - simple and obvious

 

Query Names

Q_Queryname - simple.  Some programmers like to distinguish the type of query by the prefix - like qru for update queries.  DON'T do this!  Access already tells you what type of query it  is with the little icon to the left of the name.

 

Macro Names

Don't use macros!

 

Module Names

M_Module - this is ok since we don't use macros (except an autoexec macro maybe).

 

Field Names

Naming fields in an odd or inconsistent way can make supporting your database difficult.  It can also make Visual Basic programming annoying and can make Active Server Page programming impossible.

 

 

All of the General Guidelines apply as well as the following:

 

 

A field name should make some sense outside of the context of the table in which it resides.  An example of this would be a field in an M_Orders table called 'Date'.  Date really is the Order_Date and should be named accordingly.  Also,  M_Employee.Number  - Number is the Employee_Number and should be named as such.

 

 

Be consistent when naming fields.  Use the same name for fields that contain the same type of data in different tables but distinguish them as necessary so the names make sense in the context of each table.  I know I haven't explained this very well so here is an example...

 Back to Top

 

You may have many 'Start_Dates' and 'End_Dates' in your database.  Keep the names of these fields consistent (or parallel) across tables.

 

 

Examples of consistent date field names are: Audit_Start_Date, Audit_End_Date, Job_Start_Date, Job_End_Date

 

 

Here are some examples of bad field names:

 

 

Driver 1, Student #, ORDERDATE, Sum, PRDCDE, ID

 

 

Here are some more good field names:

 

 

Date_Hired, Driver_ID, Date_Ordered, First_Name, Last_Name, Product_Code, Order_ID

 

 

Something about 'Codes' and 'Numbers'...

 

 

Try to use the word Code in field names when the data in the field is alphanumeric.  Only use 'Number' as part of the name of a field if it only contains numeric data.  This is the least important rule of all and I break it all the time.

 

Nth Degree Naming

Some folks recommend identifying the data type of every field as part of the name of the field by adding a prefix or suffix to each name.  Examples  of such a convention are chrFirst_Name, dteStart_Date, intOrder_Number. 

My opinion is that this takes conventions too far and overly complicates the database.  However, if you are developing reusable VB objects in Access then this makes sense.  But few of us will be doing this.

Back to Top

Unique Numeric ID Fields

As you can see from discussions in previous sections there is a convention for naming the Unique Numeric ID field within each table.  It is basically the root of the table name in singular form with _ID appended to it.  For the table M_Employees the unique ID field would be called Employee_ID. 

 

 

Don't use the _ID designation for any other field in your database other than these auto number fields! (or internally generated number fields that are equivalent to an autonumber field).

Lookup Lists

Discussion

If a field in a table can have a limited or known list of values then you should have a 'lookup table' to store the unique list.  An example would be a list of credit card types (Amex, MasterCard, etc). 

Back to Top

 I recommend naming lookup tables with an 'L_' prefix.  This distinguishes them from the other types of tables. Generally lookup tables will be included in the frontend of the database to improve database performance in a frontend/backend combination.

Always use a Unique Numeric ID in lookup tables and store this ID in the destination table - never the lookup value itself.  Storing the value would duplicate data in the database and break a cardinal rule.

Avoid the temptation to use abbreviations and codes for fields, especially in lookup tables.  This is often a carryover from DOS applications. 

An example is a list of shipping methods where you would have stored a single letter code for the method (e.g. U=UPS, F=Fedex, A=Airborne, etc).  This lookup table should have two fields - the numeric ID field (Method_ID) and the Method field.

To Sum or Not to Sum

Discussion

As a carryover from your spreadsheet days you may be tempted to store calculated fields in your tables.  As a general rule I recommend that you don't store calculated fields in a table.  Use visual basic, queries and reports to do the calculations if at all possible.  This goes along with the rule of not storing data twice in a database.

To Index or Not to Index

Discussion

All Unique Numeric Identifier fields (_ID) should be indexed in all occurrences.  Set them to 'Duplicates OK' only when they occur as foreign keys in a detail table.

 

 

Generally any field on the right site of an SQL 'where clause' should be indexed.  In the Access query design grid this is equivalent to saying any field  in the 'criteria' box should be indexed.

 

 

However, when you are doing some function to a field on the right site of a where clause or in the 'criteria' section of the design grid the index associated with the field will not be used.  Examples of functions are Count, Sum, Max, Left, Mid, Ltrim.  Also, if you use the 'like' operator then the index will typically not be used by the database engine.  These rules apply to most if not all database management systems.

 

Slow Database

A tip to employ if your database is slow because of zillions of records is to restrict the data first using a query and then use that query as input to the query that uses the function or the 'like' operator.  If that doesn't help then load the results of the initial restriction query into a temporary table.

Back to Top

 

Why index?  Joining two or more tables together using non indexed fields can take forever after you get many thousands of records in your database.

 

 

Why not index everything?  Indexing creates hidden tables and fields that take up space within your database.  Also, inserting and updating records with more indexes than necessary will take much more time than necessary.  Tip: when inserting many thousands records into a table, like when importing spreadsheet data, create the indexes after the import.

 

 

All Unique Numeric IDs in primary tables should be indexed and set to 'No Duplicates'.

Macros

Discussion

Macros are initially quick and easy because you don't have to use Visual Basic.  They were also necessary in early versions of MS Access.  With Access 2000 the need to use macros has essentially been eliminated.

 

 

I recommend that you don't use macros if at all possible.  Here's why:

 

 

Macros are difficult to maintain

 

 

Macros are difficult to document fully

 

 

Macros are difficult to debug

 Back to Top

 

Macros are like programming while looking through a keyhole

Relationship Diagram

Discussion

In MS Access there is an entry in the 'tools' menu called Relationships.  Why should you use it and how?

(click to see full size image)
Financial Accounts Management

 

 

The answer to why is:  to maintain the integrity of the relations in your database.

Back to Top

 

I have talked about master tables and subordinate tables.  The more common terminology is either 'master/detail' or 'parent/child'.  For this discussion let's use parent/child.

 

 

In the relationships screen you create the rules that Access will use to maintain the integrity of the relationships between your parent/child tables.

 

 

In the diagram (above) there is a parent/child relationship between M_Job_Lots and M_Job_Lot_Items.  The field called Job_Lot_ID links these tables.

 

Enforce Referential Integrity

When creating the link you will want to mark the 'Enforce Referential Integrity' check box and the 'Cascade Delete Related Records'.

 (click to see full size image)
 relationship diagram

Back to Top

 

The Enforce Referential Integrity setting prevents you from inserting 'orphaned' records. An orphaned record is when you have a child record with no matching parent record.

 

Cascade Delete

In a similar fashion the Cascade Delete prevents orphaned records when you delete a master record.  Access will automatically delete the associated child records.

 

 

All relational database management systems have this automatic capability.

 

Cascade Update

How about the 'Cascade Update Related Fields'?

 

 

This check box is for those Access users who use actual data to link two tables.  You should only need to check this on the rare occasion when the  ID field is an internally generated integer.  An example is an Employee_ID that is equivalent to an autonumber field.

Join Type

Discussion

When defining a relationship there is a button for 'Join Type'.  The Join Properties popup has three choices.

 Back to Top

Inner Joins

The first choice is 'Only include rows where the join fields in both tables are equal'.  This is the choice that you will pick 99% of the time.  After all, why would you put a foreign key into a child table if you didn't need it for all records?

 

Database De-normalization

The answer is that in the real world there are times when the foreign key is not a required field of the child table.  In this case it is not a true parent/child relationship.  It is more like an uncle/nephew relationship - sometime you have them and sometimes you don't. 

Back to Top

 

The discussion above might lead you to think that maybe this field (the foreign key) does not belong in the child table.  Maybe it belongs in a new table that is subordinate to both tables.  But for simplicity sake you decide to keep it in this table.  This decision is called database 'de-normalization' and is often done to eliminate the processing overhead associated with joining dozens of small tables.

 

 

 In other cases with these 'uncle/nephew' relationships you may not know the necessary information to make this link when the record is first created.

 

 

To keep the design of your database tight and 'normalized'  - consider having a dummy entry in the parent or 'uncle' table that is basically a place holder.  This entry might be 'To-Be-Determined'.

 

Outer Joins

In those few cases where you just won't have a value for the foreign key in the child table you will need to use the 2nd option (or 3rd - see below) in the Join Properties popup.  This option is usually referred to as an 'outer join'.  This tells Access to get all the records from the parent table and to get any records that happen to have a matching key in the child table.

Back to Top

Reverse Join

The 3rd option in the Join Properties popup is a always the opposite of the 2nd option.  Study the 2nd and 3rd choices to make sure you select the parent/child relationship and not the child/parent relationship. 










A Blue Claw Database Design Article:

MS Access Versus Other Systems
 







Blue Claw Database Design Downloadable Tutorial:
Send Email Microsoft Access Tutorial Download (Advanced)  







A Blue Claw Software Design Template:

Prescription Assistance Plan Admin Template
 











Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, Access 2010, MS Access 2013