Leonardo Da Codinchi Leonardo Da Codinchi - 1 year ago 37
MySQL Question

How to spot the relationship in RDBMS?

I was studying about relationships in RDBMS.I have understood the basic concept behind mapping relation ship,but I am not able to spot them.

The three possibilities :

  1. one to many(Most common) requires a PK - FK relationsip.Two tables involved

  2. many to many(less common) requires a junction table.Three tables Involved

  3. one to one(very rare). One table involved.

When I begin a project,I am not able to separate the first two conditions and I am not clear in my head.
Examples when I study help for a brief moment,but not when I need to put these principles in to practice.

This is the place where most begineers falter.
How can I spot these relationships.Is there a simpler way?

Answer Source

Don't look at relationships from a technical perspective. Use analogies and real-life examples when trying to envision relationships in your head.

For example, let's say we have a library database. A library must have books.


Each Book may have been written by multiple Authors and each Author may have written multiple Books. Thus it is a many-to-many relationship which will reflect into 3 tables in the database.


Each Book must also have a Publisher, but a Book may only have one Publisher and a Publisher can publish many Books. Thus it is a one-to-many relationship and it reflects with the PublisherId being referenced in the Books table.

A simple analogy like this one explains relationships to their core. When you try to look at them through a technical lens you're only making it harder on yourself. What's actually difficult is applying real world data scenarios when constructing your database.