Using Relationships in Microsoft Access
One of the more common questions I receive as both a database designer and as someone who runs a website about database design is the question of Microsoft Access Table Relationships - more specifically … why?
Are establishing relationships within the relationship view in Microsoft Access necessary? If you don't have them, will the database work properly? Is there any advantage or disadvantage to using them or not using them in the database? Most people just want to know why they should bother, or if they should bother at all.
The answer? You should pay attention to them … if you want to.
Establishing relationships within the relationship view in Microsoft Access is a necessary step for a well-rounded and finalized project in order to protect your data from orphaned records - that is, records on the "many" side of a relationship that don't have a matching record on the "one" side of a relationship.
For instance - if you have a main table holding Social Security Numbers and contact information, and in a second table, you have many records related to each person's SSN, holding data like their phone numbers or favorite foods, then it wouldn't be a good idea to delete a "main" record, but still leave the associated records in the other table just hanging around. Those are orphaned records - their key field (the SSN) doesn't exist in any other table any more because we deleted it. This bloats the size of your database and potentially keeps a bunch of meaningless records hanging around to cause trouble.
Avoiding this is what establishing Referential Integrity will do (see Figure 1). Referential Integrity and Cascading Deletes simply says, "If you delete a main record, I'll go ahead and delete all of those related records for you."
In my own projects, I only enforce relationships within the relationship window if I need to establish referential integrity. Otherwise, I don't draw the line from one table to the next. Call it personal preference, call it silly, it's just how I do it. Certainly, in databases with many tables, establishing the relationships in that view are helpful because you can see how your data is laid out - a very important point indeed. For smaller databases that you use only on your own, it's not as important.
If you need to enforce referential integrity to cascade updates and deletes, it's always a good idea to spend a little bit of time planning the relationships out in that view. Above all, don't mess with the relationships until your database is built and finished, do so earlier and if you have to change any tables, you'll have to go back into the relationships view and delete the relationship before making the changes!
©2010 Blue Moose Technology, LLC
Article Publishing and Re-Print Guidelines
- The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
- When re-published online, all links must be live hyperlinks.
- These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
- These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!
You can easily link to this article!
If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:
On your page, the code will appear like this: