ansaurus

Question

How to represent a Many-To-Many relationship in XML or other simple file format?

Answer 1

A:

The only way I can think of to truly represent a many-to-many relationship in an XML, or any other hierarchical structure, is to use many one-to-many relationships.

Here's a simple example:

<list name="listA">
    <notes name="noteA" />
    <notes name="noteB" />
    <notes name="noteC" />
</list>
<list name="listB">
    <notes name="noteB" />
    <notes name="noteC" />
    <notes name="noteD" />
</list>

In other words, each list would have its notes as child nodes.

Many of the notes child nodes would be duplicated in the XML file. The process that populates the database from the XML file would have to take duplicate notes child nodes into account.

The other alternative is to pick one list for each of the notes. A note would be the child node of only one of the lists. The advantage is that there aren't any duplicate notes. The disadvantage is that the XML file does not represent the note to list relationships properly, which may or may not be important.

Gilbert Le Blanc 2010-06-17 22:23:19

This is what I do already. I have each list and then each note in that list as an element of that list. My problem isn't duplicate notes, it's the loss of information when I lose the many-to-many relationship.

CodeFusionMobile 2010-06-17 23:04:37

@CSharperWithJava: I don't understand what information you're losing. Any notes child can have more than one list parent. That notes child would be listed as a child of all of the lists that it has a relationship with.

Gilbert Le Blanc 2010-06-17 23:34:27

I can't assume that two identical note elements are in fact the same note. In your example, both noteB's could be the same note in both lists, while noteC is in fact two different notes.

CodeFusionMobile 2010-06-18 02:10:11

@CSharperWithJava: I only put a name attribute in my example. If you list all of the columns of your table as attributes of an element, then you can't have "two different noteC's".

Gilbert Le Blanc 2010-06-18 07:07:12

All the columns of my table could be identical as well, but still be two separate Notes

CodeFusionMobile 2010-06-19 12:29:56

Answer 2

+5 A:

I think your XML format must make use of some sort of "references". If you prefer the list-to-notes relationship to be visible, then something like the following can be suitable:

<notes>
    <note id="Note1"> ... </note>
    <note id="Note2"> ... </note>
    ...
</notes>

<lists>
    <list id="List1">
        <note_refs>
            <note_ref id="Note1"/>
            <note_ref id="Note4"/>
        </note_refs>
    </list>
    ...
</lists>

If on the other hand you want to see easily the lists associated with a given note, then you can simply invert the roles of lists and notes in my example.

Alternatively, you can represent the many-to-many relationship more symmetrically as a mathematical relation: define all notes, define all lists, and then define a mapping consisting of [list reference, note reference] pairs.

Eyal Schneider 2010-06-17 22:24:14

The final suggestion here is how relational databases would do it: you define two tables, "notes" and "lists" and then a join table with "noteId", "listId" as the primary key (and likely only data). The problem here is that most XML based solutions will make such things harder to query that a relational database would. If I was forced to use XML I would stick with the "note_ref" approach (assuming that lists drive the display and the notes are something you could keep in memory). Otherwise I would switch to a database and join tables. XML is great for some things, but not every thing.

Godeke 2010-06-17 22:45:26

@Godeke The data is stored in a relational database already. This is simply an exported file format.

CodeFusionMobile 2010-06-17 23:02:36

@CSharperWithJava In that case 99% of my comment is mooted and I will simply agree that the Note_Ref approach above will allow you to ensure you don't lose the detail structure of the original (the fact notes were singular entities that were linked to).

Godeke 2010-06-18 20:30:04

Answer 3

A:

My first step would be something simple:

<notes>
  <note>
    <title>Groceries to get</title>
    <list>shopping</list>
    <list>urgent</list>
    <body>eggs, cheese, milk</body>
  </note>
</notes>

Would that fit your needs?

William Pietri 2010-06-17 22:29:24

Answer 4

+1 A:

In a database, the tool for representing many-to-many relationships is an association table. Each row in the table represents an association between two objects. Thus if a note with an ID of 1 appears in lists with IDs of 1, 2, and 3, there would be three rows in the association table:

 ID  NoteID  ListID
 --  ------  ------
  1       1       1
  2       1       2
  3       1       3

You can get a note and all of its related lists with a query like this:

SELECT [columns] FROM Association
   JOIN Notes ON Note.ID = Association.NoteID
   JOIN Lists ON List.ID = Association.ListID
WHERE Association.NoteID = @NoteID

And all the notes for a list:

SELECT [columns] FROM Association
   JOIN Notes ON Note.ID = Association.NoteID
   JOIN Lists ON List.ID = Association.ListID
WHERE Association.ListID = @ListID

That's how you'd represent it in XML:

<Lists>
  <List ID='1'>...</List>
  <List ID='2'>...</List>
  <List ID='3'>...</List>
  ...
<Lists>  
<Notes>
  <Note ID='1'>...</Note>
</Notes>
<Associations>
   <Association ID='1' NoteID='1' ListID='1'/>
   <Association ID='2' NoteID='1' ListID='2'/>
   <Association ID='3' NoteID='1' ListID='3'/>
</Associations>

In, XSLT, you could access this association like this:

<xsl:template match="List" mode="AssociatedNotes">
   <xsl:variable name="Associations" select="/*/Associations/Association[@ListID=current()/@ID]"/>
   <xsl:apply-templates select="/*/Notes[@ID=$Associations/@NoteID]"/>
</xsl:template>

<xsl:template match="Note" mode="AssociatedLists">
   <xsl:variable name="Associations"  select="/*/Associations/Association[@NoteID=current()/@ID]"/>
   <xsl:apply-templates select="/*/Lists[@ID=$Associations/@ListID]"/>
</xsl:template>

(Note the use of the mode attribute to keep these templates from calling each other until you get a stack overflow.)

Robert Rossney 2010-06-18 19:07:37

ansaurus

tags:

views:

answers:

How to represent a Many-To-Many relationship in XML or other simple file format?

related questions