Modeling a Many-to-Many reflexive relationship

Response to John Sullivan post on LinkedIn, 2020 Sept 3.

His paper: https://www.linkedin.com/pulse/better-database-design-part-1-john-sullivan/

shows an entity labeled Organization Structure having two defined relationships with an Organization entity.  The relationship arcs are labeled Parent Organization and Child Organization.  In the descriptive text he states that the Organization Structure entity has two fields: Parent Org ID, and Child Org ID.  Each field in a foreign key to its respective Organization entry, and together they form a composite key for the Organization Structure entity.

                                     Parent Organization

Organization

Organization Structure

                                 >O-----------------------|-

                                              

                                 >O-----------------------|-

                                      Child Organization

Everest responds:

John, I hate to say it but you have fallen into the TABLE THINK trap, as has everyone else who tries to model Organizational Structure this way. You tell me how many business users will understand what you have called the "Organization Structure" entity in the diagram.  You are right to say they should be greyed out.  In fact, they should not be there at all.  They are there solely for the purpose of implementation (in a relational DBMS).  How many times do we say, "the logical model should be independent, i.e., show nothing related to implementation or physical storage?  The reality is that this representation is forced when you have to implement in 1NF relations (tables) – a relational model can only directly represent at most a 1:Many binary relationship.  That is because, in a relational database, it is not possible to directly represent a many-to-many relationship – all attributes must be at most single valued. The proper logical diagrammatic representation would be an arc representing a relationship on the Organization entity to itself with a fork (for manyness) at each end of the arc. The labels on the arc are role names for each Organization as they participate in the relationship.

Organization

                                         Parent

                                     >-----------

                                                        |

                 v                                     |

                 |___Child_______| 

Most people will have no difficulty understanding the notion of a many-to-many relationship, once they understand the meaning of the fork (already used in IE notation).  Then we need to label the ends of the arc with the ROLE each instance plays in the relationship, either parent or child.  We also need to apply some constraint declarations to this relationship.  For example, we probably don't want to allow an organization to be both parent and child in the same relationship instance.  We may also want to exclude a circular chain of parent-child relationships.  As an aside, let me say that all of this is handled quite nicely and precisely in Halpin's Object Role Modeling (ORM) scheme, a variant of fact modeling, and the focus of my years of teaching advanced data modeling at the University of Minnesota.