Entity Relationship Diagrams

Entity Relationship Diagrams, or ER Diagrams, are graphic pictures of how rows in the database’s tables relate to other rows. They are dense in information about what data exists and in what tables it can be found.

ER diagrams are useful because they provide at-a-glance overviews. They allow you to locate the data in which you are interested and give guidance as to how to retrieve it.

Most tables have have an id, or key, column that contains a number unique to that row within its table. The id can be used, in perpetuity, to refer to its related row and distinguish it from all the other rows of the table. Ids are arbitrary, although for convenience they are often sequentially generated integers. The name of the column holding the id value is not always Id, although it sometimes is.

A relationship is established between the rows of two tables when an id value from one table appears as data in the other. The relationship notion is made most clear by way of diagrams and examples. The relationship concept is at the heart of relational databases and, while the underlying idea is rather simple, it took many years to develop relational database concepts so don’t expect a full understanding immediately.

When an id value of a row in one table appears as data in a second table, the data in the second table can be used to retrieve the identified row from the first table.[1] When an id value of a row in the first table appears as data only once in the second table, the two tables are said to have a one-to-one relationship. One row in the first table relates to one (or possibly zero) row(s) in the second table. When a row’s id value can appear in more than one row of a second table, the two tables are said to have a one-to-many relationship. One row of the first table can be related to many rows in the second table. One-to-many relationships are more common than one-to-one relationships. In the ER diagrams each table (corresponding to a real-world entity) is a box, and each box contains a list of the table’s columns. The lines between the boxes represent the relationships between the tables.

For example, individuals can transfer between communities zero or more times. A single individual is one kind of entity recorded in the database, one transfer of a single individual between communities is a second kind of entity. Each individual is represented in the database as a single row in a table. Each transfer is likewise represented by a single row in a different table.

The ER diagram of this shows that a row representing a one individual can be “connected to” zero or more rows recording community transfers. The diagram also shows that a transfer must be “connected to” exactly one individual, the individual who is changing communities. A transfer should not be able to exist unless the individual transferring also exists.

• Key
• Demography
• Follows
• Events

Footnotes

[1]

And the reverse is true. The id of a row in the first table can be used to find the row in the second table that holds it.

Page last generated: 2025-04-12 18:47:18 UTC