There are three kinds of relationships that can exist between entities:
one-to-one
one-to-many
many-to-many
One-to-one
In databases, a one-to-one relationship is rare, but can be useful. A one-to-one relationship specifies that for each row in the primary entity there can be one and no more than one related record in the secondary entity. In a one-to-one relationship the primary key of the first entity is often the primary key of the second entity.
150
In crow’s feet notation, one to one relationships can be represented in two ways:
Figure 10: zero or one
Figure 11: Exactly one
One use for this kind of relationship is to express a class/subclass relationship. Say a database is keeping a list of different resources. The resources can be in any of several different media and the attributes to describe each media are significantly different. If you put all the attributes in the one Resource entity, each entry will have several nulls for the attributes it doesn’t need. To solve this you can break the Resource entity into several one-to–one relationships.
Resource
PK ResourceKey ResourceTitle ResourceType
Video
PK,FK1 ResourceKey VideoFormat VideoDateReleased VideoLength
Book
PK,FK1 ResourceKey BookPublisher BookYear BookCity BookISBN
Magazine
PK,FK1 ResourceKey MagazineName MagazineIssue MagazineVolume MagazinePage
Figure 10 one-to-one relationship
151
The entities above don’t include all the relevant attributes, just enough to show the relationship. Notice that each entity has the same primary key. That means each resource will appear once in the resource table, and exactly once in one of the resource type tables. To get a clearer sense of this relationship, look at the following tables based on this design.
Figure 11: Resource Table
ResourceKey ResourceTitle ResourceType
235091 Database Programming with ADO Book
244088 PhotoShop Basics Video
200211 Data Binding with LINQ Magazine
202883 Relational Algebra Book
Figure 12: Book Table
ResourceKey BookPublisher BookYear BookCity BookISBN
235091 Westland Press 2005 San Francisco 123-77-6576-X 202883 PL University Press 1998 Seattle 234-11-2345-0
Figure 13: Magazine Table
ResourceKey MagazineName MagazineIssue MagazineVolume MagazinePage 200211 Visual Studio
Magazine
March 2008 3 76
One-to-one relationships are also sometimes used as part of a security structure. A single entity may be broken into two entities. One will contain publicly viewable content and the second private content. For example, an employee’s information might be broken into two entities. The first one contains non sensitive content such as the employee’s name, department, business phone and position title. The second table contains sensitive material such as the employee’s social security number, home address, home phone and salary information. There is a one-to-one relationship between the tables. Each one employee has exactly one related record in the private table.
152 Employee
PK EmployeeKey EmployeeDept
EmployeeBusinessPhone EmployeTitle
EmployeePrivate
PK,FK1 EmployeeKey
EmployeeSocialSecurity EmployeeAddress EmployeeSalary
Figure 124: One to One
It should be noted, this is not necessarily the best way to deal with security issues. There are many ways to allow the public aspects of the Employee entity to be accessed while protecting the private
information. Creating a view or using a stored procedure (See Chapter Seven) to control which columns a user can access is generally a better strategy.
One-to-Many
Most of the entities in any relational database will have a one-to-many relationship. A one-to-many relationship means that for each record in the primary entity there can be many associated records in the secondary or child entity. There are two crow’s feet symbols for one-to-many relationships:
Figure 125: Zero or More
Figure 126: One or more
For an example of a one-to-many relationship consider the relationship between a Department in a business and its employees. Each Department can contain zero or more employees. Each Employee belongs to one department.
153 Employee
PK EmployeeKey EmployeeLastName EmployeeFirstName FK1 DepartmentKey Department
PK DepartmentKey DepartmentName DepartmentPhone DepartmentRoom
Figure 127: One-to-many relationship
You only enter the department information once in the department table. You use the primary key to link the table to a child table by repeating it in that table as a Foreign Key. The foreign key can repeat as often as needed in the child table.
Figure 128: Department Table
DepartmentKey DepartmentName DepartmentPhone DepartmentRoom
ACC Accounting (206)555-1234 SB201
IT Information Technology (206)555-2468 NB100
Figure 129: Employee Table
EmployeeKey EmployeeLastName EmployeeFirstName DepartmentKey
FB2001D Collins Richard IT
BN2004N Faulkner Leonore IT
NC2004M Brown Carol ACC
LL2006O Anderson Thomas IT
Things to Watch Out For
It is important that you do not create a “cross relationship.” There is a temptation to think that because a department contains employees, the department entity should contain a foreign key for employee.
154
Employee
PK EmployeeKey EmployeeLastName EmployeeFirstName FK1 DepartmentKey Department
PK DepartmentKey DepartmentName DepartmentPhone DepartmentRoom FK1 EmployeeKey
Figure 30: Cross Relationship Error
Doing this will create an impossible situation. In effect, a Department will only be able to contain a single employee. The second employee will create a conflict with the DepartmentKey which cannot repeat.
This is a fairly common error among novice designers. It often isn’t discovered until the attempt to enter data into the tables fails.
Many-to-many
Many–to-many relationships are common and they are legitimate relationships in logical terms, but no database can implement them. A many-to-many relationship means that each record in the primary entity can have many related records in a second entity and each record in the second entity can have many related records in the primary entity.
The symbol for a many-to-many relationship has a crows foot on both sides of the relationship:
Figure 31: Many-to-Many Relationship
Visio doesn’t contain a symbol for this relationship.
155
For an example, consider the relationship between Subscribers and an entity designed to store a list of various magazines. Each customer can subscribe to many magazines and each magazine can be subscribed to by many customers. That creates a many-to-many relationship.
Subscriber
PK SubscriberKey SubscriberLastName SubscriberFirstName SubscriberAddress SubscriberCity SubscriberState SubscriberPostalCode Magazine
PK MagazineKey MagazineName MagazinePrice
Figure 32: Many-to-Many relationship
In any RDMS a many-to-many relationship must be resolved into two one-to-many relationships. This is done by creating a linking entity. In this case the Magazine and the Subscriber are linked by a
Subscription entity. A subscriber subscribes to one or more magazines. A magazine can be subscribed to by zero to many subscribers. Often, as in this case, creating the linking entity reveals a forgotten or undiscovered entity, Subscription is more than a linking entity. It is a legitimate entity with attributes of its own.
156
Figure 33: Linking table
Below are tables that show how these entities would be translated into a database. These are, of course much simplified. A real database would contain many more columns of essential information such as the subscription length, the magazine type (is it weekly, quarterly, etc), the magazine publisher information, etc. Also for the subscribers it would be necessary to distinguish between the billing address and the shipping address, since they are not necessarily the same.
Figure 34: Magazine Table
MagazineKey MagazineName MagazinePrice
TM2K1 Time 35.50
NW2K1 Newsweek 36.40
Figure 35: Subscriber Table
SubscriberKey Subscriber
157
Figure 36: Subscription Table
SubscriptionKey MagazineKey SubscriberKey SubscriptionStartDate
1004 TM2K1 4333 1/15/2009
1005 NW2K1 4333 1/15/2009
1006 NW2K1 4231 2/1/2009
1007 TM2K1 5344 2/15/2009
Sometimes, however, the linking entity only serves to resolve the many-to-many relationship. Consider the relationship between authors and books. Each book can have several authors and each author can author several books. This relationship can be resolved with a linking table as in the figure below:
Book
Figure 37: Linking table 2
It is not uncommon for a linking entity to have a composite key made up of the foreign keys from the two tables whose relationship it resolves. One other note: you may have noticed in the earlier diagrams most relationships are represented by dashed lines. The relationships above and the one-to-one relationships are represented as solid lines. Visio distinguishes between identifying and non-identifying
relationships. An identifying relationship is one where the foreign key in the child table is also a part of the primary key of that child table. For instance, AuthorKey is both a foreign key and part of the composite primary key in the Linking Entity—an entity which