Question: How should XML data be stored in the database.

XMLHow should data be stored in the database.

 

Example,For example, a forum post, itsXMLThe document structure is as follows:

<? XML version = "1.0" eocoding = "gb2312"?>

 

<Post>

<Question post>

<PostId> 000001 </PostId>

<AuthorId>Xx</AuthorId>

posting time XX time posting time >

post topic > XX Problems post topic >

<Post content>XX content</Post content>

</Question post>

<Reply to post>

<Question postId> 000001 </Question postId>

<PostId> 0001 </PostId>

<AuthorId>Xx</AuthorId>

posting time XX time posting time >

<Post content>XX content</Post content>

</Reply to post>

<Reply to post>

<Question postId> 000001 </Question postId>

<PostId> 0002 </PostId>

<AuthorId>Xx</AuthorId>

posting time XX time posting time >

<Post content>XX content</Post content>

</Reply to post>

...

</Post>

 

So I have two design schemes (I think the second one is more feasible)

 

Solution 1:

Set the entireXMLA document is stored in a data table as a record.

The data table structure:Fileid(Document ID ),File(Document Content), Createdate(Creation Time)

Advantages of this solution: Simple Structure

Disadvantage: you cannot perform complex queries, such as searching all posts of an author.

 

Solution 2:

design two data tables, one for question posts and one for reply posts, use a foreignkey contact two tables, the two tables do not store all XML content, instead, XML each leaf node is designed as a field

TopicTable Data Structure:Topicid(Primary Key ),Userid,Createdate,Issue,Content

Reply Table Data Structure: Replyid (Primary Key ), Topicid (Foreign key, Topic Of Topicid Form a many-to-one relationship ), Userid , Createdate , Content

Advantages of this solution: Clear Data Classification for complex search

Disadvantage: Complicated Structure

 

At first glance, I think the second design scheme is more reasonable, but I immediately came up with a question:XMLThe file structure is relatively simple. It is easy to design leaf nodes into two-dimensional data tables. However, in reality, more complex data structures and attributes may appear, as shown below:

<A>

 <B>

<C att01 = "..." Att02 = "…">

<D>... </D>

</C>

...

 </B>

 ....

</A>

 

When such a relatively complex or even more complex structure occurs, it seems that the first solution is more common. However, the second solution is basically impossible (unless multiple data tables are created to form a complex association), data needs to be re-exported from multiple data tablesXMLIt must be quite complicated ,.

 

I just got started XML , learn more about XML advantages, but consider the specific XML with database applications, there is really no design experience. Please give me some advice.