Data Model

One-to-Many Relationships

          By far the most common relationship is a one-to-many relationship. Several tuples in the secondary entity relate to a single tuple in the primary entity. The relationship is between the primary entity’s primary key and the secondary entity’s foreign key.

One-to-one relationships connect two entities with primary keys at both entities. Because a primary key must be unique, each side of the relationship is restricted to one tuple. For instance, an Employee entity can store general information about the employee.

A design element that leverages the one-to-one relationship is the supertype/subtype relationship. This relationship connects a single supertype entity with multiple subtype entities.  This design is useful when some objects share a majority of attributes but differ in a few attributes, such as customers, vendors, and shippers. All three share name and address attributes, but each has specific attributes.

In a many-to-many relationship, both sides may relate to multiple tuples on the other side of the relationship. For instance, an order may have multiple items, and each item may be sold on multiple orders.