7

I know that there are several symbols to express the cardinality between entities.

But is there a pragmatic common way to express cardinality in ascii?

Example: User -- Group. One user can have several groups and One group can have several users.

I looked at this page: https://en.wikipedia.org/wiki/Cardinality_(data_modeling)

For example: one-to-many "order" ←→ "line item"

Here are some graphical ways to express cardinality:

https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#Cardinalities

What is the most common way to express cardinalties?

Is there a standard/rfc for this?

4
  • 1
    Table1 n <-----> m Table2? Or maybe Table_1 (n) <-----> (m) Table_2?
    – Vérace
    Commented Feb 27, 2019 at 10:29
  • @Vérace for N:M it is easier, since it does not hurt if you mix N with M. I updated the question and added "order" <--> "line item". Here mixing 1 with N does hurt.
    – guettli
    Commented Feb 27, 2019 at 12:36
  • n-n or 0..n - 0..n or 0..n : 0..n I sometimes use. Commented Feb 27, 2019 at 12:38
  • @ypercubeᵀᴹ please write your comment as answer and provide an example for order <--> line-item.
    – guettli
    Commented Feb 27, 2019 at 12:48

3 Answers 3

5
+50

The UML way (unified modeling language) is to depict the cardinality with <lower bound> .. <upper bound>, being each bound a non-negative integer and * to depict potentially infinite. You can find this on the point 7.5.4 Notation inside the documentation linked before.

However this applies generally to many different modeling diagrams and not exclusively to ERDs. When writing relationships between tables on a relational manner, the most widely used notation I found was N:M (or N to M, N,M). The way you describe the relationships is important too, since there are actually 2 cardinality/multiplicity parts that you need to specify. Check the following examples:

  • Order with line items. An order must have at least 1 line item and can potentially have infinite. A line item can't exist without an order and only belongs to 1 order. This would be implemented on relational databases with Order and LineItem table, with LineItem having a not null foreign key against Order.

    Order to LineItem: 1 to N
    LineItem to Order: 1 to 1
    
  • Employee with itself (chain of command relationship). A particular employee might have no boss (the top chief) or only 1. A boss can have no employees below, or an infinite amount. This would be implemented on relational databases with one table Employee, which has a foreign key with itself which can be null.

    Boss to Employee: 0 to N
    Employee to Boss: 0 to 1
    
  • Person with itself (father and mother relationships). Every person has one father and one mother. Each parent might not have children, or have many (let's say for example fathers can have infinite amount while mothers can have up to 10). The way to implement this on relational databases is through 2 fields on the same table Person, that links with itself through nullable foreign key (partial enforcement).

    Person to father: 1 to 1
    Father to person: 0 to N
    
    Person to mother: 1 to 1
    Mother to person: 0 to 10
    

Most of these relationships are usually simplified by just mentioning the upper bound of each side. So for the boss to employee it would be 1 to N and it's read as "a boss can have up to N employees, an employee can have up to 1 boss".

There are some caveats with these expressions on the implementation, depending on each database you use.

On the order example, if an order must have an existing line item when creating the record and a line item must have an existing order, then how can you load them without failing? On relational databases, first you load the Order and then it's line items, so at one point there is an order that doesn't have a line item, but this notation expresses not how it should be implemented but how these entities are related on a business level.

On the parenthood example, at some point there will be no info about a parent, otherwise you would have infinite amount of parenthood levels. So the actual implementation of the top level would be Person to father: 0 to 1.

As for the graphical example, I find the crow's foot notation the simplest to use, but there is no universal standard for this.

1
  • Is there a reason why you link from UML to the beta page? Here is a newer version (2.5.1): omg.org/spec/UML
    – guettli
    Commented Mar 8, 2019 at 9:42
2

I simply say:

order : item is 1:many

student : class is many:many

There is also 1:1, but it is usually poor design to have two tables in a 1:1 relationship.

There can be variants with "0", but that may be more confusing than beneficial.

1

I usually work with crow's foot notation. In ASCII art I'd use the hyphen and greater than / less than. For example

A customer places many orders
Customer -< Order

A user is in many groups; a group contains many users
User >-< Group

Relationship names can be inserted within the hyphens, or within square brackets
Customer --[Places]--< Order

If precise cardinality is important it can be appended to the ends
Child (1) --[Loves]--<(3) Teddies

Caret (^), lower case v and pipe (|) can pivot the diagrams if that serves

 Customer
   |
   ^
 Order

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.