Designing "XML Proof" databases?

7 Dec 2004 12:58 PM

Brian Madden

Hello All,

I'm fairly new to the XML game. I am designing a database that will contain
online books. Each book will be broken into parts, each part into chapters,
each chapter into sections, each section into... you get the idea. I think
this is the perfect opportunity for the XML data type in Yukon. However, I
did to put this database together in the next few weeks.

My question is whether there are any best practices for designing
traditional heirarchical databases today that will smoothly transition into
an XML future. (Books, articles, suggestions here, etc.?)

I need to have each text snippet as it's own record in one table (snippetID,
title, body, etc.) I was thinking that I would create a second table for the
heirarchy itself where I would maintain the relationships tied back to the
specific snippetID's. My plan was to use SQLXML to represent the data as XML
(which I think could drive a cool DHTML menu). But, I wonder if this is the
right approach given that yukon is around the corner (yet that I need this
before Yukon's release date?)

Thanks so much,
Brian

7 Dec 2004 5:24 PM

Michael Rys [MSFT]

What do you do with the data once it is stored in the database?

Do you store and retrieve the document as a whole? In that case, I would
keep the document as an NTEXT or TEXT field depending on the encoding of the
XML and propagate some lookup info into other columns. This will be easily
converted into the XML datatype.

If you are planning on doing extensive reshaping and or selecting only
parts, you may want to partition the XML into separate components and then
do the same as above...

Best regards
Michael

Show quote

"Brian Madden" <br***@brianmadden.com> wrote in message
news:uF2NsxF3EHA.1396@tk2msftngp13.phx.gbl...
> Hello All,
>
> I'm fairly new to the XML game. I am designing a database that will
> contain online books. Each book will be broken into parts, each part into
> chapters, each chapter into sections, each section into... you get the
> idea. I think this is the perfect opportunity for the XML data type in
> Yukon. However, I did to put this database together in the next few weeks.
>
> My question is whether there are any best practices for designing
> traditional heirarchical databases today that will smoothly transition
> into an XML future. (Books, articles, suggestions here, etc.?)
>
> I need to have each text snippet as it's own record in one table
> (snippetID, title, body, etc.) I was thinking that I would create a second
> table for the heirarchy itself where I would maintain the relationships
> tied back to the specific snippetID's. My plan was to use SQLXML to
> represent the data as XML (which I think could drive a cool DHTML menu).
> But, I wonder if this is the right approach given that yukon is around the
> corner (yet that I need this before Yukon's release date?)
>
> Thanks so much,
> Brian
>