Mermaid er diagram cardinality and crow's foot marks

The mark called mermaid er diagram cardinality sits on each end of a relationship line: how many rows on this side may exist for a row on the other side. The four marks you need are || for exactly one, o{ for zero or more, |{ for one or more, and o| for zero or one. The many side holds the foreign key. The syntax overview, the full attribute list, and the rest of the grammar live on the ER diagram page. This page is about reading the foot so the picture matches the schema.

I learned the symbols by memorizing a cheat sheet and still drew them backwards. The line parsed. The preview looked professional. The foreign key was on the wrong entity, which meant the diagram described a product we were not building. Parsing is not the same as being right. Cardinality is the part that can lie while staying valid.

The four marks, said out loud

Say the line in a sentence before you admire the picture. CUSTOMER ||--o{ ORDER is "each order has exactly one customer, and a customer has zero or more orders." The || sits on the customer. The o{ sits on the order. The mark closest to a name is the claim about that name.

|| is exactly one. Not "at least one," and not "we hope there's one." A row on the other side cannot exist without this one, and it cannot point at two of them. Use it for the parent in the usual parent-and-child line.

o{ is zero or more. The circle is the zero. The crow's foot is the more. A new customer with no orders yet is legal. That's why a shop almost always wants o{ on orders, not a mandatory many. I used the mandatory form on a signup flow and the diagram said a customer row could not exist until an order existed. The code did the opposite. The diagram was stricter than the product, and a new engineer trusted the diagram.

|{ is one or more. There is a foot and there is no circle. An order with zero line items is illegal in this model. That's a real constraint if you create the order and its items in one transaction. It's a lie if you insert the order first and add items later, even for a moment. Draw the database you have during the states you allow, not the database you have at the end of a happy request.

o| is zero or one. One customer has at most one profile row. The profile may be missing. This is not a crow's foot. If you draw a foot, you have allowed many profiles, and a unique constraint in the migration will contradict the picture.

The connector between the marks is -- for a solid line. The colon and the verb come after the second entity: : places. The verb is required. CUSTOMER ||--o{ ORDER places fails to parse because the colon is missing. I still drop it when I'm typing fast. The preview stops on that line and the rest of the schema never appears, so a missing colon looks like a disaster when it's one character.

Mermaid also accepts words like one or many in place of the marks. I don't use them here. The marks are what reviewers can see at the end of the line, and mixing words and marks in one diagram makes people think they mean different things. Pick the marks and stay with them. If you want the word forms, the syntax page lists them. Don't invent a private dialect in a README.

The many side holds the foreign key

The foreign key is a column on the side that points. That's the side whose mark is o{, |{, or o|. The || side does not store a list of child ids. If you put customerId on CUSTOMER, you have said the customer row remembers one order, and the crow's foot is lying.

I got this wrong by reading left to right. The line starts at CUSTOMER, so I put the key on CUSTOMER. Left to right is the order of the words. It is not the direction of the pointer. Find the foot, or the o|, and put the FK there.

On a zero-or-one, there is no "many," but the same rule holds. CUSTOMER ||--o| PROFILE puts customerId on PROFILE, and that column should be unique. The unique constraint is what turns "zero or more" into "zero or one." If you forget unique, the database allows many profiles and the o| is fiction. The diagram can't enforce it. The diagram can show the intention next to the column. Write UK as well as FK if your team reads that marker. The point is to make the uniqueness visible, not to collect abbreviations.

Don't put the primary key and the foreign key on the same column unless the child's identity really is the parent's id. That's a possible one-to-one. It's a bad habit on an order line, where the line has its own id and also an order id. Mark id PK and orderId FK as two lines. I've seen orderId PK, FK on a line item that also needed its own id, and the diagram then couldn't show two items on one order without lying about the key.

A shop schema you can check against a migration

A customer places orders. An order contains one or more items. Each item points at a product. A customer may have a profile, and may have none. Those four sentences are the whole model. If a sentence is wrong for your shop, change the mark, don't decorate the picture.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ITEM : contains
    PRODUCT ||--o{ ITEM : includes
    CUSTOMER ||--o| PROFILE : describes
    CUSTOMER {
        string id PK
        string email
    }
    ORDER {
        string id PK
        string customerId FK
    }
    ITEM {
        string id PK
        string orderId FK
        string productId FK
    }
    PRODUCT {
        string id PK
        string sku
    }
    PROFILE {
        string id PK
        string customerId FK
    }

Walk the feet. ORDER is o{, so customerId is on ORDER. A customer may have zero orders. ITEM is |{ against ORDER, so every order has at least one item, and orderId is on ITEM. ITEM is also o{ against PRODUCT, so a product may be in no carts yet, and productId is on ITEM. PROFILE is o|, so there is at most one profile, and customerId is on PROFILE.

email is not a key in this drawing. I used a surrogate id because I've watched an email change and break every foreign key that used it as identity. If your schema really keys customers by email, switch the PK marker and say so in the paragraph under the figure. The feet don't care which column is the key. They care which entity is the one and which is the many.

I left status off ORDER on purpose. A status enum is a column, not an entity, unless you store statuses in a lookup table with attributes of their own. Adding STATUS with a crow's foot because the word appears in the UI creates a table you don't have. The diagram gets bigger and the migration doesn't. I've reviewed that diagram. It looked thorough. It was fan fiction.

The ecommerce template extends this kind of shop with payments and a few more entities. Use it when you need the extra tables. Don't paste it when the question in the room is only "where does customerId live." A larger chart is easier to nod at and harder to check.

A second schema, because one example gets memorized

People memorize the shop and then draw every problem as customers and orders. Here is a different pair of marks on a support desk. An account opens tickets. A ticket has one or more messages. An agent may be assigned, and the assignment is optional. A ticket has at most one escalation row.

erDiagram
    ACCOUNT ||--o{ TICKET : opens
    TICKET ||--|{ MESSAGE : has
    AGENT ||--o{ TICKET : handles
    TICKET ||--o| ESCALATION : raises
    ACCOUNT {
        string id PK
        string email
    }
    TICKET {
        string id PK
        string accountId FK
        string agentId FK
    }
    MESSAGE {
        string id PK
        string ticketId FK
    }
    AGENT {
        string id PK
        string name
    }
    ESCALATION {
        string id PK
        string ticketId FK
    }

TICKET.accountId is required in spirit because the mark on ACCOUNT is ||. The diagram doesn't show nullability with a separate token. The || is the claim that a ticket without an account is not a ticket in this model. If your import job creates tickets before the account lands, the mark is wrong. Use a form that allows the missing parent, and be honest that you've allowed orphans. I'd rather see the orphan in the diagram than discover it in a support thread.

agentId is on TICKET because the agent side of handles is the one and the ticket side is the many. An unassigned ticket is why that many is o{ and not |{. A queue with a mandatory agent would use |{, and then the diagram would forbid the empty queue I know we have on Monday morning. Draw Monday morning.

ESCALATION.ticketId should be unique if o| is serious. Two escalation rows for one ticket means the mark should have been o{. I drew o| at a company that then added "escalation history" and kept the diagram. The history made the mark false. Change the mark when the product grows a second row. Don't keep the prettier one-to-one because the layout is familiar.

MESSAGE is |{. A ticket with no messages isn't a state we store. If you create the ticket in one request and the first message in a later request, you have a window with zero messages, and |{ is too strict. Use o{ for that window or create them together. This is the same judgment as order items. The mark is a product decision wearing a symbol.

Marks that parse and still lie

These are the failures I trust less than a parse error, because the preview looks finished.

1. The foot is on the parent. ORDER o{--|| CUSTOMER can be a legal line and a backwards story, depending on how you read your own verb. Pick a direction and keep the parent on the || side until you can read the line out loud without hesitating. I standardize on parent-left, child-right so a glance is enough.
2. |{ used as a wish. The code inserts the parent, commits, then inserts children. For that moment the many is zero. o{ is the honest mark. Tighten it to |{ only if a constraint actually rejects the empty parent.
3. o| drawn as o{ because the foot "looks more like a relationship." A foot is many. If the product is one profile, the foot is a bug in the picture.
4. The foreign key repeated on both entities. Each side "has a link," so both got a column. You now have two pointers that can disagree. One pointer, on the many side.
5. A many-to-many squeezed into a single line with feet on both ends and no junction entity. Mermaid will draw PRODUCT }o--o{ TAG style lines, and they hide the table that actually holds the two foreign keys. If the junction has no attributes, you can still omit it and accept a less precise picture. If it has a quantity, a timestamp, or its own id, draw the junction. ITEM in the shop schema is that junction with a spine. Hiding it would hide orderId and productId.

Naming the relationship with a vague verb makes the lie easier. : relates fits every wrong foot. : places, : contains, : handles can be checked against a sentence. If you can't finish the sentence, you don't know the cardinality yet. Leave the line out until you do. An incomplete diagram is better than a complete one that invents a constraint.

Attributes that aren't keys can stay out. A twenty-column entity turns the feet into a footnote. I list the primary key, the foreign keys, and maybe one column a human uses to recognize the row, like email or sku. The rest is a table definition. Link it. Don't paste it into the figure.

Where the syntax overview stays

I kept identifiers, key markers, and line styles short on purpose. The ER diagram page is the syntax overview: how entities are declared, how the markers are spelled, what the line styles mean. This page will go stale if it tries to be that page too. When a line fails to parse, open the syntax page. When a line parses and you don't believe the constraint, stay here and read the foot again.

If you already have SQL, the ER diagram from SQL guide is the path from a CREATE TABLE to a first diagram. Generated diagrams get cardinality wrong in a specific way: they see a column named something_id and guess. Check every guess against the four marks. A guessed || on a nullable column is the error I look for first.

An ERD creator is a reasonable way to get boxes on the page quickly. An EER diagram generator is aimed at the extended notation, which is a different set of claims. Don't let either of them choose |{ for you without reading it out loud. A class diagram is the wrong picture if the question is rows and foreign keys. Classes have methods and inheritance. Those aren't cardinality. A data flow diagram shows movement between processes, not how many orders a customer may have. I've watched a meeting try to answer a foreign-key question with a data-flow picture. The picture was busy and the key was still on the wrong table.

The AI ER generator will draft entities from a description of the product. It will also draft confident feet. Treat every mark as unread until you've said the sentence. Five minutes of reading beats a diagram that forbids a state your insert statement allows.

Fix the feet, then share it

Paste the schema into the editor and change one mark at a time. Flip o{ to |{ and ask what breaks in the product. If the answer is "nothing, we already reject that state," keep the stricter mark. If the answer is "the import job," put the circle back. Commit the diagram next to the migration that creates the foreign key. When the migration adds a second profile row, the same pull request changes o| to o{. That's the whole maintenance plan. A diagram that can't survive that edit was only ever a poster.