An EER diagram generator for subtypes, with an honest limit

An eer diagram generator, if you mean Enhanced ER with subtypes and inheritance, has to be honest about the picture Mermaid will actually draw. You can name a supertype, a subtype, and a relationship called "is a". You cannot draw a Chen specialization circle, a disjointness letter inside that circle, or a total-versus-partial double line. Mermaid's erDiagram is crow's foot. The inheritance tree belongs on a class diagram. I use both, and I don't pretend they are the same notation.

What "enhanced" was supposed to add

Plain ER is entities, attributes, and relationships, plus cardinality. Enhanced ER, the EER people meet in a database course, adds specialization and generalization. A PERSON has a name. An EMPLOYEE is a person with a title. A CUSTOMER is a person with a plan. The subtype inherits the supertype's attributes. You are not supposed to copy name onto every child box and hope the reader notices the duplication.

The textbook drawing for that is usually Chen-style. A circle sits between PERSON and the subtypes. A d in the circle means disjoint: a person is an employee or a customer, not both. An o means overlapping: a person may be both. A double line into the circle means total: every person is at least one of the subtypes. A single line means partial: some people are just people. Those four combinations are the part of EER that crow's foot does not have a glyph for.

I still want the model in git. The question is which file is allowed to carry which claim. The ER file can show the tables you will actually migrate: a person row, an employee row keyed by the same id, a customer row keyed by the same id. The class file can show that Employee and Customer are kinds of Person. The sentence under the figure carries disjointness, because neither picture in Mermaid has a d to put in a circle. If a tool calls itself an EER generator and emits a keyword Mermaid does not have, it is inventing a language. Don't paste that into a README and hope the preview agrees.

The limit, said plainly

erDiagram draws entities, attributes, and crow's foot lines. The line PERSON ||--o| EMPLOYEE : "is a" is a normal relationship with a name you chose. The parser does not know that "is a" means inheritance. It knows there is one person and zero or one employee on that line. There is no specialization keyword, no subtype arrow, and no way to attach a disjointness constraint to a pair of relationships. I am not going to invent one for this post. If you find a snippet online that starts with eerDiagram, it will not render here.

That is a good limit, once you stop fighting it. Crow's foot is the right picture of the relational mapping. The employee table's primary key is also the foreign key to person. That is the usual way to map a subtype when the subtype doesn't get its own surrogate key. The diagram should show that key, not a circle. The circle was a conceptual claim. The key is the schema. Design reviews that mix the two end up arguing about a symbol nobody is going to migrate.

A class diagram is the inheritance picture this ER line can't draw well. Person <|-- Employee puts a hollow triangle on the parent. Two subtypes hang off one parent and the eye reads a tree. Two crow's foot lines named "is a" do not read as a tree. They read as two optional one-to-one relationships, which is exactly what the tables are. Use the class diagram when the review is "what kind of person is this." Use the ER diagram when the review is "where does title live, and what is the key."

The ER diagram syntax page is the cardinality reference. The class diagram syntax page is the inheritance reference. This post is the seam between them. If you only needed crow's foot for orders and customers, you don't need EER language at all. You need an ERD diagram creator walkthrough and a foreign key on the many side. Subtypes are a smaller, more annoying problem.

The "is a" line

Here is the pattern I will actually commit. PERSON is the supertype. EMPLOYEE and CUSTOMER are optional subtypes. Each relationship is named "is a". The employee side is zero or one, because a person might not be staff. The person side is exactly one, because an employee row that doesn't point at a person is an orphan, and I don't want that row.

erDiagram
    PERSON ||--o| EMPLOYEE : "is a"
    PERSON ||--o| CUSTOMER : "is a"
    PERSON {
        string id PK
        string name
        string email
    }
    EMPLOYEE {
        string personId PK, FK
        string title
        date hiredOn
    }
    CUSTOMER {
        string personId PK, FK
        string plan
    }

