Lists Home |
Date Index |
From: Empowering You [mailto:firstname.lastname@example.org]
Sent: 10 November 2002 21:56
Subject: [xml-dev] many to many relationship to XML Schema
Q: I'd appreciate help on mapping a SQL relational database with a
many-to-many relationship to a XML schema in the .NET environment.
A: I would suggest you start from the conceptual data model, not from
the SQL implementation model.
This generally leads to a model in which the top-level elements
represent business objects (by which I mean, objects that the users of
your system would recognize). Very often the associative entities will
be represented by duplicated information in more than one business
object. Whether this is a good thing or a bad thing depends on what the
XML is for: is it persistent or transient, is it read-only or
Mapping relational tables directly to XML is (in my view) usually wrong,
unless you are using the XML simply to move data from one relational
database to another. Why constrain yourself to first-normal-form when
you're using a technology that doesn't require it?
In a Microsoft SQL 2000 database:
The Page table holds web pages.
The Product table holds products.
Because many products can be contained on one web page, and a specific
product can occur on more than one web page, I have a
PageProduct table for the 'many' side of the relationship of both Page
and also Product.
Because I want to output web pages that contains a list of products and
it looks like an XML schema, I decided to use XML instead of taking the
data directly from the relational database.
1. I can create the XML schema to look like the relational tables and
include the PageProduct table as a schema element. And maybe I should
do this if I want to allow the user to update the products from the
or 2. I can create the XML schema to only use Page and Products and I
don't think it needs the PageProduct element at all. This way seems
much more intuitive. But it isn't the way the relational tables work. I
am unclear about what would happen if I don't include a PageProduct
element and then want to try doing semi-automatic updates (maybe using
one of the grid controls) in ASP.NET and ADO.NET, probably using the
built-in features in datasets, to the Page and Product tables. I would
think it might not work without a PageProduct table because Page and
Product are not directly related.
Or maybe I should do it both ways: Not use a dataset and not include
PageProduct in the schema if I'm not going to update; and then include
the PageProduct table in the schema and use a dataset if I am going to
do updates. In this case I'd have two schemas, one for read-only and
one for updates.
Basically, all I've found on mapping relationships to schemas is what
Microsoft has on their web site. It has the pieces and parts, and even
details about SQLXML, Managed XML, and extensions. But I'm not getting
the overall picture of how to set up my schema when I want to do
updating back to the database. When I don't want to update I don't
think it matters since I can always do a one to one mapping of an
element to a SQL database field. It's when I want to consider updating
the database that I don't understand the best way to write the schema.
Any suggestions on the best way to do this specific scenario? And also
I need to understand more about mapping relational databases to XML
schemas in .NET, especially when updating back to the database. Also it
would help to see a list of rules about when an element or attribute
becomes a field in a new table, or the name of table, or a field in an
existing table if I'm using default mapping. I've seen the rules
spattered throughout the documentation, but am not sure of exactly what
Please buy the "Wow! Look At Windows XP" book I wrote at