We use cookies, including third-party cookies from Google to serve personalized ads through AdSense, to operate this site and understand how it is used. By continuing to browse, you accept this use. See our Privacy Policy and Terms of Use for details, including how to opt out of personalized advertising.
Accept
SmartData CollectiveSmartData Collective
  • Analytics
    AnalyticsShow More
    Flat editorial illustration: The article is about joining public web signals with internal business metrics so BI teams can inter
    Alternative Data in BI: Bringing Public Web Data Into Your Dashboards
    23 Min Read
    Flat editorial illustration: The article is about automating the collection, preparation, and delivery of marketing data across p
    From Data to Dashboards: 5 Marketing Reporting Automation Software Picks
    12 Min Read
    Flat editorial illustration: The article contrasts passive follower totals with a behavioral path from attention and trust to cli
    Creator Analytics Beyond Follower Count: Seven Metrics That Measure Audience Behavior
    11 Min Read
    Flat editorial illustration: The article connects AI and marketing analytics with managerial judgment grounded in consumer behavi
    How an Online MBA in Marketing Connects AI and Data Analytics
    11 Min Read
    What Kind of Problem-Solving Distinguishes Data Analysts From Software Engineers -- AI-generated illustration
    What Kind of Problem-Solving Distinguishes Data Analysts From Software Engineers
    7 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-25 SmartData Collective. All Rights Reserved.
Reading: Resolving Many-to-Many Relationships
Share
Notification
Font ResizerAa
SmartData CollectiveSmartData Collective
Font ResizerAa
Search
  • About
  • Help
  • Privacy
Follow US
© 2008-23 SmartData Collective. All Rights Reserved.
SmartData Collective > Big Data > Data Mining > Resolving Many-to-Many Relationships
Data Mining

Resolving Many-to-Many Relationships

KarenLopez
KarenLopez
7 Min Read
Resolving Many-to-Many Relationships
Illustrative image generated with OpenAI gpt-image-1.
SHARE

Ever have one of those long, back and forth email exchanges where when you’re done you feel as if you’ve written a few chapters of an upcoming book?  I’m going to be taking a few of these from my archive to share with others who may be getting the same types of questions from non-modelers.

Today, we will be discussing the question of why does resolving a many-to-many relationship require a new relationship?  Why do we have to create a new entity?


This is a fairly classical data modeling concept.  It might be referred to as an intersectional entity, a resolution entity, an associative entity, or in short form, a M:N entity.  Silverston calls this concept Intersection or Association entity.  Simsion and Witt call these Intersection entities, Associative entities, Resolution entities, or Relationship entities.  Riordan calls these Junction tables, even in the data model.  I’d say that the most common terms are Intersection or Associative entity, but I think it depends on what tool one uses and what types of data modeling books one reads.

A typical example might start out with a many-to-many relationship, say between cars and people:

More Read

Because It's the Weekend: Data Mining Wine
Because It’s the Weekend: Data Mining Wine
Uncertainty Coefficients for Features Reduction – Comparison with LDA Technique
Interview –Michael Zeller CEO,Zementis
Redefining Loyalty Programs with Big Data and Hadoop
From Big Data to Smart Data: Supporting Critical Business Decisions

PERSON >-|—owns—-o-< CAR

This is a many-to-many relationship:  A person may own cars and a car may be owned by more than one person.  In my made up example, my business rule is that a car has to be owned by at least one person (which is sort of bending the real world rules, but bear with me).  We’ll also ignore the fact that there are other relationships between cars and people.  We’ll focus just on ownership.

We can’t leave many-to-many relationships that way in a relational database, so we need to resolve them.  There are also normally real business reasons why they need to be resolved, but I’ll leave that for another discussion, too.

To resolve a many-to-many, you create a new entity, in my example, OWNERSHIP:

PERSON – ||—registers—-0-< ownership >-|—–is registered on—–CAR

OWNERSHIP keeps track of the relationship between a specific person and a car.  It becomes the list of just two things: a person and a car.

Karen owns car 1234

Kirstin owns car 2345

Rob owns car 1234

Rob owns car 3456

In this list, notice that Karen and Rob jointly own car 1234.  Car 2345 is owned only by Kirstin and car 3456 is owned only by Rob.  Karen owns only one car, Rob owns two, and Kirstin owns one.  We can also assume that Richard owns no cars (according to the data) because he has no entry in OWNERSHIP. 

