Previous Topic: Additional Data Modeling TopicsNext Topic: Mutually Exclusive Relationships


More About Identifiers

An identifier of an entity type is a combination of attributes and relationships whose values uniquely identify an entity.

Each entity type must have at least one identifier, and many have only one, but an enterprise identify entities in more than one way. A salesperson use a name of a customer, while an accountant needs a number of a customer. CA Gen allows up to five separate identifiers to be specified for each entity type. Each identifier of an entity type must uniquely identify an entity.

The following illustration shows part of an Entity Relationship Diagram.

More About Identifiers

Here, a new entity type, Order Item, has been added. Although attribute names do not typically appear on Entity Relationship Diagrams, they are included in this example to help clarify the notion of identifiers.

Beginning with the entity type CUSTOMER, assume that the attribute Number is an identifier. In other words, no two CUSTOMERs have the same attribute value for Number. Further assume that while two customers can have the same name, and two customers can live at the same address, no two CUSTOMERs can have the same name and can live at the same address. The combination of Name and Address can therefore be an identifier.

So, the entity type CUSTOMER has two identifiers, as shown in the following table.

Identifier

Description

Identifier 1

Number

Identifier 2

Name
Address

Next, consider the entity type ORDER. Assuming that no two ORDERs have the same values for Number, then Number is an identifier of order.

Likewise, the entity type PRODUCT has an identifier, which is an attribute that is called Part Number.

ORDER ITEM is slightly different. Assuming that the ORDER ITEM attribute named Number is unique only within an ORDER, no value of ORDER ITEM Number can be used to uniquely identify each of its occurrences. The following illustration, for example, shows two orders, each with order item Numbers of 001, 002 and 003.

More About Identifiers (2)

However, because the value of ORDER ITEM Number is unique for a given order, you can uniquely identify each ORDER ITEM entity by the combination of its pairing with a particular order and its Number, that is, by a combination of one relationship membership and one attribute: Identifier 1 is contained on order "order item Number."

If there is a business rule that each product can appear only once on an order (that is, no two ORDER ITEMs on any one ORDER reference the same PRODUCT), another identifier emerges that requires no attributes at all.

An ORDER ITEM can be uniquely identified by the combination of its pairing with a specific ORDER and a specific PRODUCT. The identifier is therefore composed entirely of relationship memberships. As a result, the set of identifiers for ORDER ITEM is:

There are some necessary restrictions on the attributes and relationship memberships that participate in identifiers:

For example, if it is accepted that a CUSTOMER can change Address, then this should not be part of the identifier for CUSTOMER.

It also follows that an identifying relationship membership cannot be transferred. This will become significant in interaction analysis. For example, suppose that the business needs to be able to substitute a PRODUCT on an ORDER ITEM. Since the relationship membership is for PRODUCT must not be transferable, then either it should no longer form part of an identifier, or when altering the PRODUCT on an ORDER ITEM, one ORDER ITEM would need to be deleted, and another created and associated with the new PRODUCT.

Analysts familiar with database terminology might recognize that the concept of an identifier is similar to that of a key. Do not, however, be influenced by database concepts, facilities or design techniques when defining the identifiers used by business. There are important differences:

For example, instead of using is contained on ORDER as an identifier of ORDER ITEM, you could have added the identifier of ORDER (in this case, Number) as an attribute of ORDER ITEM, so that each ORDER ITEM contains the Number of the ORDER on which it appears. Since Number is not really an attribute of ORDER ITEM, but exists only to facilitate access to ORDER, it is called a foreign key.

A foreign key serves the same purpose as a relationship: to relate two entities. However, when using CA Gen in database design, a relationship membership should always be preferred as an identifier to using an attribute as a foreign key.

Explicitly including an attribute as a foreign key obscures the fact that entities of the two affected types can be related and requires that the value of the attribute be maintained explicitly by a business system to reflect the value of an attribute in the paired entity.

A relationship explicitly defines a possible pairing, so using relationships as identifiers provides a more accurate representation of the view of data for business, and allows the developer to postpone decisions on how to implement data until design is reached.

In CA Gen, relationship memberships that participate in identifiers are highlighted on the Entity Relationship Diagram with an "I" superimposed over the end of the relationship nearest to the entity type it identifies. The following illustration shows this convention assuming that "is contained on ORDER" and "is for PRODUCT" both participate in identifiers.

More About Identifiers (3)