by David FaourWhen 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.

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.