22

Background
I saw this figure describing the different crowfoot notations used in ERD: enter image description here
I'm not able to find the difference between the "many" notation and the "zero or many. However I was able to find an example (see bottom left for the "many" notation):
enter image description here (source)

Question
When to use the "many" notation and what is the difference between this notation and the "zero or many" and "one or many" notation.

Christophe
  • 74,672
  • 10
  • 115
  • 187
KingBoomie
  • 545
  • 2
  • 5
  • 11
  • I have always assumed that the first notation in your list meant "one and only one", and I assumed that the third one was just a less commonly used equivalent of the first one. I have never seen the second one. – mastazi May 09 '17 at 02:50

4 Answers4

22

The 2 first relations, One and Many, have an unspecified lower bound. So when using them you leave an ambiguity of whether they are mandatory or optional.

This ambiguity is useful in modeling, in order to cope with one or several of the following circumstances:

  • lower bound could be temporarily undefined, for example during the design phase, when all business rules are not yet clear.
  • lower bound is not relevant, for example because it's a decision that can be taken later, when configuring the software and according to business rules (e.g. ad hoc definition of a not null constraint in the database, or run-time parameter that defines whether the application code shall accept the situation or not.
  • to avoid unnecessary anxiety when there is a discrepancy between theoretical model and application practice. Typically, this is the case for your shipment to item relation: In the real life, you'd expect that one shipment contains at least 1 item (people usually don't like to send empty boxes!). So in the model, you'd expect one or many. But on the other side in your application, you could very well decide to create a shipment in two stages: you create an empty shipment in the sales office, and later you add the items (with a barcode reader on the shop floor). So the app and the db have to deal with shipments that can be temporarily empty.

The last case happens much more than one usually expects. Keeping the lower bound unspecified, has therefore the advantage of forcing dba and developers to keep the lesser constraint (i.e. 0 or many in the application practice), without contradicting the theoretical assumption (i.e. 1 to many, because in the end, no shipment leaves the factory empty).

Christophe
  • 74,672
  • 10
  • 115
  • 187
  • Cool! What about 1 (`---|-`) vs. 1 and only 1 (`---||-`)? – Paul-Sebastian Manole Sep 13 '20 at 15:12
  • 2
    @Paul-SebastianManole The 1 is ambiguous because it could be “0 or 1” or “1 and only 1” . So if you know the lower bound, indicate it. In my own experience 1 and only 1 is quite rare. So 1 without clarification tends to be understood as 0 or 1 more than exactly 1. But as daid, it stays ambiguous. – Christophe Sep 13 '20 at 15:27
  • This makes perfect sense because when we use single pipe (|) in the model we are not specifying the other part. The other answer 'one thing such X that satisfies Y' is confusing because the model is not dependent on the actual behaviour of the entities, it is just showing the relationship. – Akshay Hiremath Jun 29 '22 at 03:24
4

I think people just use them just when they don't care enough about being specific.

Personally I avoid the first two examples on your list entirely.

For your example, I would assume -- from the context -- that shipments have one-and-only-one shipper, and items exist in one-and-only-one shipment, and suppliers can have zero-to-many shipments.

Darien
  • 3,453
  • 2
  • 19
  • 18
3

I typically use the -< "many" notation when writing fast on a whiteboard. It's useful there because we're just trying to sketch a general idea. Vagueness is sometimes helpful at this stage.

The difference between "many" vs. "zero or many" and "one or many"...

  • "many" -< is effectively the same as "0 or many" because we cannot assume a lower bound that is not stated. It's either obvious or vague depending on how your team understands it.

  • "zero or many" -0< is specific. It means the number of related entities >= 0.

  • "one or many" -|< means the number of related entities > 0.

btw: The "one" -|- notation is explicit. It means exactly one, and is relatively infrequent to use. If you want to write a vague version of "one" you write a line -- with no symbol at all at the end.

This is a bit like playground baseball. This is how it's played in my neighborhood. Is there an ISO committee for this? Do we really care?

joshp
  • 3,451
  • 1
  • 21
  • 27
1

You asked:

When to use many notation?

The ERD should reflect the design and the business requirement you decided to implement as RDBMS rules.

As an example from the ERD you have provided, A Shipment Includes MANY line_items. Since both sides are mandatory, this means that:

1-In physical design, there would be FK of Shipment ID in the Item table.

2-This FK column can't allow nulls. This makes it mandatory to reference an existing shipment before inserting a line_item in the Item table.

3-As a result your shipment table must include the desired row before an line-item is inserted.

You asked:

what is the difference between this notation and the "zero or many" and "one or many" notation.

Your diagram does not provide an easy case to use to explain this one.

When you have a one-many relationship, it means that a PK of the of the table in the One side of the relationship will be created and play the role of FK in the many-side of the relationship. Very much like the previous example. The difference is that when you have "zero or many", it is actually, "zero, 1 or many", meaning that under certain business conditions, your application is allowed to insert a row in the shipment table even if no items reference this particular shipment.

Unlike the previous case, the FK is created in the Item table as Nullable (allows nulls).

NoChance
  • 12,412
  • 1
  • 22
  • 39