Dataset with many-to-many relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any special way of handling a many-to-many relationship in a
dataset?
I am wondering if I could set up my dataset so I can navigate directly to
the second table in the many-to-many relationship as opposed to the table
joining the two.
 
Thad,

For a lack of a better word in what I'd like to call classic ADO.NET - which
uses simple dataset with relations and maps an RDBMS functionality, I don't
think there is another way of doing a many to many relationship without
having a mapping table in the middle.

So, your many to many relation is between A and B, You'd have an AB, with a
1 to many between AB and A .. and between AB and B.

Then given an A row, you could do GetParentRow, and then GetChildRows from
the row you get from AB.

So much for that ... there is however, within the confines of ADO.NET
another way of doing Many to Many relationships - which indeed doesn't use a
mapping table. You can specify many to many relations in an XSD - viz it'd
look like something as follows -

<xsd:Schema>
<xsd:complexType name="A">
<xsd:sequence>
.......
<xsd:element name="BEntries" type="B"
minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>

<xsd:complexType name="B">
<xsd:sequence>
.......
<xsd:element name="AEntries" type="A"
minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:Schema>

Something like the above :) where "...." denotes the extra columns.

Ok good .. now that you have a good XSD representation for your object, you
could then use either SqlXML, or basically SqlXmlDataAdapter to pass in
"updategrams" into the database. That way, you could get around creating a
mapping table for Many to Many relations. Also to work with the above data,
using a rather simple xslt transform, you could very easily flip from A
based XML to a B based XML (know what i mean? .. well an XML that you can
browse using A rows, versus one that you can browse using B rows).

You can find more details on that in a book I have written (ISBN:1590594347)
through Apress in Chapter #12.

Regards,

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik
 
Back
Top