Crow's foot notation

Crow's foot - properly, Information Engineering notation - puts two marks on each end of every relationship line. The one further from the entity is the maximum, and the one nearer the entity is the minimum.

Combine them and there are exactly four possibilities, which is the entire notation:

This is the one thing about crow's foot that is genuinely counter-intuitive, and it accounts for most misread diagrams. The symbols at an end describe the entity at that end, and you read them when travelling towards that entity.

On the fourth line of the diagram above, the crow's foot is at the Orderend. It does not mean "many customers". It means: for one customer, there are zero or more orders. Say it as a journey - "starting at a customer and following the line, I arrive at zero or more orders" - and the direction stops being ambiguous.

A practical habit that removes the problem entirely: write the verb phrase on the line in the direction it reads left to right, and check the reverse reading out loud before moving on. Thirty seconds per relationship, and it catches errors that survive months in a document.

The line style carries a second piece of information, and it is about keys rather than counts.

The test is one question: could this row be identified without knowing its parent? An order line numbered 3 is meaningless without knowing which order - that is identifying. An order belonging to customer 41 is still order 8817 regardless - that is non-identifying.

A crow's foot at both ends says "many to many", and no relational database can store that in two tables. The relationship becomes an entity of its own - a junction, associative, or link entity - holding a foreign key to each side.

Two things follow, and the second is the reason this is worth doing early rather than at implementation time:

1. The junction usually has its own attributes. An enrolment has a date, a grade, a status. Those attributes had nowhere to live while the relationship was a line, and their absence was hiding a gap in the model.
2. The junction deserves a real name. Enrolment, Booking, Assignment - not StudentCourse. A domain expert recognises the first and cannot review the second, and the name is usually a word the business already uses.

Note the keys in the junction above: both foreign keys together form the primary key, which is what stops the same student enrolling twice on the same course. That constraint came free from modelling it properly, and would otherwise have been a validation somebody remembered to write.

None of this is exclusive. A great many real projects draw crow's foot for the database and a class diagram for the domain model, and keep both, because they are answering different questions about the same nouns.

1. Symbols on the wrong end. The most common error in the notation. Read both sentences aloud.
2. Everything optional. A circle on every inner position means the model asserts no rules, and every one of them becomes application code instead.
3. Many-to-many left in a logical model. Fine conceptually, impossible physically. Resolve it and name the junction.
4. Junction entities named by concatenation. OrderProduct tells a reviewer nothing; OrderLine tells them everything.
5. Relying on dash style alone for identifying relationships. Tools disagree. Show the composite key in the box too.
6. Unlabelled relationships. The verb is what makes the line readable aloud, and reading it aloud is the review.

With cardinality settled, the next question is what the tables should be at all - which is schema design: keys, normalization, and the trip from a model to DDL.