One-to-One SQL Relationship

One-to-One Relationship Basics

In SQL, a one-to-one relationship has the underestimated power to split big tables into small pieces without loosing the performance of a flat table model. In some cases it’s a better solution than a full blown EAV based model.

The perfect use case for a 1:1 relationship are related entities which share basic attributes, like catalog entries for example. But without diving into theory first, take a look at the following entity relationship diagram:

The idea behind this concept is it to have a table that stores basic attributes which are shared among all entities. In case of a catalog entry shared attributes are the product name, a description and a price for example. Attributes which are specific to certain types of products are stored in separate tables. The relationship between these tables is stored by using the same primary key in every table.

 

Implementation

As you can see in the example above, a one-to-one relationship can be implemented by simply using the same primary key for both tables. If a products with the entity id 12345 is stored, this id is used in every table.

In addition you’re able to use different primary keys and add a relationship column to every table, but that approach has different drawbacks: Your SQL statements will be more complex and if you want to use foreign keys, this will result in a circular dependency.