Entity Relationship Diagrams (ERD): Designing Database Schemas

Before you write a single SQL CREATE TABLE statement or configure an ORM like Prisma or Hibernate, you need to know how your data relates to itself.

The Entity Relationship Diagram (ERD) is the blueprint for your database. While technically not a core UML diagram (ERDs predate UML), modern modeling tools often bundle ERDs with UML because they serve the exact same purpose: visualizing architecture.

Core Components of an ERD

1. Entities (The Tables)

An Entity is a recognizable concept, person, place, or thing about which data can be stored. In a relational database, an entity maps directly to a database table.

They are drawn as rectangles with the name of the entity inside (e.g., Customer, Order, Product).

2. Attributes (The Columns)

Attributes are the properties or traits of an entity. These map directly to the columns of your database table.

In modern "Crow's Foot" notation (the most common for database design), attributes are listed inside the entity rectangle, below the title.

• Primary Key (PK): A unique identifier for a specific row in the table (e.g., user_id). It is usually denoted by a key icon or underlined text.
• Foreign Key (FK): An attribute that creates a link between two tables. It refers to the Primary Key of another table.

3. Relationships (The Lines)

Relationships describe how two entities interact. Lines are drawn between entities to show these connections. The ends of the lines use special symbols to denote the Cardinality and Ordinality.

Understanding Cardinality (Crow's Foot Notation)

Cardinality defines the numerical attributes of the relationship: how many instances of one entity can be associated with instances of another entity?

One-to-One (1:1)

Each record in Table A relates to exactly one record in Table B.

Example: A User has one User_Profile.

One-to-Many (1:N)

The most common relationship. A single record in Table A relates to multiple records in Table B, but a record in Table B only relates to one record in Table A.

Example: A Customer can place many Orders. An Order belongs to exactly one Customer.

In Crow's Foot notation, the "Many" side is drawn as three diverging lines (looking like a crow's foot) touching the entity box.

Many-to-Many (M:N)

Multiple records in Table A can relate to multiple records in Table B.

Example: A Student can enroll in many Courses, and a Course can have many Students.

Note: Relational databases cannot natively handle M:N relationships. You must create a Join Table (also known as an associative entity) between them. In this example, you would create an Enrollment table that holds student_id and course_id.

Generating ER Diagrams from Text

Manually drawing database schemas in tools like MySQL Workbench or draw.io is incredibly slow. Aligning the foreign keys and drawing the crow's foot arrows is tedious.

With AutoUML, you can describe your database relationships in plain English, and the AI will generate the perfect ER Diagram with all the correct foreign keys and crow's foot notations automatically.