The relational database model relies on data being stored in many small tables that can all be tied back together again through the use of key fields. Using the primary identifiers of a record, the database engine will search through related tables to find records that connect. In this video, Adam Wilbert shows how to use primary and foreign keys and demonstrates how they work to maintain the validity of the data.
- In a well designed database you'll create many tables that relate back to the records in other tables. If you have data that isn't specifically about the primary theme of the table, then it's probably the case that it should go in another table. For instance let's suppose that we want to store credit card information in our database, here's a couple of questions that you might ask when determining where to store that credit card data. Does a credit card number describe our guest themselves, and no it probably doesn't? Will every guest want us to remember their credit card number, and probably not? Will some guest have multiple credit cards on file with us? And, I'd say yes, that's a possibility.
So credit card numbers are not specifically about the guest, some guest won't have any and some guest will have many. All of these answers lead me to conclude that credit card information should be stored in a different table. Lets create a new table to store credit card information and then create the hooks to be able to tie it back to a specific guest. From the create tab I'll choose the table option to create a new table in datasheet view. And just like before I'm going to leave auto number ID field, but I'm going to change it's name to CreditCardID.
The next column is going to store the credit card name, it'll be stored as a short text data type. I'll press the tab key to go over to the next column, again, this is going to be the short text data type, and it'll store the card type. The fourth column, once again, short text data type, and will store the card number here. And finally the last column will store the expiration date so we can choose the date and time data type for that.
I don't need to add any additional field, so when I highlight the click to add section I'll just go ahead and click off of it. Now these are all the fields that we need to store the credit card details themselves. But, we'll also need to create a way to tell us what customer this card belongs to. We could put it in a name field, but what would happen if we have two guests with the same name? How would we know which of them this card belongs to? This is why we have the unique primary keys in each table. We can make use of them in other tables so that we'll know exactly which guest each card belongs to. This is called a foreign key in the credit card table since it tells us which each credit card it's connected with.
Let's create an additional field here called guest ID. Now because we're linking to the auto number field in the guest table I want to make sure that I choose the data type of number. The name of the field will be guest ID. I'll press enter and once again I'll just click anywhere on the screen to dismiss the click to add drop down menu. You can rearrange the columns any way you want. Go ahead and click on the guest ID column to highlight it and then click and drag to drag it over here to the beginning and place it right after the credit card ID. Typically you're going to find foreign key fields at the beginning of the table but it's not required that it be there.
Come up to the quick access toolbar and save the design of this new table and we'll call it guest credit cards. Then I'll create a new entry over here in the navigation ping. So now it's time to start filling in some data into our two tables, let's go ahead and close the guest credit cards table and take a look again at our guest table. I'll just go ahead and double click on it to open it. Previously we entered in three different guest, Katherine, Donald and Sarah. With the guest ID's of one, two, and three. So let's say that Katherine and Donald, that is, our guests with the ID of one and two, don't have any credit cards on file.
Sarah, our guest number three, has two cards. We would enter that data like this let's go ahead and open up the guest credit cards table, for the credit card ID remember we can't enter a new number here, it will be auto assigned. I'll go to the next column for guest ID and we'll type in three. Then we can fill in some additional details about the card name, type, number, and expiration date. Because we're tying it into guest ID number three we know that this credit card belongs to Sarah, let's go ahead and enter in her second card.
Notice that the structure is very flexible and it's efficient in allowing us to create one, many, or no records that are related to each customer, and the order the cards are entered in doesn't matter. If Katherine, the guest with the ID of number one decides later to put in a card, it's completely fine, it can go anywhere in the table as long as it's tied to her guest ID. We can go ahead here into the third record and type in a one for the guest ID to tie it to Katherine and type in her details. So adding foreign keys to your tables is just like adding any other field.
The important point to keep in mind is that the data types must match. This means that if you're using an auto number field as the primary key in one table like we are with our guests then the foreign key in the additional table, for instance the guest ID needs to be set to the numerical data type as well. As long as the data is the same you can use one as the reference to look up information in the other location.
- Determine the essential uses for the Trust Center.
- Explore the functions of the database Navigation pane.
- Recognize the fundamentals of entering data when using Access.
- Identify the necessary steps when importing a table when using Access.
- Break down the fundamentals of filtering and sorting table data in Access.
- Identify the method utilized when building queries in Design view.
- Determine the role of forms in Access.
- Examine all of the elements involved in maintaining a database in Access.
- Explore how to properly protect an Access database with a password.