Understanding table structure and relationships

- Tables provide the foundation of any good database. And the success of the database, how well it functions and how flexible it is to future growth often comes down 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 employees table you would have a record for each individual in the company. Fields are the vertical columns.

Each field in a table is a single property that is being tracked for each record. In a well designed Access table each field stores just a single piece of information, such as a first name, instead of the entire full name. Each field can hold data of a single data type. You can choose to store short text, up to a maximum of 255 characters, or long text, with a maximum character limit of just above 65,000. This is useful for comments and memos. We can also store numerical values that you would perform mathematical calculations with.

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 an incrementing serial number field that are automatically generated for you when new records are added to the table. Yes and No is a binary field. It can be yes or no, true or false, or on or off. OLE Objects is an older format, it's used for attaching files, such as images or Word documents. If you need to attach files though I recommend using the newer and 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'd rather not import files into the database you can choose to use a hyperlink data type instead 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 fields in your data tables. And the Lookup Wizard will help you create a drop down list to limit the available entries. We'll look at using the Lookup field in an upcoming movie.

In addition to the fields that describe each record's properties we'll also create fields that link tables together. Previously I've mentioned that Access stores data in related tables. Because of this each record in the table needs to be uniquely identifiable. In order to ensure that 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, and a library card number, a social security number, a checking account number, and the list goes on and on.

The reason that all these systems use serial numbers instead of just your name is so that they can guarantee an 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 is also going to be our link to finding related records in additional tables. In the related table they'll be called the foreign key. The process of breaking your data into related tables is called Normalization. Normalization rules fall into several categories called Normal Forms.

Your database is said to satisfy the First Normal Form, or 1NF, if all of the cells contain only a single value. Rather than have 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 customers that each employee has helped in the employees table. Instead we should create a new table that describes the interacts between our employees and our customers.

The Third Normal Form states that the data can be calculated or derived from other fields should not be stored in the database. We wouldn't want to store fields for employee initials for instance 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 are the foundation of your database. Creating a strong foundation as a base and then growing your database on top with additional queries, forms, and reports will mean that your database will be able to be flexible and efficient.