From the course: Access Essential Training (Office 365/Microsoft)

Database concepts - Microsoft Access Tutorial

From the course: Access Essential Training (Office 365/Microsoft)

Start my 1-month free trial Buy for my team

Database concepts

“

- [Instructor] Microsoft Access is an application that helps you build relational databases, and that means, in order to learn Access, we also need to learn about how databases work at the same time. So this course is necessarily going to cover both topics simultaneously. Before we begin in Access, I think it's important to outline a few key concepts about what a relational database is and define some of the keywords that we'll be hearing throughout the course. Access creates databases that follow the relational model. Let's take a moment to understand what that means. Take a look at the following data table of contact phone numbers. Dahlia Landon, for example, has three different phone numbers stored in this table: one for the office, one for her mobile phone, and one for her home phone. This makes it easy to find any of her contact phone numbers by looking for the rows with Dahlia's name. The structure of the table introduces a number of redundancies, though. You'll notice that the first and last names of each person are repeated over and over every time there's an additional contact phone number. This repetition of data increases the space required to store the information, and creates an opportunity for inconsistencies in the data to creep into the database. For instance, if one of these rows had Dahlia's name misspelled, it could cause problems if you are creating a directory from this misspelled information. Data redundancy is a primary issue that databases are designed to efficiently handle, so there's room for improvement on how we're storing this particular information. We can structure this data table differently and store the same data. Now, instead of each phone number being recorded on different rows in the table, we create a new column for each type. I can still find Dahlia's office and mobile numbers, and you can continue adding columns to the right to store additional types of numbers as needed. But this approach introduces a new problem. Now we have lots of blank cells where individuals don't have a contact number of every type. Further, this table isn't going to grow very well. What if we wanted to add in a fax number for one individual, or a second office number? We'd have to alter the structure of the table and add additional columns to accommodate those types of changes. One of the main principles of a good database design is that users of a database should be able to input new data rows into a table, but they shouldn't need to add new columns, even in fringe cases. A well-designed relational database solves these problems, too. The solution in a relational database is to stop trying to store everything in one single table. Unlike a flat-file table, such as an Excel spreadsheet, Access organizes data into multiple tables that connect to each other through a common attribute called keys. Using the exact same data, we can organize our phone numbers like this, where we have a table that is just about the employees and another table that is just about phone numbers. These tables are related together through a new column called a key. In the phone number table, you'll see it's named the Employee ID. All of the same data is still present, and we can make the same types of connections. If I wanted to know Dahlia's phone numbers, I first need her employee ID from the employees table. Then I'll take that piece of information over to the phone numbers table and use it to look up the related rows here. In this case, we find the three rows that display Dahlia's office, mobile, and home numbers, exactly as before. With this approach to using multiple related tables, we can accommodate growth and prevent inefficiencies in our storage. We can easily add new people or additional types of contact numbers as new rows of data in each table. And neither of those changes require altering or adding new columns to either table. So, what are the main benefits of a relational database? One, they remove redundant information. This makes the database smaller and faster. And two, they eliminate the need to restructure the data tables in order to accommodate growth. When the process of adding new data only requires the tables to grow vertically by adding new rows, the internal structure is more predictable, and can better organize your information. It may not seem like that big of a deal, but relational databases can grow to store millions of rows of data or more. And small efficiency gains can add up quick and make the database more flexible in the types of analyses that you can perform and the speed at which they can retrieve and report useful information.

Contents