Relationships can be classified by their
. Degree - n-ary, unary, binary, ternary
. Connectivity - "one" or "many"
. Cardinality one-one, one-many, many-many
. Direction - parent, child
. Type - identifying, non-identifying
. Existence - mandatory, optional
Not all modeling methodologies use all these classifications.
The degree of a relationship is the number of entities associated with the relationship. The general form of a relationship is the n-ary relationship. Special cases include the binary and ternary ,where the degree is 2, and 3, respectively.
Unary Relationships - a unary relationship is a relationship between the instances of a single entity type ( the rows of a single table if we refer to the after-mapping phase ).
Epocrates database design includes more unary relationships, like the one between drugs and generic drugs ( entity that in the medical community is well known and sums information related to the active chemical substance of a particular drug release ).
Binary relationships, the association between two entities is the most common type in the real world. A special case of the recursive binary relationship occurs when an entity is related to itself. An example might be \"some doctors are married to other doctors\".
Epocrates incorporates a great number of binary relationships, recursive or not. It is in fact the most natural and often encountered type or relation.
A ternary relationship involves three entities and is used when a binary relationship is inadequate. Many modeling approaches recognize only binary relationships. Ternary or n-ary relationships are decomposed into two or more binary relationships.
Epocrates ternary relationships have been also decomposed the same way into two binary relationships, and n-ary relationships have not been needed for a value of n greater than 3.
The connectivity of a relationship describes the mapping of associated entity instances in the relationship. Its values are \"one\" or \"many\". The cardinality of a relationship is the actual number of related occurrences for each of the two entities.
The basic types of connectivity for relations are: one-to-one, one-to-many, and many-to-many.
One-to-One is the type of relationship which has a connectivity of "one" in both directions. The meaning is that one ( or at most one ) instance of an entity is associated with exactly one instance of another entity. Sometimes this type of relationship is underused than either of the two other types of relationships. For database designers, one key indicator that a one-to-one relationship is when a table's fields are used only for a certain logical subset of records in that table.
One-to-Many is when a single instance of an entity is related with zero, one or more instances of another entity;the relationship is multi-valued in one direction. One row in the parent table ( parent - child distinction will be made specifically in the next paragraph ) is associated with more rows of the child table.
Many-to-Many relationships are multi-valued in both directions ( if we refer to the decomposed case of n-ary relationships into many binary ones ). Many-to-many relationships cannot be directly translated to relational tables but instead must be transformed into two or more one-to-many relationships using associative entities ( the DrugsReactions example in the associative entities desription ).
The direction of a relationship indicates the parent - child relation by means of the originating and respectively terminating entity involved in the relationship. For one-to-one relationships the parent is the independent entity, if one of the participating entities is dependent and arbitrary otherwise; in the case of one-to-many the situation is very simple from this point of view, the parent being the "one"-entity, and in the case of many-to-many relationships the parent-child is also arbitrary.
The type of a relationship makes the following distinction : an identifying relationship means that the child table cannot be uniquely identified without the parent., while in a non-identifying relationship each of the two entities are independent.
The existence criteria distinguishes between optional and mandatory relationships. If the participation of an instance of an entity is needed for the participation of the entity in the relationship, then we say it's a mandatory relationship; otherwise the relationship is optional.
