[
Lists Home |
Date Index |
Thread Index
]
Title: Message
I'd appreciate help
on mapping a SQL relational database with a many-to-many relationship to a
XML schema in the .NET environment.
Scenario:
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 website page.
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 they are.
Thanks,
Pam
Pam Ammond
|