From the course: Access 2019 Essential Training
Table structures and relationships
From the course: Access 2019 Essential Training
Table structures and relationships
- [Narrator] Tables provide the foundation for any good database. And the success of the database, how well it functions and how flexible it is to future growth often comes down to the structure of the tables that house your data. Understanding how tables function in Access is key to creating a well structured database. Tables in Access follow a very strict model. Their horizontal rows are called records. Records are an unbroken chain of properties that describe a single entity. For instance, in an employee's table you would have a record for each individual of the company. Fields are the vertical columns. Each field in a table is a single property that's being tracked for each record. In a well designed database table each field stores just a single piece of information, such as a first name instead of an entire full name. Each field can hold data of a single data type. You can chose to store short text which is up to a maximum of 255 characters. Or long text with a maximum character limit of just about 65,000. This is useful for listing comments or memos. Numerical values that you would prefer a mathematical calculation with can also be stored with a numerical value. These can be set to integers only or can include exact decimal values or even floating point numbers. There are data types that store date and time or currency values. AutoNumber fields create incrementing serial number that are automatically generated for you when new records are added to the table. Yes/No is a binary data type. It can be yes or no, true or false, or on and off. OLE objects is an older format used for attaching files such as images or word documents. If you do need to attach files, I recommend using the newer much more efficient attachment data type. Really, the OLE Object data type is just kept around for backwards compatibility and probably shouldn't be used at this point. If you would rather not import files directly into the database, you can chose to use a hyperlink data type and provide a link to external content. This could be files on your network, email addresses or locations on the internet. Finally the calculated field creates data by calculating it from one or more of your other fields. And the Lookup Wizard will help you create a drop down list to limit the available data that could be entered. In addition to the fields that describe each records properties you'll also create fields that linK tables together. Previously I've mentioned that Access stores data in related tables. Because of this, each record on the table needs to be uniquely identifiable. In order to assure this is the case, we'll create a field specifically for this purpose called the primary key. The primary key is typically a serial number or other unique identifier. Think about how many unique identifiers you have for the various databases that you personally appear in. You probably have a drivers license number, a library card number, a social security number, a checking account number and the list goes on and on. The reason all of these systems use serial numbers is instead of just your name is so that they can guarantee a unique individual. It wouldn't be very nice if one Mary Smith had to pay the fine for another Mary Smith's overdue library book. Just because the library couldn't tell them apart. The primary key will also be our link to finding related records in additional tables. In a related table, they'll be called the foreign key. The process of breaking down you into related tables is called normalization. Normalization rules fall into several categories called normal forms. Your database is said to be satisfying the first normal form, or 1NF, if all of the cells contain only a single value. Rather than have a single employee name field, we should break that into separate first name and last name fields. Breaking addresses into separate street, city, state, and zip code fields is another common example. The second normal form states that only data that is dependent on the primary key belongs in that table. We wouldn't want to store information about the customer that each employee has helped in the employee's table. Instead we should create a new table that describes the interactions between our employees and our customers. The third normal form states that the data that could be calculated or derived from other fields should not be stored in the database. You wouldn't want to create a field for employee initials, for example. Because we can easily calculate them By taking the first letter from the first name field, and the first letter from the last name field. Tables are where your data lives and all the foundation of your database. Creating a strong foundation is a base and then growing your database on top with the addition of queries forms and reports, will mean the your databases will be able to be flexible and efficient.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.