Time for a quick Microsoft Access knowledge check. This quiz covers a handful of relational database fundamentals that every intermediate Access developer should know: primary keys, foreign keys, AutoNumbers, lookup values, and unique indexes. These are the little details that keep a database organized instead of turning it into a pile of duplicate data and sadness.
Give yourself a moment to answer each question before checking the explanation. Keep score if you like. No peeking. The database police are watching.
Question 1: What is the main purpose of a primary key in an Access table?
Is it used to uniquely identify each record, store copies of related records, automatically create forms, or sort records alphabetically?
Answer: A primary key uniquely identifies each record in a table.
For example, in a Customer table, each customer should have a unique CustomerID. Two customers might share the same name, city, phone number, or even an email address in some situations, but they should never share the same primary key value.
Primary keys are the foundation of relational database design. They give Access a reliable way to tell one record from another and allow other tables to point back to that specific record. Without a proper key, related data gets messy very quickly.
Question 2: A customer can have many contact records. Where should the CustomerID foreign key normally be stored?
Should it be in the Customer table only, the Contact table, both tables as separate primary keys, or in a report instead of a table?
Answer: The CustomerID foreign key belongs in the Contact table.
This is one of the most important rules in relational database design: in a one-to-many relationship, the foreign key goes on the many side.
One customer may have many contacts. Maybe a company has a billing contact, a shipping contact, a manager, and three people who all insist they are the person you need to talk to. Each of those contact records needs a CustomerID field so Access knows which customer owns that contact.
You would not create CustomerID1, CustomerID2, CustomerID3, and so on in the Customer table. That is a classic beginner mistake. It limits how many related records you can store and makes queries, forms, and reports much more difficult than they need to be.
Question 3: If a primary key is an AutoNumber field, what data type should its matching foreign key usually be?
Short Text, AutoNumber, Long Integer, or Date/Time?
Answer: The foreign key should be a Number field with a field size of Long Integer.
An AutoNumber primary key stores a Long Integer value behind the scenes. Therefore, the matching foreign key must be able to store that same kind of value. In Access, that means setting the related field to Number and choosing Long Integer for its Field Size property.
Do not make the foreign key another AutoNumber. AutoNumber fields generate new values automatically, which is exactly what you do not want in a foreign key. The foreign key needs to store the ID that already exists in the related table.
Question 4: Why is it usually better to store a ShippingMethodID in an order record instead of repeatedly typing the shipping method name?
Do IDs make the table use more columns? Do they avoid inconsistent duplicate names and make changes easier? Can Access not store text in related tables? Or can shipping methods only be used once?
Answer: Using an ID avoids inconsistent duplicate names and makes changes much easier.
Suppose you type shipping methods directly into every order: "UPS Ground," "UPS ground," "UPS-Ground," "UPS," and "That brown truck company." Access sees those as different values, even though they may all mean the same thing.
Instead, create a ShippingMethod table with one record for each method and assign each one a ShippingMethodID. Your Order table stores only that ID. If you later decide to rename "UPS Ground" to something else, you change it once in the ShippingMethod table. Every related order automatically reflects the updated name in your queries, forms, and reports.
That is the whole point of relational design: store a fact once, then relate to it wherever needed. It saves space, prevents typo-related nonsense, and makes maintenance much less painful.
Question 5: What does setting a field's Indexed property to Yes, No Duplicates accomplish?
Does it allow only one blank value, automatically make the field an AutoNumber, prevent two records from having the same field value, or create a relationship with every table?
Answer: It prevents two records from having the same field value.
A unique index is useful when a field should not contain duplicate values, even if it is not the primary key. For example, you may want customer account numbers, employee badge numbers, inventory SKU values, or email addresses to be unique.
Be careful, however. A unique index does not automatically make a field a primary key, and it does not create relationships for you. It simply tells Access, "No two records can have the same value in this field." That can be a very useful rule when the data genuinely needs to be unique.
So, how did you do? If you got all five, congratulations: you have a solid grasp of some of the most important building blocks in Access table design. If you missed a couple, do not worry. Relational design takes a little practice, especially when you are learning where keys belong and why duplicate data causes so many problems.
Watch the embedded video for the full quiz and explanations. These topics, and plenty more relational table fundamentals, are covered in my Access Expert Level 1 class.
Live long and prosper,
RR
No comments:
Post a Comment