Developing a Conceptual Model › Identifying Relations › Step 2: Selecting Primary Key (Unique Identifier) for an Entity

Step 2: Selecting Primary Key (Unique Identifier) for an Entity

This step provides information for selecting primary keys for an entity.

• Primary Key or Unique Identifier—An attribute or group of attributes assigned to an entity to uniquely define an instance of the entity.
• Foreign Key—An attribute or group of attributes of an entity that connects this instance with an instance of another entity. In this way it defines the relationship between two entities. It consists of attributes defining the primary key of the related entity.

A primary key can either be a single attribute, a relationship, or a combination of attributes and relationships. Each entity must have a single primary key. The entity can have several alternate keys.

For example, the Customer entity in your Order Entry model may have several instances representing different customers (Customer A, Customer B, Customer C). An instance is equivalent to a record among other customer records in your Customer database file. The primary key uniquely identifies each record.

You select Customer Code as the primary key for Customer entity:

ENTITY                   CUSTOMER

Attributes   K       Customer code

                   Customer name

                   Customer address

                        Customer credit limit

                   Customer account balance

Although you could have chosen Customer Name as a key instead, a code is a better choice to ensure uniqueness. It also allows the customer's name to change without having to create a new customer record.

Note: Avoid using keys whose values can change.

A primary key can also be used to implement the relationship between entities by forming a link between the entities.

For example, in the case of Customer and Order entities, the link is Customer Places Order. The relationship between the two entities Customer and Order is recognized by the presence of the Customer Code in the list of attributes of the Order entity.

Example:

CUSTOMER                          ORDER

K       Customer code           K       Order Code

Customer name                 Customer code

Customer address                   Order quantity

Customer credit limit             Order line

Customer account balance              Vendor ID

                               Product Code

Customer Code is the primary key of the Customer entity; it does not play a role in identifying orders. However, Customer Code is needed in the Order entity to identify which customer placed the order.

In this case, the Customer Code is used to represent the relationship between Order and Customer. It becomes a foreign key in the Order entity file. The relationship means a single customer belongs to this order.