Database Schema Diagram Symbols and Notations Explained

If you've ever stared at a database schema diagram and felt lost about what all those shapes, lines, and symbols mean, you're not alone. Understanding database schema diagram symbols and notations is one of those skills that separates someone who can read a database blueprint from someone who's just looking at boxes and arrows. Whether you're a developer working on a new project, a student learning relational databases, or a business analyst trying to make sense of a system, knowing how to interpret these diagrams saves you hours of confusion and prevents costly miscommunication.

What do the basic shapes in a database schema diagram represent?

Every database schema diagram uses a set of visual building blocks. Each shape carries a specific meaning, and once you learn them, reading any diagram becomes straightforward.

Rectangles (or boxes) These represent tables or entities. Each rectangle typically shows the table name at the top, with columns listed inside. This is the most common symbol you'll see in any entity-relationship diagram (ERD).
Ovals (ellipses) These represent attributes or properties of an entity. In older Chen notation ERDs, ovals are used to show column names attached to their parent entity.
Diamonds These represent relationships between entities. You'll mainly see diamonds in Chen notation diagrams, not in the more modern crow's foot style.
Lines Lines connect entities to relationships or to other entities. The style of the line ending (crow's foot, circle, straight line) tells you about the cardinality of the relationship.
Primary key markers A column name underlined or marked with "PK" indicates the primary key, the unique identifier for each row in that table.
Foreign key markers Marked with "FK" or shown with a specific icon, foreign keys indicate columns that reference a primary key in another table.

You can see these symbols applied in a real-world context by looking at a database schema diagram example for an e-commerce website, where tables like Orders, Customers, and Products are connected through foreign key relationships.

What are the most common database schema notations?

There isn't just one way to draw a database schema. Over the years, several notations have become standard, and each uses symbols a little differently.

Chen notation

Introduced by Peter Chen in 1976, this is the original ERD notation. It uses rectangles for entities, ovals for attributes, and diamonds for relationships. Lines connect everything together. Chen notation is common in academic textbooks and is often the first notation students encounter. It's detailed and expressive, but it can get cluttered on complex diagrams.

Crow's foot notation

This is the most widely used notation in professional database design. Instead of diamonds, relationships are shown as lines directly between entity rectangles. The line endings use a "crow's foot" symbol (three prongs) to indicate "many," a single line for "one," and a circle for "zero." For example, a line from Customers to Orders might show "one customer has many orders" using a single line on the customer side and a crow's foot on the orders side.

Crow's foot notation is popular because it's compact and easy to read at a glance. Most modern database tools, including options to generate database schema diagrams from code, default to this notation.

UML class diagram notation

Unified Modeling Language (UML) borrows from object-oriented design and uses class-like boxes to represent tables. Columns are shown as attributes with their data types. Relationships use arrows and multiplicity labels like 1.. (one to many) or 0..1 (zero or one). UML notation is less common for pure database work but shows up when database design is part of a larger software architecture document.

IE (Information Engineering) notation

IE notation is similar to crow's foot but uses slightly different line endings and symbols for cardinality. It was popularized by James Martin and is still used in some enterprise modeling tools. The differences between IE and crow's foot notation are subtle, but if you're using a specific tool, it's worth checking which notation it follows.

How do you read cardinality and participation symbols?

Cardinality describes how many instances of one entity can be associated with instances of another entity. This is the part of a schema diagram that trips people up the most.

Here are the common cardinality symbols in crow's foot notation:

One and only one (mandatory) Shown as a straight vertical line (|). This means exactly one record must exist.
Zero or one (optional) Shown as a circle (O) followed by a straight line (O|). A record may exist but isn't required.
One or many (mandatory many) Shown as a straight line followed by a crow's foot (|<). At least one related record must exist, and there can be many.
Zero or many (optional many) Shown as a circle followed by a crow's foot (O<). Related records may or may not exist, and there can be many.

For example, imagine a Customers table connected to an Orders table. If the line on the Customers side shows | (one) and the line on the Orders side shows O< (zero or many), it means each customer can have zero or more orders, and every order must belong to exactly one customer.

Understanding these symbols is essential when you want to create a database schema diagram from SQL code, since the foreign key constraints in your SQL define these exact relationships.

What do the column-level symbols and annotations mean?

Beyond the table shapes and relationship lines, schema diagrams include annotations inside each table box. These details matter when you're reading a diagram to build or modify a database.

PK (Primary Key) The column that uniquely identifies each row. Sometimes shown with a key icon or underline.
FK (Foreign Key) A column that references a primary key in another table. Sometimes shown with an FK icon or a special marker.
Data types Often listed next to column names, like INT, VARCHAR(255), TIMESTAMP, or BOOLEAN.
NOT NULL constraint Sometimes shown with a filled circle (●) or an asterisk () to indicate the column cannot be empty.
Default values Listed after the data type, such as DEFAULT 'active' or DEFAULT CURRENT_TIMESTAMP.
Unique constraint Sometimes indicated with a "UQ" label or a special icon, meaning no two rows can have the same value in that column.
Indexes Shown with an "IDX" label or a small icon, indicating the column is indexed for faster queries.

What's the difference between logical and physical schema diagrams?

This is a distinction that catches many people off guard. A logical schema diagram focuses on the business concepts entities, attributes, and relationships without worrying about implementation details. A physical schema diagram includes database-specific details like data types, indexes, constraints, and storage parameters.

When you see a diagram with ovals for attributes and diamonds for relationships, it's likely a logical ERD. When you see table boxes with specific column data types, primary keys, and foreign keys, it's a physical schema diagram. Most database developers work with physical schema diagrams day to day.

Common mistakes when reading database schema diagrams

Misreading a schema diagram can lead to wrong assumptions about how data is structured. Here are mistakes to avoid:

Confusing the crow's foot direction The crow's foot (many side) points toward the table that has multiple records, not the table with one record. Getting this backward changes the meaning entirely.
Ignoring optional vs. mandatory participation A circle (O) means the relationship is optional. A straight line (|) means it's required. Skipping over this detail can lead to data integrity problems when you build the database.
Mixing up notations A diagram drawn in Chen notation uses different symbols than crow's foot notation. If you read a crow's foot symbol as if it were Chen notation, you'll misunderstand the relationships.
Assuming all columns are shown Many schema diagrams simplify things and only show key columns. Just because a column isn't in the diagram doesn't mean it doesn't exist in the actual database.
Overlooking junction tables In many-to-many relationships, there's usually a junction (or bridge) table between two entities. If you see two entities with a many-to-many line between them, look for the hidden junction table.

How do different tools display schema diagram symbols?

Different database design tools render symbols with slight variations, which can cause confusion if you switch between tools.

MySQL Workbench Uses a proprietary notation that resembles crow's foot but with its own line ending style. Primary keys are shown with a key icon.
pgAdmin (PostgreSQL) Displays schema diagrams with table boxes, column lists, and relationship lines. The symbols follow a simplified crow's foot style.
dbdiagram.io Uses a clean, minimal crow's foot notation that's easy to read and popular for documentation.
Lucidchart and Draw.io Support multiple notations and let you choose between Chen, crow's foot, and UML styles.
Microsoft Visio Offers database model diagrams with IE notation as the default.

No matter which tool you use, the core concepts stay the same. The shapes always represent tables, the lines always represent relationships, and the line endings always describe cardinality. The visual style just changes slightly.

Quick reference: cheat sheet for schema diagram symbols

Here's a simple reference you can keep handy:

Rectangle / Box Table or entity
Oval / Ellipse Attribute (Chen notation only)
Diamond Relationship (Chen notation only)
Single line (|) One (mandatory)
Circle + line (O|) Zero or one (optional)
Line + crow's foot (|<) One or many
Circle + crow's foot (O<) Zero or many
Underline or PK label Primary key
FK label or icon Foreign key
Dashed line Derived or weak relationship
Double line Total participation (every instance must participate)

Practical checklist: reading any database schema diagram

Identify the notation Is it Chen, crow's foot, UML, or IE? This tells you how to interpret the symbols.
Find the tables Look for rectangles. Read the table name at the top and the column list inside.
Check primary keys Find columns marked with PK, underline, or a key icon.
Trace the relationships Follow the lines from table to table. Note which side is "one" and which side is "many."
Read cardinality symbols Look at line endings to understand whether relationships are mandatory or optional, one-to-one, one-to-many, or many-to-many.
Note foreign keys These tell you how tables connect. A foreign key in one table always points to a primary key in another.
Look for junction tables In many-to-many scenarios, identify the bridge table that connects two entities.
Verify data types and constraints If the diagram is physical, check data types, NOT NULL markers, and default values.

Keep this checklist open the next time you encounter an unfamiliar diagram. Within a few minutes, you'll know exactly how the database is structured and how the tables relate to each other. If you want to practice, try generating your own diagram from an existing SQL schema and see how the symbols map to the code you already understand.