Crow's Feet - One vs One And Only One

Question

What is the difference between

ONE:

ONE AND ONLY ONE:

I can't find any crows-feet documentation that allows for the "ONE" above, and I've never encountered it in a database diagram. As far as I know, crows-feet notation always has two parts - one (| or O) to mark mandatory or optional and a second (| or branching) to indicate singular or multiple cardinality. — AmericanUmlaut, Mar 14 '17 at 16:25

score 119 · Accepted Answer · edited May 16 '18 at 00:06

119

I think n.piskunov is on the right track.

I would use it as the mathematical analogy found here: https://math.stackexchange.com/questions/247380/analogy-between-one-and-only-one-and-if-and-only-if

"existence of at most one such X that satisfies Y."

As an example, consider these relations:

A student, Alice, can only have one dorm room at at time. A dorm room can only house one student at a time (for the sake of this example). Next year, Alice will be assigned a new dorm room, and at that point her dorm room from this year will be assigned to a new student.

Alice can have one and only one login (e.g. a11235) and that login can only be assigned to Alice. When Alice graduates, no one else can be assigned the login a11235.

edited May 16 '18 at 00:06

Gerry

10,337
3
31
40

answered Mar 25 '18 at 01:07

valem

1,690
2
9
11

14

I think this is the best example – Lorenzo Belfanti Feb 12 '20 at 15:59
I agree that this is the best example. It doesn't really matter if someone says "it should always be written with two lines", for with this definition by valem & Gerry it actually adds value to the model. – Fom Sep 19 '21 at 15:40

n.prokhorov · Answer 2 · 2017-12-28T14:45:57.577

31

In my opinion the difference is:

