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:
Because the relationships between the JoinActorsMovies table and the Actors and Movies tables are both many-to-one, the primary key in the Actors / JoinActorsMovie relationship is in the Actors table (which is mapped to the parent element -- <Actor> -- in the XML document) and the primary key in the JoinActorsMovie / Movies relationship is in the Movies table (which is mapped to the child element -- <Movie> -- in the XML document).
The JoinActorsMovies table has two foreign keys. The Actor_FK key maps the ActorID column to the ID column in the Actors table and the Movie_FK key maps the MovieID column to the ID column in the Movies table.
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>