Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

News      User Comments     History     Notify Me

10/17/2007 3:57:57 AM
Access Foreign Keys & Space
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Here's another GREAT question from a customer:

First of all, I now have a small business going as a database developer, and a large part of the credit goes to Richard. Excellent classes! But one question. I think you sometimes use IDs as primary keys in tables unneccesarily. For example, you showed a table of payment types, where you had an ID field and a payment type field. Wouldn't it be better just to have the payment type field only, and make it the primary key? Payment types will be unique, and then when you need the payment type in a query, you don't have to tie in the payment type table just to get the description, since the payment type will be stored as the foreign key in the other table, for example, the orders table. Please keep the Access classes coming!

It's certainly possible to create tables without keys. Personally, I prefer to use an ID field in almost every table because it keeps things clean and easy - and can save space in your database.

Let's take Payment Type for example. Let's look at two tables. The first one has IDs, and the second one doesn't.

1 - Visa
2 - MasterCard
3 - Discover
4 - American Express

Now in the second table, there's no ID to refer to the payment type, so for each customer's order where I have to track the payment type, their ORDER TABLE now has to have the full text of the payment method in it.

What takes up less space in your database:

1001, Joe Smith, American Express
1002, Sue Jones, MasterCard
1003, Bill Watson, American Express
1004, Adam West, American Express

Or this (assuming you use a CustomerID and PaymentID):

1001, 1, 4
1002, 2, 2
1003, 3, 4
1004, 4, 4

See? The space savings alone in a large database make it worthwhile. If you have 10,000 orders, you'll save multiple megabytes worth of space. And remember, a small database is a fast database.

ALSO, using IDs keeps your VBA code cleaner. What's easier:

X = DLOOKUP("Info","PaymentT","PaymentID=" & ID)


X = DLOOKUP("Info","PaymentT","PaymentType='" & PaymentName & "'")

Gotta put those single quotes (or two double quotes) around those string values - I hate working with string value.

So there ya go. Really it's just to keep the database tight and efficient, but there are lots of reasons why it's best to use an ID for every table - even if you don't think you'll need it.

Hope this helps.

Permanent Link
Keywords: access index id tips
Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 4+1:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
8/15/2018Access Dev 9 is ONLINE

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP