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. 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.
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 ourAccess 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
Linking Access Database Tables
Access Table Relationships Rules
Lookup List help in Microsoft Access
Access Table Relationship Diagram
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.
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.
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.
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.
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…
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.
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).
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.
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
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)
The answer to why is: to maintain the integrity of the relations in your database.
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)
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.
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?
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.
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.
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.
Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint
Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016