Identifying Attributes › Identifying the Attributes for Each Relationship Type

Identifying the Attributes for Each Relationship Type

Some data relationships have attributes that describe meaningful non-key information, others do not, as described below:

• A one-to-one relationship sometimes carries non-key data. An example of a one-to-one relationship is LEADS, where the business rules state that each project has a single leader, and one employee may be project leader for only one project. For this relationship, it may be important to carry the dates when the project leader begins and ends leadership responsibility.
• A one-to-many relationship typically does not carry any non-key data. The relationship LOCATES, for example, simply relates an employee to a particular office. There is no additional information about that relationship that is required by the business functions.
• Many-to-many relationships usually do carry non-key information required by the business functions. EXPERT IN, for example, carries information about a particular employee's level of expertise with a particular skill.
• A self-referencing structure is a special kind of many-to-many relationship that sometimes carries non-key data. For example, in the Commonweather Corporation, the relationship between workers and managers is defined as REPORTS TO and the relationship between managers and workers is defined as MANAGES. Non-key data about the REPORTS TO and MANAGES relationships might be the dates on which a relationship began and ended.

List the attributes

As you determine the attributes that are associated with a particular relationship, you should list these attributes, as follows:

 IS LOCATED                              BELONGS TO 

     * OFFICE CODE                           * DEPT ID
     * EMP ID                                * EMP ID

 LEADS                                   HEADS      

     * PROJECT CODE                          * DEPT ID
     * WRKR EMP ID                           * HEAD EMP ID

 WORKS ON                                IS POSITIONED   

     * PROJECT CODE                          * EMP ID
     * WRKR EMP ID                           * JOB ID
                                               SALARY
                                               COMMISSION PERCENT
EXPERT IN                                      BONUS PERCENT
     * EMP ID                                  OVERTIME RATE
     * SKILL CODE                              START DATE
       SKILL LEVEL                             END DATE
       DATE ACQUIRED

CHOOSES                                  REPORTS TO  

     * EMP ID                                * WRKR EMP ID
     * HEALTH PLAN CODE                      * SUPR EMP ID
     * COVERAGE TYPE                           WRKR BEGIN DATE
                                               WRKR END DATE

INSURED BY                               MANAGES  

     * EMP ID                                * SUPR EMP ID
     * LIFE PLAN CODE                        * WRKR EMP ID
                                               SUPR BEGIN DATE
                                               SUPR END DATE

 PAYS FOR HOSP                           SPECIFIES  

     * HEALTH PLAN CODE                      * HEALTH PLAN CODE
     * COVERAGE TYPE                         * COVERAGE TYPE
     * HOSPITAL CLAIM ID

 PAYS FOR PHY                            PAYS FOR DENT 

     * HEALTH PLAN CODE                      * HEALTH PLAN CODE
     * COVERAGE TYPE                         * COVERAGE TYPE
     * NON-HOSPITAL CLAIM ID                 * DENTAL CLAIM ID