In the attributed model, the entity OWNERSHIP would look like this:

OWNERSHIP

=======================

Person.PersonID (fk)

Car.CarID (fk)

…in a very simple world where we don’t worry about time.   The real world reason why we need these associative entities is because they almost always involve an aspect of time and other attributes, but we’ll ignore that for now.

Each foreign key in this associative entity came from the relationship from CAR and PERSON to OWNERSHIP.  That’s why we need two relationships.  We could not drop one of them, because each plays the part of associating the two concepts to each other, one pair at a time. 

The PARTY AFFILIATION entity is a special case of the associative entity above because it started out (at least conceptually), as a recursive relationships (a relationship from an entity to itself).  These are more difficult to draw in ASCII data modeling, so I’ll just duplicate the entity:

PARTY >-o—–is affiliated with—–o-< PARTY >

So just imagine the relationship being “dog eared” back to the same entity.

We created PARTYAFFILIATION  to do the same job as OWNERSHIP:

PARTY -||——-is affiliated via—–o-< party affiliation >-0———-is affiliated via—–|| PARTY

It would result in an associative entity that looked like this:

PARTYAFFILIATION

=====================

PARTY.PARTYID (fk)

PARTY.PARTYID (fk)

…which we can’t have, since the foreign key would have migrated twice with the same name.  So we rolenamed one of the relationships for it to be:

PARTYAFFILIATION

=====================

PARTY.PARTYID (fk)

PARTY.SUBPARTYID (fk)

Personally, I prefer to rolename both in these cases so it is very clear which role the foreign key is taking, but it works either way.

So if you check out your books on data modeling for the terms I mentioned in the beginning, you might come up with some more examples of why many-to-many relationships resolve to two relationships with an associative entity in the middle.

 

Technorati Tags: Data Model,M:N,Many to Many,Relationships,Associative Entity,Intersection Entity,Intersectional Entity,Recursive Entity

TAGGED:data modeling
Share This Article
Facebook Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

Flat editorial illustration: The article is about joining public web signals with internal business metrics so BI teams can inter
Alternative Data in BI: Bringing Public Web Data Into Your Dashboards
Big Data Business Intelligence Exclusive Web Analytics
Flat editorial illustration: A SaaS-editorial scene showing product usage, support patterns, and renewal feedback informing a fle
Using Product Usage Analytics and Support Data to Build a B2B Product Roadmap
Business Intelligence Infographic
From Hiring To Payroll: Managing HR Data Across Business Systems -- AI-generated illustration
From Hiring To Payroll: Managing HR Data Across Business Systems
Big Data Business Intelligence Infographic
Flat editorial illustration: The article is about automating the collection, preparation, and delivery of marketing data across p
From Data to Dashboards: 5 Marketing Reporting Automation Software Picks
Analytics Business Intelligence Exclusive Marketing

Stay Connected

1.2KFollowersLike
33.7KFollowersFollow
222FollowersPin

You Might also Like

Marketing a book, country by country
Uncategorized

Marketing a book, country by country

9 Min Read
Data Design Is Not Optional
Best PracticesBig DataCRMData ManagementITPolicy and Governance

Data Design Is Not Optional

4 Min Read
Data Modeling with Generalizations – The Tool Issue
Uncategorized

Data Modeling with Generalizations – The Tool Issue

5 Min Read
Data Modeling Tools: 14 Picks Compared by Modeling Layer in 2026 -- AI-generated illustration
Modeling

Data Modeling Tools: 14 Picks Compared by Modeling Layer in 2026

42 Min Read

SmartData Collective is one of the largest & trusted community covering technical content about Big Data, BI, Cloud, Analytics, Artificial Intelligence, IoT & more.

Chatbots and SEO: How Can Chatbots Improve Your SEO Ranking?
Chatbots and SEO: How Can Chatbots Improve Your SEO Ranking?
Artificial Intelligence Chatbots Exclusive
How To Get An Award Winning Giveaway Bot
How To Get An Award Winning Giveaway Bot
Big Data Chatbots Exclusive

Quick Link

  • About
  • Contact
  • Privacy
Follow US
© 2008-26 SmartData Collective. All Rights Reserved.
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?