Building Strong Relationships

Building Strong Relationships


February 28, 2001

by David Faour

When we moved from the era of the flat-file into the realm of the RDMBS, the concept of relationships was introduced. Employees have offices, parents have children, and customers order products. Those three examples illustrate one to one, one to many, and many to many relationships.  Let's take a look at how to identify, diagram, and implement these relationships.

One to One

One to one relationships exist when a row of data from Table1 has only one matching row in Table2, and that matching row in Table2 matches only the original row in Table1.

Examples are one employee may have only one office, and one office may belong to only one employee. One house has only one address, and one address has only one house.


The employee to office relationship modeled above indicates a few things. First, that an employee may have only one office and vice versa. Second, it indicates that an employee is not required to have an office, neither is an office required to have an employee. The circles, or zeroes indicate that it's optional to have zero. And the straight line indicates one. Technically, this is a zero or one to zero or one relationship

One to Many

One to many relationships exist when a row in Table1 is permitted to have many matching rows in Table2. For example, a customer may make many orders. Optionally, a customer may make no orders, or only one order.


This diagram illustrates that Customers may order zero, one, or many times. It also shows that a single order can belong to Only one customer.

One to many relationships are going to be very common. Some examples follow.

Customers to Orders

Suppliers to products

Manager to employees


Also demonstrated is the concept of the Foreign Key. A foreign key is a non-key attribute in a table such as Orders.CustomerID that links to the primary key in another table such as Customer.CustomerID.

 

A many to many relationship exists with items such as orders to products, professors to courses, students to courses. Each student can take many courses, and each course can have many students.

These types of relationships are resolved by an associative entity, also known as a cross-reference table or intersection table.

The following model will show how to resolve Orders to products.

As you can see, the proper way to handle a many to many relationship, assuming a logical model in 3NF, is to break the relationship down into a series of one to many relationships.

The following example shows Customers to Products.


Customers may have many orders, but each order belongs to one customer. Orders are required to have one or more order details, and each order detail may belong to only one order. Each order detail may have only one Product, and products may appear on zero, one, or many order details.