Q: How do I map many-to-many relationships in XML-DBMS?

Applies to: 1.x, 2.0

A many-to-many relationship is one in which entities in one class relate to many entities in another class and vice versa. An example is the relationship between actors and movies: each actor is in (relates to) many movies and each movie stars (relates to) many actors.

In a relational database, many-to-many relationships are modeled through join tables, which map entities in one class (such as actors) to entities in another class (such as movies). For example, the following tables might be used to model the actors / movies relationship:

   Actors   JoinActorsMovies   Movies
   ------   ----------------   ------
   ID       ActorID            ID
   Name     MovieID            Title
   Sex                         Rating

These tables might contain the following data, which show, for example, that Al Pacino acted in Serpico and The Godfather, and that The Godfather had the actors Al Pacino and Marlon Brando:

   Actors                  JoinActorsMovies   Movies
   ---------------------   ----------------   ---------------------
   (1, Al Pacino, M)       (1, 1)             (1, Serpico, R)
   (2, Marlon Brando, M)   (1, 2)             (2, The Godfather, R)
   (3, Jack Kehoe, M)      (2, 2)
                           (3, 1)

While there are a variety of ways to represent this data in XML, the one supported easily by XML-DBMS requires (a) that the XML document shows a single hierarchical view through the data and (b) that the join table is mapped to a class element. For example:

   <Actors>
      <Actor ID="1">
         <Name>Al Pacino</Name>
         <Sex>M</Sex>
         <Movies>
            <Movie ID="1">
               <Title>Serpico</Title>
               <Rating>R</Rating>
            </Movie>
            <Movie ID="2">
               <Title>The Godfather</Title>
               <Rating>R</Rating>
            </Movie>
         </Movies>
      </Actor>
      <Actor ID="2">
         <Name>Marlon Brando</Name>
         <Sex>M</Sex>
         <Movies>
            <Movie ID="2">
               <Title>The Godfather</Title>
               <Rating>R</Rating>
            </Movie>
         </Movies>
      </Actor>
      <Actor ID="3">
         <Name>Jack Kehoe</Name>
         <Sex>M</Sex>
         <Movies>
            <Movie ID="1">
               <Title>Serpico</Title>
               <Rating>R</Rating>
            </Movie>
         </Movies>
      </Actor>
   </Actors>

IMPORTANT! I just noticed that this isn't quite right. Rather than grouping all of the Movie elements into a single Movies element, XML-DBMS will generate a separate Movies element for each row in the join table. As a result, each Movie element will be wrapped in its own Movies element -- not an ideal situation. Looks like you will have to use XSLT to get rid of these when exporting data from the database and add them when importing data into the database. Sigh. Back to the rest of the article.

It is important to note that the data in the Movies table is duplicated in this document. This is because the XML document takes a single hierarchical view across what is, in fact, a graph of data. While this causes no problems when constructing the XML document from the database, it does cause problems when inserting data into the database with version 1.x. This is because version 1.x will try to insert the same data (such as that about the movie Serpico) each time it is encountered, resulting in a duplicate key error. This is not a problem in version 2.0, where the UpdateOrInsert or SoftInsert actions can be used.

The map document for this relationship uses three ClassMaps: one to map the <Actor> element type to the Actors table, one to map the <Movies> element type to the JoinActorsMovies table, and one to map the <Movie> element type to the Movies table. The ClassMap for the Actor element type is straightforward. Note that it has a <RelatedClass element pointing to the ClassMap for the <Movies element, shown below using the version 2.0 mapping language:

   <ClassMap>
      <ElementType Name="Actor"/>
      <ToClassTable Name="Actors"/>
      <PropertyMap>
         <Attribute Name="ID"/>
         <ToColumn Name="ID"/>
      </PropertyMap>
      <PropertyMap>
         <ElementType Name="Name"/>
         <ToColumn Name="Name"/>
      </PropertyMap>
      <RelatedClass KeyInParentTable="Unique">
         <ElementType Name="Movies"/>
         <UseUniqueKey Name="PrimaryKey"/>
         <UseForeignKey Name="Actor_FK"/>
      </RelatedClass>
   </ClassMap>

The ClassMap for the <Movies element type is the interesting one. Of note, it has no PropertyMap elements. This is because none of the data in the JoinActorsMovies table is of interest to the XML document: the actor ID is included in the <Actor element and the movie ID is included in the <Movie element. The ClassMap does have one RelatedClass element, which points to the ClassMap for the <Movie element.

   <ClassMap>
      <ElementType Name="Movies"/>
      <ToClassTable Name="JoinActorsMovies"/>
      <RelatedClass KeyInParentTable="Foreign">
         <ElementType Name="Movie"/>
         <UseUniqueKey Name="PrimaryKey"/>
         <UseForeignKey Name="Movie_FK"/>
      </RelatedClass>
   </ClassMap>

There are two things worth noting here:

The ClassMap for the <Movie element is the simplest: it just contains PropertyMaps for the movie's ID, name, and rating:

   <ClassMap>
      <ElementType Name="Movie"/>
      <ToClassTable Name="Movies"/>
      <PropertyMap>
         <Attribute Name="ID"/>
         <ToColumn Name="ID"/>
      </PropertyMap>
      <PropertyMap>
         <ElementType Name="Title"/>
         <ToColumn Name="Title"/>
      </PropertyMap>
      <PropertyMap>
         <ElementType Name="Rating"/>
         <ToColumn Name="Rating"/>
      </PropertyMap>
   </ClassMap>

Back to the XML-DBMS FAQs