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.
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.
One to many relationships are going to be very common. Some examples follow.
Customers to Orders
Suppliers to products
Manager to employees
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.
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.
following example shows Customers to Products.