many to many relationships

This content is an extract from GetAhead in Business Analysis - the FULL course is in eBook and multimedia CD-Rom format.

Why many to many relationships are invalid.
It is normally the case that what appear to be many to many relationships can, on closer inspection, be broken down into new entities and relationships. This fact, combined with the fact that very few database management systems can implement them, are the main reasons for eliminating many-to-many relationships. Resolution of many-to-many relationships is normally carried out by the introduction of a link entity. This link entity holds the data associated with the relationship between the original entities and both of the original entities are deemed to be masters of this link entity. Consider the example of patients booking appointments to see doctors. In large practices it is becoming increasingly common for each patient to see whichever doctor is available. Patients are therefore no longer directly linked to one doctor and it is apparent that each doctor will see numerous patients. Initially we have identified a many-to-many link, as shown. However an efficient medical administration system will need to be able to uniquely identify each instance of an appointment between a given doctor and a specific patient. This would enable the practice to notify all those patients who would be affected by any changes, cancellations etc. In this example there is a known term for the appointment between a doctor and a patient. This is called a consultation. It is accurate to introduce this term as the new link entity. Each occurrence of the entity type consultation will now hold specific data and information that uniquely identifies it. It may be that the link entity will be a previously overlooked entity - in which case the naming of it should be straightforward; as in the example given. If the name of the link entity is not immediately apparent then it should be given a meaningful name.

Adding a Link Entity.
Consider this relationship, taken from the hospital diagram that was introduced earlier. In this relationship a patient may receive many drugs and a drug may be given to many patients. Resolve this relationship by adding a link entity, give the new link entity a meaningful name and try to identify the main attributes of this entity. When you have drawn your own solution for this, go to the next page to see the suggested solution. Investigation of this relationship should have led to the identification of the link entity ‘prescription’. In this example the hospital rules state that a separate prescription must be written for each drug. The relationships shown on this diagram accurately reflect the information supplied. Each patient may receive one or more prescriptions, but each prescription must relate to one and only one patient. Each drug may belong to one or more prescriptions, but each prescription must detail one and only one drug.

Examining 1:1 Relationships.
A one-to-one relationship often indicates a common underlying entity. Where this is the case the entities are merged - the primary key of the new entity may be the primary key of one of the original entities. Alternatively, a new key can be created if necessary. In this example, the entities ‘person’ and ‘driving license’ have been merged to create a new entity called driver. The attributes of driver are those of both ‘person’ and ‘driving licence’. An entity created by such a merger may often contain optional attributes.

Merging Entities.
This one-to-one relationship is taken from the hospital diagram – as shown earlier. Resolve this relationship by merging the two entities and try to identify the main attributes of this entity. When you have drawn your own solution for this, go to the next page to see the suggested solution. It should be apparent that the medical record entity could be merged with the patient entity as all of the information on the medical record relates to the patient. The medical record also duplicated many of the attributes already stored for the patient. The only attributes that were unique to the medical record were the medical record number and the medical details. By merging these entities we have added the medical details to the patient entity and the medical record number is now redundant.

Resolving 1:1 Relationships.
If the entities cannot be merged, then the relationship will probably have to be resolved into a one-to-many relationship. In this case the analyst must decide which entity is the 'master' and which is the 'detail'. If the relationship is time dependent and one entity exists before the other, then the one created first is deemed to be the master. When investigating the time dependency of entities refer to the relevant entity life histories. If the time dependency is still unclear it may be helpful to consider the optionality of the relationship: If the relationship is optional at one end, then make the optional end the master as this is the entity which would normally be created first. If the relationship is optional at both ends It can be difficult to determine the creation sequence. If no time dependency can be established, arbitrarily make one entity the master and the other the detail. If the relationship is mandatory at both ends this implies that both entities were created simultaneously and may indicate an instance of the only valid one-to-one relationship the 'super-type and subtype entities'.

This content is an extract from GetAhead in Business Analysis - the FULL course is in eBook and multimedia CD-Rom format. TOP