Home‎ > ‎Database‎ > ‎Database Normalization‎ > ‎

Table Relationships in Access

Table Relationships

    One of the major advantages of databases such as Microsoft Access is their ability to maintain relationships between different data tables. The power of a database makes it possible to correlate data in many ways and ensure the consitency (or Foreign_key) of this data from table to table.Establishing table relationhips is the way we draw together data from separate tables. This minimizes redundant data and maintains higher levels of data base reliability.
To illustrate, if you are designing a database that will track information about books, you may have a table called Titles, that sotores information about each book , such as the book's title, date of publication, and publisher. There are also information you might want to store about the publisher, such as the pulisher's phone number, address, and zip code. If you were to store all of this iformation in title table, the publisher's phone number would be duplicated for each title that publisher prints.A better solution is to store the publisher information only once in a separate table, Publishiers. You would then put a pointer in the Titles table that references an entry in the publishers table.
    To makesure that you data is not out of sync, you can enforce referencial integrity between the Titles and Publishers tables. Referential Integrity relationships help ensure that information in one table matches information in another. For Example, each title in the Titles table must be associated with a specific publisher in the Publishers table. A title cannot be addedd to the database for a publisher that does not exist in the database.
Types of Table Relationships
You bring information together by creating relationships among tables of your database. There are three kinds of relationships that you can create:
  • One to many relationship
  • Many-to-many relationship
  • One to one relationship
The type of relationship you chose depends a lot on how entities are ralated to each other.
To ilustrate, take a sales representative and customers as an example:
Ones sales representative has many customers, This is one -to many relationships.
Now consider and order an a product:
An order can have many products and product can appear on many orders, This is referred to as many-to many relationships.
Finally, think about and employee and salary. One employee receives one salary. This is one-to -one relationship.
     A relationship works by matching data in key columns, usually columns with the same name in both tables. In most cases, the relationship matches the
primary key from one table, which  provides a unique identifier for each row, with an entry in the foreign key in other table. For example, sales can be associated with the specifict titles sold by creating a relationship between the title_id column in the Title table( The primary key) and the Sales table ( the foreign key).
The type of relationship that is created depends on how the related columns are defined.
One-To-Many Relationships
    A one-to- many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in a table B, but a row in table B can have only tne matching in table A. For example, the publishers and Titles tables have one-to-many relationships: ecah publisher produces many titles, but each title comes from only one publisher.
A one-to many relatinships is created if only one of the related columns is a primary key or has unique constraint.
In Access, the primary key side of a one-to-many relationships is denoted by a key simbol. The foreign key side of a relationship is denoted by an infinity
 Many-to-Many Relationships
    In a many-to-many relationship, a row in a table A can have many matching rows in table B, and vice versa. You create such a relationship by difining a third table, called a junction table, whose primary key consits of the foreign keys from both Table A and Table B. For example, the Authors table and the
Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthorss table is the combination of the au_id  column ( the authorer's table's primary key) and the title_id column ( The Titles table's primary key).
One-To-One Relationships
    In a one-to-one relationshp, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the reated columns are primary keys or have unique constraints.
This type of relationship is not common because most information related in this way would be all in one table. You might use one-to-one relationship to:

  • Divide a table with many columns
  • Isolate part of a table for security reasons
  • Store data that is short-lived and could be easily deleted by simply deleting the table
  • Store information that applies only to a subset of the main table
The primary key side of a one-to-one relationship is denoted by a key symbol.  The foreign key side is also denoted by a key symbol.

The First Step is to Organize Your Data

    Before we can create a relationship between tables, we must ensure that the tables on each side of the relationship contains fields of the same data type and that they will contain matching data. If the Country is referred to in the country field of one table as United Kingdom the relationship will be meaningless if in the other  table it is referred to as U.K here must be an exact match for the relationship to work. Combo Boxes in forms and lookup fields in Tables help to ensure that discrepancies like this don not occur.

If you are interested in learning more about  Table Relationships
  Click Here for more information