An Entity Relationship Diagram (ERD) is a graphical representation of the table structure in a database. As the name implies, an Entity Relationship Diagram represents entities and relationships between them. The entities are the tables in a database. The relationship represents 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. Matching key values in the two tables then identify the relationships between the rows of one table and the rows of the other table. If there are two tables as in the table example:
The table shows that Hans lives in Cologne and Franz and Franziska live in Hamburg.
In an ERD diagram, such relationships are shown by representing the tables by rectangles and the relationships by connecting lines, as in a block diagram. Over time, countless forms of representation have evolved, not all of which can be shown here. Especially the representation of the relationship lines knows different forms. The most common form is called "Crow feet ". But what most of them have in common is that they try to show the kind of relationship between the tables.
The most important types of relationships are:
1:1/0: Each primary key can occur at most once in the foreign key table.
1:n: Each primary key can occur once or more in the foreign key table. This is the most common form.
m:n: Both tables can refer to each other multiple times. This form cannot be mapped directly in relational databases and must be resolved using a mapping table, for example. The Join topic also deals with relationship types.
An important aspect in the creation of ERD diagrams is normalization. In simple terms, it prevents data from being stored redundantly. For example, in the example above, there is a "Residence" table 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 are therefore created by the rules of normalization, e.g. in the case of hierarchical forms such as country - region - city - postal code - address, which, strictly speaking, must all be resolved into their own tables. Special forms of ERD diagrams are the so-called star schemas and snowflake schemas, which are used primarily in data warehouses and OLAP applications. They always have a central table around which the other tables are grouped in a star or snowflake shape.