Often one reads a book or hears a presenter making a pun about relational theory being called “relational” because of entities being “related.” Such references are nothing but misplaced puns. Relational theory derives the relational in the name from the idea that a “relation” is a mathematical term synonymous for a “set” and each entity represents a set of some sort. However, relationships between entities are still a very important concept albeit not an eponymous one.
What is a relationship? For our purposes, two objects, two ideas are “connected” somehow, and that connection is a relationship. Generically there are three kinds of relationships, one-to-one, one-to-many, and many-to-many. These names describe the expected number of relationship members on each side of the relationship. Variations abound as each side may need both a minimum and a maximum value established for announcing the number of possible members one side or the other may support. So, we could have zero-or-one-to-one, zero-or-one-to-many, one-to-zero-or-many, and so forth.
For an example we have an ORDER entity, and an ORDER has one or more ORDER LINEs. The nature of that connection, or association, in a relational model is evidenced by the existence of what is called a foreign key. The foreign key is created by taking the primary key from the “one” side of a relationship and placing those attributes on the “many” side of the relationship. On the ORDER LINE entity, the primary key of the ORDER entity, say the Order Number, would exist as a foreign key allowing the connection between the two entities. SQL queries would ultimately use these columns on the implemented tables for joins.
If on the “one” side of such a relationship, we have a circumstance allowing for a zero or one —which in this example would be the slightly irrational business rule of “an ORDER LINE may have zero or one ORDERs”—then that foreign key would need to be defined so that it allows for the foreign key attribute to be null, supporting that zero idea.
In a one-to-one relationship the designer needs to ascertain a dominant side to be the “parent” for the relationship. The “child” side is the one to have the foreign key placed within the entity. Many-to-many relationships would be a problem for defining a foreign key as there is no “one” side in such an arrangement. However, many to many is more an idea than something that can be implemented directly in a relational database. As data models are refined the many-to-many idea is replaced by the concept of an associative entity. The relationship itself is upgraded into an entity.
This new associative entity has a one-to-many relationship with each of the entities sharing the many-to-many relationship. Foreign keys may now work as normal. Assuming a many-to-many relationship between a CAR entity and a DRIVER entity, we might create an associative entity named CAR DRIVER, the CAR DRIVER entity would contain an instance for each specific car and specific driver combination that occurs. The CAR DRIVER entity would have a foreign key into the CAR entity, as a CAR may have zero-to-many CAR DRIVERs, and similarly a DRIVER may have zero-to-many CAR DRIVER instances. If a CAR has no DRIVERs, or a DRIVER drives no CARs, then no instances would exist inside the CAR DRIVER entity. So, for this no driver/no car circumstance none of the foreign keys need worry about allowing for a null. Relationships are fundamental to a properly functioning normalized relational database design. Identifying and implementing the business accepted relationships between the data objects in scope provides for designs that are easy to use and understand.