It’s complicated! Relational Databases.

I’ve been tasked to create the schemas for our databases and it is all about relationships! One-to-one relations, one-to-many relationships, and many-to-many relationships. These relationships give relational databases an opportunity to split and store data between in different tables. Each table is used to define a specific set of information like a table for users, a table for customer information, or a table with form data. Today we will go over the different types of relationship and their use cases.

One-to-many: The most common type of relationship in relational databases. This occurs when one element (we’ll just call it the parent element) may be linked to many (child) elements. But the child element can only be linked to one element. In the parent/ child example, a parent can have multiple children but each child can only have one parent. In this case you can use a foreign key on the many side of the relationship linking back to the one side of the relationship when creating a table.

One-to-one: This refers to a relationship when one (Husband) element may only be linked to one element (Wife). In a relational database, one row in a table can only be linked to one row in another table. Another example would be every person being assigned one social security number. Creating two separate tables for one-to-one relationships are grossly unnecessary. It makes much more sense to include both of these items in the same table.

Many-to-many: These relationships occur when one (cousin) can have many of another element (cousin). Another example is many customers purchasing different products and different products being purchased by many customers. In these cases, you will need a join table to map the many to many associations.