Read the markers from the entity they touch. On PERSON ||--o| EMPLOYEE, the || touches PERSON and the o| touches EMPLOYEE. Exactly one person, zero or one employee. The same reading applies to CUSTOMER. I put the supertype on the left so the parent is the thing I meet first. Swapping the entities without swapping the markers will compile into a different rule: every employee must exist and a person is optional. That is a legal line and a bad subtype.

personId on EMPLOYEE is PK, FK. It is the primary key of the subtype row and the reference back to PERSON. That is the identifying mapping. I don't also put name on EMPLOYEE. The name lives once, on PERSON. Copying it down is how subtype tables rot. Someone updates the person name and the employee copy stays wrong, and the diagram that listed both columns gave them permission.

title and hiredOn stay on EMPLOYEE because a customer does not have a hire date. plan stays on CUSTOMER. If a field is shared, move it up. If you are unsure, leave it off the diagram until the migration has a column. An EER picture full of maybe-columns is a brainstorm, and brainstorms belong in a list, not in a schema review.

The relationship label has quotes because it contains a space. PERSON ||--o| EMPLOYEE : is a looks readable and then the parser takes is as the label and chokes on a. The quotes are not decoration. : "is a" is the form that keeps both words. I use that exact label so a reader sees the intent. The label is still just a name. It does not turn the line into a specialization circle.

What this drawing fails to say

Look at the two "is a" lines together. Nothing stops a person from having an employee row and a customer row. That is overlapping specialization. Nothing forces a person to have at least one of them. That is partial specialization. If your rule is disjoint and total, this picture is incomplete on purpose. I write the rule under the figure in a sentence: "A person is an employee or a customer, not both, and not neither." The sentence is the constraint. I do not add a fake entity called DISJOINT to hold it.

People try to encode disjointness by drawing a single relationship to a vague ROLE entity. That hides title and plan in a pile of nullable columns, which is the opposite of a subtype. Nullable columns are how you avoid a second table, and they are a fine design when you have two flags. They are a bad design when employee and customer each have a handful of required fields. The diagram should make that choice visible. Two subtype boxes say you chose two tables. One box with a type column says you chose single-table. Don't draw the two boxes and then implement the column. The picture will outlive your memory of the compromise.

Total participation has a similar trap. ||--|| between PERSON and EMPLOYEE says every person is an employee and every employee is a person. That is total and mandatory, and it deletes the idea of a person who is only a customer. I use ||--o| because the zero is the honest case for a directory that stores people before they are staff. If your database check constraint says otherwise, change the marker. The marker is cheaper to fix than the production data.

Cardinality details beyond this subtype trick live in the cardinality notes. I don't want this page to become a second copy of that one. The only cardinality I need here is "one person, zero or one extension row." If you need many orders per customer, that is a different line, CUSTOMER ||--o{ ORDER : places, and it is ordinary ER, not EER. Don't call every crow's foot an enhanced diagram. The word means the subtype problem.

The inheritance picture

This is the drawing the ER diagram will not give you. Employee and Customer are kinds of Person. The triangle sits on Person. Shared attributes stay on the parent. Subtype attributes stay on the child. There is still no d in a circle. There is a tree, which is what people wanted when they asked for EER and then felt disappointed by two relationship lines.

classDiagram
    class Person {
      +string name
      +string email
    }
    class Employee {
      +string title
      +date hiredOn
    }
    class Customer {
      +string plan
    }
    Person <|-- Employee
    Person <|-- Customer

Person <|-- Employee is inheritance. Employee is a Person. The methods and fields on Person are available on Employee without being redrawn. That is the generalization the crow's foot line only hinted at by sharing a key. I don't put personId on the class boxes. The key was a relational fact. The class diagram is the type fact. Mixing them is how you get a class called PERSON with a field called PK, which helps nobody.