Red entity (king's palace) is related to single inhabitant - Prince Arthur. There are no other inhabitants, who may live in palace, except Arthur. But relation type is still Many-to-one (many palaces to single inhabitant)
But, any inhabitant may have a horse(s). Relation is, again, many-to-one (many horses to definite inhabitant), but behaves differently. (Knights are also allowed to own a horse).

edited Dec 28 '17 at 14:45

answered Dec 28 '17 at 14:38

n.prokhorov

930
7
13

2

That is a great way of looking at it. I do not know if it is true, but is an answer, which is more than what I had before. – Atron Seige Feb 28 '18 at 13:40
1

I'm also not 100% sure about it, because I wasn't able to find any proofs about my idea, but I can't invent a better explanation – n.prokhorov Mar 01 '18 at 20:31
4

Unfortunately this answer is wrong and will mislead any reader. The correct answer as given below by @user3114859 is that the relationship requires two marks and so the 'ONE' mark doesn't exist. If you want to use a version of Crow's Foot that does use it then the "ONE AND ONLY ONE" as explained by valem below at least has some value distinct from "ONE". – Jay Daley Mar 25 '19 at 00:12
1

As @JayDaley says, this is wrong and misleading. Both [this](https://stackoverflow.com/a/50714961) and [this](https://stackoverflow.com/a/53293820) answer nail it: `--|-` is the (deliberately ambiguous) base case, and both `--o-|-` and `--|-|-` are two unambiguous, specific sub-cases of `--|-` (the former allowing `0 or 1` instances an the latter allowing `strictly one`) – Sam Feb 05 '21 at 22:04

score 24 · Answer 3 · answered Nov 14 '18 at 05:39

dosenmatter is correct

To be specific with the notation, always use two signs:

The one closest to the table
- This is the cardinality
- It says if there can be one or many (line or crows feet)
The other one
- This is the modality (aka ordinality)
- It says if the minimum can be zero or one (ring or line)

If there is only a single line, this only means that we don't know if there could be "zero or one" or "one and only one".

dosentmatter · Answer 4 · 2018-06-06T07:57:03.097

18

According to this answer to another question about differences in crow's feet notation, it says "one" and "many" are ambiguous.

Wikipedia also uses the double bar, "one and only one" for one artist performing many songs. This doesn't match with the accepted solution, because it doesn't mean that one artist performs all songs like how king arthur inhabits all palaces. It also doesn't match valem's solution, because an artist isn't tied to one song and can be reassigned to other songs.

My conclusion, is that "many" can mean "zero or many" or "one or many" because it is ambiguous on purpose. You are leaving it unspecified because you don't know or don't care at this point in time. Similarly, "one" can mean "zero or one" or "one or only one". See how in both notations, being specific is just specifying the lower bound as 0 or 1.

So in summary,

use "zero or one" if it can be 0 or 1, {0, 1}
use "one and only one" if it can be 1, {1}
use "one" if it can be "zero or one" or "one and only one", {0, 1} ∪ {1}

If you think about it, "one" === "zero or one" since {0, 1} is a superset of {1} and {0, 1} ∪ {1} === {0, 1}.

I think the notation is designed like this just to parallel the "many" notation. Also, just to explicitly say you are being nonspecific about the lower bound.

edited Jun 06 '18 at 07:57

answered Jun 06 '18 at 07:49

dosentmatter

1,494
1
16
23

1

Doesn't this just mean that "zero or one" is the same as "one" then, since in both cases, it can be zero or it can be one? – Paradox Oct 04 '18 at 14:28
1

Yes, that's what I mentioned in the second to last paragraph. And in the last paragraph, I made a guess of why this is so. – dosentmatter Oct 04 '18 at 20:51
2

This should be the accepted answer, the accepted one is incorrect. – LearningMath Nov 24 '18 at 17:38
1

I'm skeptical of this answer, because there is a separate arrowhead that already means "zero or one" (`---O-|-`) – Cameron Hudson Dec 21 '19 at 00:31
2

@CameronHudson I have already addressed "zero or one" in my answer and why I think it is the same as "one". – dosentmatter Dec 23 '19 at 18:26
1

@dosentmatter I understand. I'm not saying that you're wrong. But if "one" `===` "zero or one", then the ERD symbology contains a redundant arrowhead. I would be surprised if this was the case, because the inventor of the ERD was a smart person. Possibly the inventor thought that it was a feature to have an additional arrowhead for "one", for expressiveness, but I'd argue that it isn't a [good] feature because of the confusion that has ensued, with this thread as one example. So either the ERD inventor made a mistake, or "one" `!==` "zero or one". I'm skeptical of both alternatives. – Cameron Hudson Dec 24 '19 at 19:53
2

@CameronHudson Yeah, I agree that it is a bit redundant and I might be incorrect, but I think it's the best way to explain it or at least lie to myself. I think of it in the same way as optional characters or syntactic sugar you type when programming. Even though there are redundant ways to do the same thing, you might omit some characters if you think it aids in reading. On the other hand, it might be easier to read if you are explicit rather than implicit. In the end, it's just a tool, so it doesn't really matter as long as your team agrees on a convention and you document it beforehand. – dosentmatter Dec 26 '19 at 15:49
@CameronHudson you can see this as a specificity feature instead of a redundancy issue: `--|-` is the base case, and both `--o-|` and `--|-|` are two specific cases that satisfy `--|-` (the former allowing **0 or 1** instances an the latter **strictly one**) – Sam Feb 05 '21 at 18:52

score 3 · Answer 5 · answered Mar 29 '19 at 07:58

I think the one (and only one) difference between "one" and "one(and only one)" relationships is of uniqueness.

A non-transferable airline flight ticket PNR is issued to, let's say, 'Alice (and only Alice)' and is not transferable to any other person but a movie ticket ID is issued to (and held by at any time) "one" person but can be transferred/sold to another person before entering the movie theater.

score 2 · Answer 6 · answered Jul 28 '20 at 12:39

Use the "one and only one" when you're absolutely sure it's a singular relationship. Your completed diagram should have this notation since it's more specific.
Use the "one" notation if you know you want some sort of single relationship, but you're not completely sure which yet. Maybe you're concepting an idea within an ERD and you're pretty confident it's some sort of "one" relationship, but you have some kinks to work out before you know if it's a "zero or one" or a "one and only one" or a "one or many" relationship. In that scenario, just put the singular "one" notation and figure out your exact mins and maxes later.

score -1 · Answer 7 · edited Mar 16 '17 at 12:44

-1

One and Only One is used for cardinality. I don't think One alone can be used for cardinality in crow foot. Source

edited Mar 16 '17 at 12:44

Atron Seige

2,783
4
32
39

answered Apr 05 '16 at 12:48

user3048613

19
3

1

But One means exactly the same thing. If FK might be null, then it would be Zero or One, not One. – Atron Seige Apr 06 '16 at 06:25

Crow's Feet - One vs One And Only One

7 Answers7