Cookies help us display personalized product recommendations and ensure you have great shopping experience.

By using this site, you agree to the Privacy Policy and Terms of Use.
Accept
SmartData CollectiveSmartData Collective
  • Analytics
    AnalyticsShow More
    sales and data analytics
    How Data Analytics Improves Lead Management and Sales Results
    9 Min Read
    data analytics and truck accident claims
    How Data Analytics Reduces Truck Accidents and Speeds Up Claims
    7 Min Read
    predictive analytics for interior designers
    Interior Designers Boost Profits with Predictive Analytics
    8 Min Read
    image fx (67)
    Improving LinkedIn Ad Strategies with Data Analytics
    9 Min Read
    big data and remote work
    Data Helps Speech-Language Pathologists Deliver Better Results
    6 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
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

Readability of Decision Trees
How Your Hadoop Distribution Could Lose Your Data Forever
Gen Y Won’t Jump the Facebook Ship Just Yet
The problem with the RDBMS (Part 3) – Let’s Get Real
Analyzing Healthcare in Sweden

PERSON …

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:

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

sales and data analytics
How Data Analytics Improves Lead Management and Sales Results
Analytics Big Data Exclusive
ai in marketing
How AI and Smart Platforms Improve Email Marketing
Artificial Intelligence Exclusive Marketing
AI Document Verification for Legal Firms: Importance & Top Tools
AI Document Verification for Legal Firms: Importance & Top Tools
Artificial Intelligence Exclusive
AI supply chain
AI Tools Are Strengthening Global Supply Chains
Artificial Intelligence Exclusive

Stay Connected

1.2kFollowersLike
33.7kFollowersFollow
222FollowersPin

You Might also Like

Marketing a book, country by country

9 Min Read

Reading – Viral Data in SOA: An Enterprise Pandemic

2 Min Read
understand the difference between fact tables and dimension tables
Big Data

Data Driven Companies Must Understand Differences Between Fact Tables & Dimension Tables

5 Min Read

Project Cartoon: Data Modeling – Different Points of View

1 Min Read

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

ai in ecommerce
Artificial Intelligence for eCommerce: A Closer Look
Artificial Intelligence
AI and chatbots
Chatbots and SEO: How Can Chatbots Improve Your SEO Ranking?
Artificial Intelligence Chatbots Exclusive

Quick Link

  • About
  • Contact
  • Privacy
Follow US
© 2008-25 SmartData Collective. All Rights Reserved.
Go to mobile version
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?