Relationship Keys

The construction of a relationship requires that you designate at least one attribute in each entity as a relationship key. Relationship keys are necessary so a relationship can be resolved. For example, the toPhoto relationship which relates a Person entity to a PersonPhoto entity, uses two relationship keys: personPhotoID, the source key in the Person entity, and personPhotoID, the destination key in the PersonPhoto entity.

Figure 5-1 shows the PERSON table’s columns.

Figure 5-1 Foreign key for PersonPhoto in Person table

When Enterprise Objects resolves this relationship, it creates a join table by looking up the PERSON_PHOTO_ID key in the PERSON_PHOTO table. In Figure 5-1, the row with PERSON_ID = 2 has a value of 1 in the PERSON_PHOTO_ID column. The relationship specifies that the PERSON_PHOTO_ID column in the PERSON table resolves to the PERSON_PHOTO_ID column in the PERSON_PHOTO table. As shown in Figure 5-2, the row with PERSON_PHOTO_ID = 1 in the PERSON_PHOTO table holds binary data that represents a photo.

Figure 5-2 PersonPhoto primary key in PersonPhoto table

There are some general guidelines when choosing which attributes to use as relationship keys. In to-one relationships, the destination key must be a primary key in the destination entity. In to-many relationships, the destination key is usually a foreign key in the destination entity (which is most often a copy of the source entity’s primary key). The source key or foreign key should emulate the destination key in that the data types must be the same and the names should be similar.

So, in the previous example, PERSON_PHOTO_ID is the primary key for the PERSON_PHOTO table and it is a column of type int. In the PERSON table, PERSON_PHOTO_ID is a foreign key that is of the same type and name as the primary key it maps to in the PERSON_PHOTO table.

When you use relationship keys to express an affiliation between two entities, keep in mind these general rules:

• For to-one relationships, the source attribute is a foreign key in the source entity while the destination key is the primary key of the destination entity.
• For to-many relationships, the source attribute is the primary key in the source entity (but it can also be a foreign key in the source entity) while the destination key is a foreign key of the destination entity.

If you have consistency checking enabled in EOModeler, it warns you if any to-one relationships in your model have destination keys that are not primary keys.