ERD notation: Crow's Foot vs. Chen
Entity-relationship diagrams are drawn in one of several notations. Crow's Foot is the standard in practice; Chen is the standard in academic ER theory.
| Notation | How cardinality is shown | Best for |
|---|---|---|
| Crow's Foot | Graphical markers at line ends: bar (one), circle (zero), fork (many) | Practical database design, modern ERD tools, team communication. |
| Chen | Diamond shapes for relationships, 1/N/M labels on lines | Academic ER modelling, complex multi-way relationships. |
| IE (Information Engineering) | Variant of Crow's Foot with slightly different end markers | Legacy enterprise tools, IDEF1X compatibility. |
Cardinality markers (Crow's Foot)
In Crow's Foot notation, each end of a relationship line carries two markers: the minimum cardinality (zero or one) and the maximum cardinality (one or many). Reading both ends gives you the full constraint.
| Marker | Relationship type | What it means |
|---|---|---|
| — | | One-to-one (1:1) | Each row in A relates to exactly one row in B. Example: User → UserProfile. |
| — < | One-to-many (1:N) | One row in A relates to many rows in B. Example: User → Orders. |
> — < | Many-to-many (M:N) | Many rows in A relate to many rows in B. Requires a junction table. Example: Products ↔ Orders via order_items. |
○ — | | Zero-or-one (0:1) | Optional one. The foreign key is nullable. Example: Order → Invoice (not every order has been invoiced). |
○ — < | Zero-or-many (0:N) | Optional many. Example: Customer → Orders (a new customer may have no orders yet). |
Example: e-commerce ERD
The following five-entity model covers the core of a typical e-commerce data model. The order_items table is the junction table that resolves the many-to-many relationship between orders and products.
| Entity | Primary key | Key attributes | Foreign keys |
|---|---|---|---|
users | user_id (PK) | email, password_hash, created_at | — |
orders | order_id (PK) | status, total_amount, created_at | user_id (FK → users) |
order_items | order_item_id (PK) | quantity, unit_price | order_id (FK → orders), product_id (FK → products) |
products | product_id (PK) | name, description, price, stock_qty | category_id (FK → categories) |
categories | category_id (PK) | name, parent_category_id (self-ref) | — |
Type this prompt to generate the above ERD:
E-commerce data model in Crow's Foot notation: users have many orders; orders have many products through order_items (junction table with quantity and unit_price); products belong to one category; categories can have a parent category (self-referencing). Include primary keys and foreign keys. Normalise to 3NF. Show as an ERD.
Normalisation tiers: 1NF, 2NF, 3NF
Database normalisation is a set of rules for organising a relational schema to minimise redundancy.
- First Normal Form (1NF). Every column holds atomic, indivisible values. No repeating groups or arrays stored in a single column. Every row is unique, identified by a primary key.
- Second Normal Form (2NF). Meets 1NF, and every non-key column is fully dependent on the entire primary key — not just part of it. Eliminates partial dependencies in tables with composite primary keys.
- Third Normal Form (3NF). Meets 2NF, and no non-key column depends on another non-key column (no transitive dependencies). If
category_nameis stored on theproductstable alongsidecategory_id, it violates 3NF — move it to acategoriestable.
flow-chart.io generates 3NF-normalised table structures by default. You can ask for a denormalised design explicitly if your use case (e.g., a reporting schema or data warehouse) calls for it.
ERD best practices
- Every entity must have a surrogate primary key (e.g., id SERIAL or UUID). Avoid composite natural keys as PKs — they make joins brittle.
- Place the foreign key column on the 'many' side of a one-to-many relationship, never on the 'one' side.
- Represent many-to-many relationships with an explicit junction table. Name it after both entities (order_items, user_roles, product_tags).
- Label cardinality on both ends of every relationship line — 'orders has one user' and 'user has many orders' must both be readable.
- Use NOT NULL for required foreign keys. A nullable FK means the relationship is optional; annotate this with a zero end marker.
- Keep one ERD per bounded context. Split large applications by domain (e-commerce, inventory, CRM).
Frequently asked questions
- What is the difference between an ERD and a schema diagram?
- An entity-relationship diagram (ERD) is a conceptual or logical model: it shows the entities in a domain, their attributes, and the relationships between them, expressed in notation (Crow's Foot or Chen) that is database-agnostic. A schema diagram is a physical model tied to a specific database engine — it shows tables, columns with exact data types, indexes, and constraints as they exist in the actual database. ERDs are the right tool for design and communication; schema diagrams are the right tool for implementation review and database documentation.
- What is the difference between Crow's Foot notation and Chen notation?
- Crow's Foot notation represents cardinality at the end of each relationship line using graphical markers: a vertical bar for 'one', a circle for 'zero', and a three-pronged fork (the crow's foot) for 'many'. Chen notation uses diamonds for relationships, rectangles for entities, and ellipses for attributes, with cardinality expressed as numbers (1, N, M) on the lines. Crow's Foot is the standard for practical database design work; Chen is more common in academic ER modelling.
- What is database normalisation, and does flow-chart.io help with it?
- Normalisation is the process of organising a relational schema to reduce data redundancy and improve integrity. First Normal Form (1NF) requires atomic values and unique rows. Second Normal Form (2NF) requires that every non-key column is fully dependent on the whole primary key. Third Normal Form (3NF) requires that every non-key column depends only on the primary key and not on other non-key columns. flow-chart.io generates 3NF-normalised table structures by default when you describe your data model in plain language.
- Does flow-chart.io support PostgreSQL and MySQL schema ERDs?
- Yes. You can describe a data model in plain language and ask for it expressed in PostgreSQL or MySQL conventions. The generated ERD labels columns with appropriate data types for the target platform (e.g., SERIAL vs AUTO_INCREMENT for primary keys, TIMESTAMPTZ for PostgreSQL). The SQL DDL export generates CREATE TABLE statements for the target platform.
- Is the generated ERD editable, or is it a static image?
- Fully editable. Every entity box, attribute row, relationship line, and cardinality marker is a typed object in the diagram's scene graph. You can rename any entity or attribute, change a column's data type label, add or remove columns, change the cardinality on a relationship, or reroute a foreign key connection. Nothing is flattened to pixels until you explicitly export to PNG.
- Can flow-chart.io export SQL DDL from an ERD?
- Yes. The SQL DDL export generates CREATE TABLE statements with primary key definitions, foreign key constraints, and NOT NULL markers derived from the required/optional cardinality markers on the diagram. The output targets ANSI SQL by default and can be flavoured for PostgreSQL or MySQL on request. This is available on Pro and Team plans.
- How many ERDs can I generate on the free plan?
- The free plan includes 40 AI credits per month — roughly 8–10 standard ERDs depending on the number of entities and relationships. No credit card required. Pro and Team plans include higher credit limits, SQL DDL export, PDF export, JSON export, version history, and team sharing features.