If a reviewer asks "can someone be both," you still answer in prose. Overlapping subclasses are legal in some languages via interfaces and awkward in others via single inheritance. The diagram above uses single inheritance and does not show a person object that is both classes at once. If your domain really has people who are staff and customers, say so under the figure, and consider whether the class model should be roles composed into Person rather than subclasses. Person o-- EmployeeRole is a different design. I won't sneak it into this picture. Switching from inheritance to composition is a product decision, and it should show up as a deleted <|-- line in the diff so people notice.

The class diagram creator post walks visibility, composition, and a billing model in more detail. Use that when the boxes grow methods. Use this page when the question started as tables and subtypes. I link them because teams keep asking one diagram to be both, and the files stay cleaner when the jobs stay split.

From SQL, not from a circle

The generator I trust starts from the tables, not from a nostalgia for Chen notation. If the migration already has employees.person_id as a primary key and a foreign key, the ER diagram should say PK, FK and the relationship should be one to zero-or-one. The guide that turns SQL into an ER diagram is the mechanical version of that reading: foreign keys become crow's feet, join tables stay as entities, nullable columns change the circle in the marker. Bring a subtype along only when the SQL really has a subtype table. A type column on PERSON is not two entities. Draw the column as an attribute and stop.

An AI ER generator will sketch entities from a description like "people, some of whom are employees." Read the output for a many-to-many that should have been one-to-one, and for a copied name attribute on every box. Those are the two tells. Fix them by hand. The five complimentary AI uses are a budget, not a reason to regenerate the file until the circle appears. The circle is not coming. Edit the line.

I also keep a one-line comment in the Markdown under the fence, not inside it. Mermaid comments exist, and they tend to rot in the middle of the diagram where a reviewer skips them. The prose under the figure is where I put "disjoint, partial" or "overlapping, partial." The next person can grep for "disjoint". They cannot grep a circle that was never drawn.

Mistakes I keep seeing

1. Expecting a specialization circle. Mermaid will not draw one. Naming the relationship "is a" is the pattern. The class diagram is the tree. The sentence is the disjointness rule.
2. Using ||--o{ because most ER examples are one-to-many. A subtype extension is zero or one, o|, unless you really store many employee rows per person. Many rows means you modeled a history table. Call it that.
3. Putting name on PERSON and EMPLOYEE. Shared attributes live on the supertype. The subtype holds what is true only of that subtype.
4. Marking personId as FK and forgetting PK, or the reverse. In the identifying mapping it is both. A separate surrogate key on EMPLOYEE is a different mapping, and then the relationship is no longer "the same row, extended."
5. Drawing CUSTOMER and EMPLOYEE and then writing a check constraint you never put in words. If both rows are forbidden, the diagram looks overlapping. Say "not both" underneath, or change the schema to a single type column and delete a box.
6. Reversing <|-- on the class diagram so Person inherits from Employee. The triangle sits on the parent. The same parent-side habit you use for billing accounts applies here.

A data-flow sketch of how a person record is copied into a warehouse is a different question. The data flow diagram generator post is where that picture belongs. Don't add a flow arrow to this ER diagram to show "sync." ER lines are relationships between stored things, not jobs that run at night.

What I commit

I commit two fences and a short paragraph. The ER fence has PERSON, EMPLOYEE, and the "is a" line, plus CUSTOMER when the product really has that second subtype. The class fence has the triangle. The paragraph states disjoint or overlapping, and total or partial, in words a DBA and a product manager can both read. I do not commit a screenshot of a Chen diagram from a lecture slide. The slide will not update when hiredOn becomes startDate.

If the schema is still a sketch, I would rather have the class diagram alone than a fake ER diagram with entities we will not create. Adding an EMPLOYEE box implies a table. People will ask for the migration. That is healthy pressure. Don't draw the box until you accept the table, or label the figure as a conceptual class model and skip erDiagram entirely.

Open the editor and paste the "is a" pattern before you add a third subtype. If you cannot say which attributes move up to PERSON, you are not ready for the third box. Add the attribute decision first. The diagram gets easier after that, not harder.