Relations

A relation describes the relationship between two entities. It contains two identifiers. The identifier of Entit A asnd the identifier of Entity B.

The Relationship of Entity A and Entity B, shown in the preceding page, has four possible solutions.

The first two implementations are where the primary identifiers of Entity A and Entity B are joined as a non-atomic, two element, primary identifier of the Relation. Each element is a foreign identifier in the Relation. The foreign identifier sequence is based on which retrieval path, from Entity A or Entity B, is used most often by the application. In the physical implementation, two indices are built in the DBMS, one over each element.

The second two implementations are where one of the primary identifiers of Entity A or Entity B is used as an atomic, single element identifier and the other primary identifier from Entity A or Entity B is used as an attribute. This solution is used for entities that are only joined in rarely-run batch processes, such as end-of-month reports where fast retrieval is unnecessary. DBMS overhead is reduced since only a single index is built.

How do you know which solution is the best? Which solution should be built and deployed?

You don't. It's to early in the process. Don't get ahead of yourself. For now, know that you have four possible solutions. They would all work, but one works better than the other three.