Database cardinality and optionality relationship

database cardinality and optionality relationship

Relationship cardinality answers a fairly simple question: Given an entity A, how many instances, at most, of another entity B might potentially be linked to a. This image depicts the Customers Place Orders ERD. Name; Cardinality; Optionality; Percentage of entities participating in pairings that are. In the blog about designing binary relationships, I briefly described crow's foot notation, we must first understand the basics of database design. Cardinality refers to the relationship between a row of one table and a row of.

Given an entity A, how many instances, at most, of another entity B might potentially be linked to a given instance of A? The answer could be "zero" in which case there's no relationship between the entitiesbut in general the answer is either "one" or "many" in other words, more than one. It can be useful to know one additional piece of information about a relationship. This is what's called the relationship's optionality.

This information is not strictly necessary for a complete ERD, but it can be very useful information to gather. Cardinality allows you to answer the question "How many? Optionality, by contrast, answers the question "How few? Is it permissible to have a customer with no recorded orders?

Answering these questions often reveals important information about business rules and workflow in the intended system. The answers probably won't mean designing your data structures any differently, but could be quite important when it comes to data validation and workflow in the finished system.

Optionality in Many-to-Many Relationships Suppose that you have a database system designed to track information about college students including their high school transcripts and grades from other schools, sports, student organizations, and classes.

Two of the entities in this system are a Student entity of course and a Class entity. The relationship between these two entities is many-to-many. So you know that one student record can potentially be linked to many class records, if a student is enrolled in many classes. But is there a minimum number of classes that a student must be associated with at any time? Put differently, should it be permissible to have a student record in the system that's not associated with any class records?

Your first instinct might be to say no. After all, students have to take at least one class, don't they? But that's not quite the question that's being asked.

The question is not whether all student records eventually have to be associated with at least one class record. The question is, must a student record, always and at all times in its existence, be associated with at least one class record? And the answer to this question is clearly no. New students, or transfers, are not associated with class records until they first enroll for classes.

Determining Cardinality and Optionality

But their records might be entered into the system weeks or even months prior to enrollment. So the answer here is that it's acceptable for student records to have no associated classes. Here's how to show this rule in the ERD notation. Take a look at Figure 5. In addition to the crow's-foot, which shows the fact that, potentially, multiple class records can be associated with a single student, we now also have an open circle to indicate that it's all right for a student to have no associated class records.

Entity-relationship notation for the Student-Class relationship, with optionality shown at the Class side.

Relationship Optionality

In an ERD in which you fully diagram all the optionalities, each end of a relationship line has two notations: The graphical notation closest to the entity specifies the cardinality, and the one farther away specifies the optionality. So the way to read the notations at the Class end of the diagram in Figure 5. What's the fewest number of students with which a class may be associated?

database cardinality and optionality relationship

Well, before anyone enrolls for the class, the answer is zero. And what's the largest number of students with which the class may be associated? It doesn't matter whether the answer is 10 or As long as the answer is more than 1, you can just use the generic term "many" again.

The Student end of this relationship is drawn as shown in Figure 5. Entity-relationship notation for the Student-Class relationship, with optionality shown at both sides. This diagram now provides a bit more information than a plain, unadorned ERD would have. Now you've specified not only that the relationship of Class to Student is many-to-many, but also that it's permissible to have classes with no associated students, and students with no associated classes.

This set of questions about optionality applies equally to all relationship types.

database cardinality and optionality relationship

But each relationship type has some optionality scenarios that are, for lack of a better term, more typical of that relationship type. In the sections that follow we'll examine some of these typical scenarios.

Nothing in the sections that follow, though, should suggest that a given relationship type will never exhibit other types of optionality. Optionality in One-to-Many Relationships In dealing with one-to-many and, by extension, with many-to-one relationships, there are two broad scenarios, which can be called "loose binding" and "tight binding. Loose Binding Consider the relationship between the entities Customer and Sale.

It seems to be one-to-many: One customer may have many sales. What about the optionality? Take it one side at a time. Is it permissible to have a customer with no associated sale records?

This is a business rules question; in many business scenarios, it seems likely that this would be all right. Until people actually buy something, they're better described as prospects than customers, but we probably still want to allow them in the database without a sale.

So a customer can have anywhere from zero to "many" sale records. Now look from the other side.

database cardinality and optionality relationship

One to one 1: For example, 1 man is married to 1 woman. One to many 1: For example, 1 manager manages many employees, each employee is managed by 1 manager. Many to many m: For example, Each students take many modules, each module is taken by many students.

Attributes to relationships

How many is many? If it's 0, 10 orthe way you implement the relationship is the same. Each relationship can be optional or mandatory for each entity. This gives three types for binary relationships: Optional for both entities Optional for one entity, mandatory for the other Mandatory for both entities Putting all of this together there are 6 binary relationships that we need to know how to implement: It is because the many that it is associated with could be 0, 10, etc.

The point is the 0. The way we implement the relationship means that the one side could be associated with zero on the many side - it's got optioanlity built in. This will become clearer when we start resolving relationships.

For now, the important thing is the meaning of the three terms, degree, cardinality and optionality. These are dictated by the 'Enterprise rules'. Nor does it matter what happens outside of our database design.