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

Question

I have a list management appliaction that stores its data in a many-to-many relationship database.

I.E. A note can be in any number of lists, and a list can have any number of notes.

I also can export this data to and XML file and import it in another instance of my app for sharing lists between users. However, this is based on a legacy system where the list to note relationship was one-to-many (ideal for XML).

Now a note that is in multiple lists is esentially split into two identical rows in the DB and all relation between them is lost.

Question: How can I represent this many-to-many relationship in a simple, standard file format? (Preferably XML to maintain backwards compatibility)

Answer 1

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.