What is earth

ERD (entity relationship diagram)

An Entity Relationship Diagram (ERD) is a graphic representation of the table structure in a database. As the name suggests, an Entity Relationship Diagram represents entities and relationships between them. The entities are the tables in a database. The relationship forms the relationships between the tables.


In relational databases, relationships between databases are defined with so-called primary keys (PK) and foreign keys (FK). The primary key of a table is a unique identifier for the table rows. This can be inserted as a foreign key in the rows of another table. The relationships between the rows of one table and the rows of the other table are then recognized by matching key values ​​in the two tables. As in the table example, there are two tables:

The table shows that Hans lives in Cologne and Franz and Franziska in Hamburg. In an ERD diagram, such relationships are represented by the tables being represented by rectangles and the relationships by connecting lines, as in a block diagram. Countless forms of representation have developed over time, all of which cannot be shown here. Especially the representation of the relationship lines knows different forms. The most common form is called "crow feet". What most of them have in common, however, is that they try to clarify the nature of the relationship between the tables.

The most important types of relationships are:

1: 1/0: Each primary key can appear a maximum of once in the foreign key table.

1: n: Each primary key can appear one or more times in the foreign key table. This is the most common form.

m: n: Both tables can refer to each other more than once. This form cannot be mapped directly in relational databases and must be resolved with a mapping table, for example. The topic of Join also deals with the types of relationships.

An important aspect of making ERD diagrams is normalization. Put simply, it prevents data from being stored redundantly. In the example above there is a table "Residence", so that the residence does not have to be entered twice in the Person table and would therefore be redundant.

A large part of the relationships in an ERD diagram is therefore created by the rules of normalization, e.g. with hierarchical forms such as country - region - city - zip code - address, which, strictly speaking, all have to be broken down into separate tables. Special forms of ERD diagrams are the so-called star schemas and snowflake schemas, which are mainly used in data warehouses and OLAP applications. You always have a central table around which the other tables are in a star shape or Group them in a snowflake shape.