Types of Relationships Between Tables

When a column contains the values of the field of another table, it's called a foreign key. It's responsible for the relationship between the tables.

There are three types of relationships:

In a one-to-one relationship, each row in a table is connected with one and only one row in the other table. It's as if a table has been split in half. This is a rare type of relationship and is predominantly used for security reasons.

In a one-to-many relationship, each row of a table matches multiple rows in another table.

In a many-to-many relationship, several rows from one table match several rows from another table. This type of relationship produces an association table, which combines the primary keys of both tables.

ER Diagrams

The structure of databases can be shown with ER (entity-relationship) diagrams. They show tables and the relationships between them.

Tables are shown as rectangles (boxes) with two parts. The table name goes in the upper part.

In the lower part we see lists of table fields with an indication of which keys they are related to: primary or foreign. The keys are usually marked PK (primary) or FK (foreign), but they can be also marked with a key icon, a #, or another symbol.

ER diagrams also show relationships. The end of the line connecting two tables indicates whether one or several values from one table match values from the other.