Joins - Links between Tables in a Database

Joins- Links Between Tables

To retrieve information from multiple tables, there must be a link between the tables. This is commonly known as the key field. It is a unique field within each record. For example, in our telephone address book example, the key field would be telephone number. For Access or other database program to link two tables together, it must create a link between these key fields which must be of the same data type in both tables.

Joins

There are several types of links or joins.

Equal join pulls records that have a match in both tables.
For example, if you were pulling information from a Customer Table and an Order table, if Customer A was in your customer table but had not placed an order, no information about Customer A would display but the information for Customer B would print since there is a matching record in both tables.

Left join pulls all records from the left (first) table if there are no matching records in the right table. In this case Client C would not display since he cannot be found in the order table. Conversely, if you were pulling from the right table, no information about Client D would appear since that company is not in your Client table.

Right join pulls all records from the right table and only matching records in the left table.

If tables in a query aren't joined to one another, either directly or indirectly, the database doesn't know which records are associated with which, so it displays every combination of records between the two tables. Therefore, if each table had 10 records in it, the query's results will contain 100 records (10X10). This result set of every possible combination is called a cross product or Cartesian product. This type of query can take a long time to run.

The point of this explanation is that you must consider what information you actually want and then decide upon which join to use. Equal joins are the most common however if you do need to use an outer join, make sure you place the tables in the correct order.

What is a Database?

What is a Database?


A database is an organized set or collection of related data that is stored in a file. The data in a database is entered in as records that consist of items or fields of data. Everyone has a database- take a look at your address book. An address book is a set of related information about a person (his name, telephone number and perhaps their address, fax number etc.). It is also organized - it is sorted alphabetically by name.


Each record is essentially a row of related information. Each field within the record contains information that pertains to that record. For example, a telephone directory can be considered a database. Each record has specific and unique information. The following fields or columns of information would typically be found in a telephone directory: first name, last name, address, and city and telephone number.

The following example shows a sample database:

Last Name, First Name, Address, City and Telephone are columns within the database table and are called Field Names. In other words, they identify the type of data shown in the columns below. First Name is the field name while John, Joan and Nancy are fields within individual records. Each row or record holds unique information. Only John Smith lives at 302 Milton Street in Indianapolis and has the telephone # 876-2256. In this case, the unique field would be telephone number.

A relational database allows you to create smaller and more manageable files that can be joined to extract or compare data on an as needed basis because the data in one file is linked to another via a unique field.

A database management system (DBMS) controls the structure of a database and access to the data. In other words, it creates, manages and protects that data as well as providing access, which also allows the user to integrate and share data across several files.

I thought I would change topic for the moment and talk about some important concepts to understand before creating a database.

I am off on vacation starting tomorrow so I probably won't be blogging. Have a wonderful 4th of July.

Database Design


The core of good database design is planning. Before actually creating a database, you should have a good idea of the kind of data your database will contain and how that data should be broken down. It is recommended to plan your database out on paper before beginning the creation process.

When creating databases, there are rules that most designers follow which help them create consistent, efficient well thought-out databases. This set of rules is called normalization, which dictates that your database tables will eliminate inconsistencies and maximize efficiency. The goal of normalization is to reduce data to its simplest structure with minimum redundancy and maximum data integrity.
Some of the important goals of normalization are:

• All fields should be broken down so that data cannot be divided further. For example, the field Name could be broken down further to last name, middle name, and first name.

• Each table must have one unique key field called a primary key. That is to say, there must be one field that identifies a record and does not allow duplicates. An example of this would be a social security number or customer number.
• All fields must directly refer to the primary key. For instance, in a customer table, you would only include information related to that customer, such as name, address, etc. You would not include a field called “Product name” in a customer table.

• A field cannot contain more than one value.

Thus, a normalized database stores each piece of information in its own table, all fields are broken down to their lowest possible level and each piece of information can be referred to by the its primary key. This is an important concept and is the entire premise behind a relational database.