How to represent a Many-To-Many relationship in XML or other simple file format?
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 开发者_JAVA技巧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)
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.
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.)
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?
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.
Just do it simply like this:
<notes>
<note id="1">
<name>George</name>
<text>1984</text>
<notesLinks>
<listId id="1"/>
</notesLinks>
</note>
<note id="2">
<name>George</name>
<text>animal farm</text>
<notesLinks></notesLinks>
</note>
</notes>
<lists>
<list id="1">
<notesLinks>
<noteId id="1"/>
<noteId id="2"/>
</notesLinks>
<name>some info</name>
</list>
</lists>
加载中,请稍侯......
精彩评论