When we create a one-to-many relationship between two entities, we copy the primary key from the entity on the one side (the parent entity) over as a foreign key to the entity on the many side (the child entity). We traditionally copy over all of the metadata associated with the primary key such as name, format and definition. The one exception where a foreign key can have a different name than its primary key is when there is more than one relationship from the same entity. To avoid having two or more data elements with the same name in the same entity, we "role name," meaning giving the foreign key a different name than its primary key.

For example, in the data model below, in Employee we have a foreign key back to Employee Type which has the same name as its primary key (Employee Type Code), and we have two foreign keys in Customer that point back to Employee. These two foreign keys are role-named to avoid having Employee ID twice in the Customer entity and to provide additional meaning as to what the foreign key represents. Primary Contact Employee ID points back to the Employee who is the primary contact for this Customer, and Initial Contact Employee ID points back to the Employee who initially made contact with this Customer.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access