In the blog about designing binary relationships, I briefly described crow’s foot notation. Crow’s foot notation is a design technique that visually explains our relationship between tables.
In order to understand crow’s foot notation, we must first understand the basics of database design. I recommend you read the previous blogs listed to familiarize yourself with all of the database relationship concepts:
Cardinality refers to the relationship between a row of one table and a row of another table. The only two options for cardinality are one or many.
Example: Think of a credit card company that has two tables: a table for the person who gets the card and a table for the card itself. A row from the card_holder table would have a relationship with a row in the card table because the card_holder holds the card.
Here is an example of a one to one relationship.
If a card_holder can have only one card this would be a one to one relationship. If the card holder can have multiple cards it would be a one to many relationship: Here you can see the previous example updated with a one to many relationship.
In a one to one relationship we have a connection from one row of the first table to one row of another table. In a one to many relationship we have a connection from one row of the first table to one or multiple rows of the other table.
We can illustrate that using crow’s foot notation:
One is illustrated with a vertical line., Many is illustrated with 3 lines. Think of many as a crow’s foot!
As cardinality is the maximum number of connections between table rows (either one or many), modality is the least number of row connections! Modality also only has two options, 0 being the least or 1 being the least.
Another way to think of this is not required or required. If we have a modality of at least zero, there doesn’t have to be a connection at all (nullable). If we have a modality of at least one, then we have to have that connection (not null).
This can be a bit confusing so let’s look at the credit card company example:
In the card table we have a column called holder_id. This has a connection to the card_holder table because a credit card is owned by somebody who will use it…make sense? Now, if the modality is 0 or more, than we can have a row without a holder_id value. If the modality is 1 or more, than we must have a value in the holder_id column.
Modality of 0 or more = A card is not required to be held. This means that the table not only holds active cards, but it may hold cards that are not currently held by anybody. This is called a nullable column because it accepts an empty field.
Modality of 1 or more = A card is required to be held. This means that a card with no owner would not be able to be put in this table. This is said to be a not null column because it does not accept nulls.
We can also draw modality in our crow’s foot notation. If the modality is zero or more, we put a little circle right beside the cardinality. If the modality is one or more, we put a vertical line next to the cardinality:
Here is an illustration of modality. Notice that we can put both cardinality and modality on the same line. Modality goes on the inside and cardinality goes on the outside.
Modality goes on the inside, cardinality on the outside.
Cardinality and modality together gives us four possible combinations:
Here are all of our possibilities.
Let’s apply this to the credit card example:
Here are all of our options. The only thing to to take into consideration is the fact the we call the person a card holder even if he or she doesn’t currently have a credit card. It may be best to either change the title “card holder” to “customer” or only use “at least one” to ensure that a a card holder is currently holding a card.
This can go both ways. Let’s look at an example of a person taking a class in a college. Here are our rules: A class has to have at least 1 person but is not limited to just one, and a person can take at least zero classes but is not limited to just one:
Here you can see how we can put both examples on a single line to complete the relationship diagram.
- Cardinality is the max.
- Cardinality is one or many.
- One is illustrated with a vertical line.
- Many is illustrated with a crow’s foot.
- Modality is the least.
- Modality is 0 or 1.
- 0 is a circle
- 1 is a vertical line.
- Modality goes on the inside of the line while cardinality goes on the outside.
- Modality illustrates whether a column is null or not null.