Building Strong Relationships
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.