Do you Understand Many-to-Many Relationships ?

Associative entities are represented differently in various data models

5 minute read

In the Past

A many to many relationship is represented with an associative entity. Experts and non-experts in IT field know that this is one of the most important topics in Entity-Relationship and Relational theory. Seven years ago, when I started my data modeling saga, searching for SQL joins returned numerous results and few of them were on XML data. But I could hardly find any discussion about how a join table is related to the theory of two international data model standards, namely RDF and Topic Maps. A year later, in 2010 W3C started publishing “A Direct Mapping of Relational Data to RDF”. Coincidentally, back in 2009-2010 the NoSQL movement was born and Graph Databases started becoming a hot topic but nodes and edges were shrouded in disorientation for users of relational databases.

In the Present

It is 2016 and in my opinion the situation with associative entities has become darn confusing. Edges of a Property Graph data model are bidirectional but RDF links are unidirectional. A bridge-table of an Entity-Relationship schema has links only at the many side, i.e. we have an intersection of two one-to-many relationships. Foreign keys of a bridge table can be conveyed to RDF objects but for integrity and cardinality constrains you have to use OWL. Topic Maps use bidirectional associations with roles, therefore they are closer to the property graph data model. Each data model has a query language SQL, SPARQL, TMQL, Cypher, OrientDB SQL and GraphQL. There are significant differences among them and you have to understand the model in order to process or store efficiently data.

Epilogue

I think I made my point, but most important is the firm decision I took to clear a bit this info-glut with a series of articles in associative data modeling written with a hands-on practice style:

  • Part 1/6 is about Entity-Relationship data model
  • Part 2/6 discusses Topic-Map data model
  • Part 3/6 is focusing on Property Graph data model
  • Part 4/6 is explaining RDF Graph data model
  • Part 5/6 is a demonstration of QlikView associative data model
  • Part 6/6 presents R3DM/S3DM associative semiotic hypergraph data modeling perspective

Cross-References

R3DM Project Posts

2017

Associative Semiotic Hypergraph API in Mathematica for Next-Generation BI Systems
European Wolfram Technology Conference 19-20 June 2017 in Amsterdam
My speech at European Wolfram Technology Conference 2017 about a new data modeling framework R3DM/S3DM that is implemented on top of OrientDB graph database and coded in Wolfram Mathematica

Are our old data model standards out of shape ?
An overview of critical points to consider when modeling with R3DM/S3DM
Both Topic Maps and RDF/OWL exhibit signs of aging. These signs do not indicate maturity levels but on the contrary they signal a re-examination of the data modeling, information representation problem

The three dimensions of AI and a fourth one as the key to unlock them
Comments on a review of AI by John Launchbury, special assistant to DIRO, DARPA
Although there has been significant progress with first and second generation AI systems in reasoning, learning and perceiving, abstraction has not been part of the game. The mechanism of abstraction can unify these other three processes.

Associative Data Modelling Demystified: Part 6/6
R3DM/S3DM: Build Powerful, Meaningful, Cohesive Relationships Easily
Demonstration of a new data model framework that transforms OrientDB into a HyperGraph Database

Data Modelling Topologies of a Graph Database
Definition and Classification of Graph Databases into Three Categories
The associative data graph database model is still a heavy hitter, stacking up well against property graphs and triples/quadruples. Expect a comeback.

A Quick Guide on How to Prevail in the Graph Database Arena
A brief discussion on criteria to meet a differentiation strategy for graph databases
A swift introduction to the key factors that influence the performance and unification character of graph databases

Associative Data Modeling Demystified: Part 5/6
Qlik Associative Model
Qlik's competitive advantage over other BI tools is that it manages associations in memory at the engine level and not at the application level. Every data point in every field of a table is associated with every other data point anywhere in the entire schema.

2016

Associative Data Modeling Demystified: Part 4/6
Association in RDF Data Model
In this article we will see how we can define an association in RDF and what are the differences with other data models that we analyzed in previous posts of our series

Do you Understand Many-to-Many Relationships ?
Associative entities are represented differently in various data models
It is 2016 and in my opinion the situation with associative entities has become darn confusing. Edges of a Property Graph data model are bidirectional but RDF links are unidirectional.

Associative Data Modeling Demystified: Part 3/6
Association in Property Graph Data Model
In this article, we continue our investigation with the Property Graph Data model. We discuss how a many-to-many relationship is represented and compare its structure in other data models

Associative Data Modeling Demystified: Part 2/6
Association in Topic Map Data Model
In this post, we demonstrate how Topic Map data model represents associations. In order to link the two, we continue with another SQL query from our relational database

Associative Data Modeling Demystified: Part 1/6
Relation, Relationship and Association
In this article, we introduce the concept of association from the perspective of Entity-Relationship (ER) data model and illustrate it with the modeling of a toy dataset

2015

Towards a New Data Modelling Architecture
Part 2: Atomic Information Resource (AIR)
We introduce the Atomic Information Resource (AIR) unit of R3DM conceptual framework

Towards a New Data Modelling Architecture
Part1 - Relational/ER Constructs in Wolfram Language
We start with terms and constructs that most of us are familiar with from the Relational and Entity-Relationship database management systems