reconstructing an XML tree from RDB data

[lwwhite]

I'm not sure if this discussion is a better fit for the Access or XML forum and I don't want to double-post, so I'm starting in Access because you've been so helpful to me here.

I am preparing a presentation comparing using a relational database vs. a native XML database within a content management system to manage XML-based documentation. The point I am trying to make is how difficult it can be to reconstruct the XML hierarchy/tree once the data has been burst into an RDB structure. I am using a simple family tree as my example and I have built a sample database (in Access) with one table that records each family member's name and other statistics (age, etc.) and a second table that joins family members and relationships:

family
------
f_id
name
age

rships
------
r_id
f_id
f_id2
rship

(Where you choose two family members and the relationship between them. This is the most efficient way I could think of to represent this recursive data.)

Now, if I were to try to recreate the family tree in a XML-type tree view, how would I go about that? I don't want any specific code, just a general approach that a typical RDB might take to reconstruct the tree, if it is possible to explain that in any meaningful way without getting technical and specific. I know that it would be schema-dependent, complicated, and require maintenance should the family tree structure change, etc. That's exactly the point I'm trying to make.

And I should point out that I'm still feelin' the love for Access and RDBs in general...just trying to make the case that it's not necessarily the best tool for XML. I don't want to use this post to kick off a debate about NXD vs RDB. I would just like a general explanation of the XML reconstruction process, please.

Thanks in advance.

[MMcCarthy]

I'm not sure if this discussion is a better fit for the Access or XML forum and I don't want to double-post, so I'm starting in Access because you've been so helpful to me here.

I am preparing a presentation comparing using a relational database vs. a native XML database within a content management system to manage XML-based documentation. The point I am trying to make is how difficult it can be to reconstruct the XML hierarchy/tree once the data has been burst into an RDB structure. I am using a simple family tree as my example and I have built a sample database (in Access) with one table that records each family member's name and other statistics (age, etc.) and a second table that joins family members and relationships:

family
------
f_id
name
age

rships
------
r_id
f_id
f_id2
rship

Ok, I don't think you need the r_id here. The two f_id's are the primary key (composite Primary key) as follows:

rships
f_id1 (Compostite Primary key - foreign key referencing primary key of family)
f_id2 (Compostite Primary key - foreign key referencing primary key of family)
rship

This is actually a join table but instead of joining a many to many relationship between two different tables it is joining a many to many relationship with itself.

Now, if I were to try to recreate the family tree in a XML-type tree view, how would I go about that? I don't want any specific code, just a general approach that a typical RDB might take to reconstruct the tree, if it is possible to explain that in any meaningful way without getting technical and specific. I know that it would be schema-dependent, complicated, and require maintenance should the family tree structure change, etc. That's exactly the point I'm trying to make.

And I should point out that I'm still feelin' the love for Access and RDBs in general...just trying to make the case that it's not necessarily the best tool for XML. I don't want to use this post to kick off a debate about NXD vs RDB. I would just like a general explanation of the XML reconstruction process, please.

Thanks in advance.

Building a Tree View from a database point of view is something you would never do. However, a tree view for something like this would be difficult to implement. A tree is where you start with a parent and each parent can have one or more children and each of those children can themselves have one or more children. Your problem arises when you have partners, husbands, wives, etc. This make things extremely complicated.

However, I'm sure there is a way of programmatically representing this. I'm going to ask some of the experts from the other forums to have a look at this.

[pbmods]

I think one problem, conceptually, with the situation is that there are no rules defining relationships. For example, if `f_id` 1 is the son of `f_id` 2, then `f_id` 2 is also `f_id` 1's father. And if `f_id` 3 is `f_id` 2's wife, then `f_id` 1 is also the son of `f_id` 3, even though `f_id` 1 and `f_id` 2 would represent one 'unit' if you were to create a visual representation of the family tree.

I think a more manageable way to structure the database would be to add a `father_id` and a `mother_id` field to the `family` table, since we know each person has exactly one of those (except for `f_id` 0 and `f_id` 1; we'll call them... um, Sadam and Steve).

I could see, if you wanted to create a more btree-like setup, creating a `siblings` view where you would match up `f_id`s with the same `father_id` and `mother_id`.

[MMcCarthy]

I think one problem, conceptually, with the situation is that there are no rules defining relationships. For example, if `f_id` 1 is the son of `f_id` 2, then `f_id` 2 is also `f_id` 1's father. And if `f_id` 3 is `f_id` 2's wife, then `f_id` 1 is also the son of `f_id` 3, even though `f_id` 1 and `f_id` 2 would represent one 'unit' if you were to create a visual representation of the family tree.

I think a more manageable way to structure the database would be to add a `father_id` and a `mother_id` field to the `family` table, since we know each person has exactly one of those (except for `f_id` 0 and `f_id` 1; we'll call them... um, Sadam and Steve).

I could see, if you wanted to create a more btree-like setup, creating a `siblings` view where you would match up `f_id`s with the same `father_id` and `mother_id`.

You also have the issue where siblings can have the same father but different mother.

Essentially database wise this would be one table

tblPerson
PersonID (Primary key)
FatherID (Foreign key referencing the Primary key of this table)
MotherID (Foreign key referencing the Primary key of this table)

Using this you would never actually record a sibling relationship. Instead siblings would be worked out using queries on the data.

[MMcCarthy]

You also have the issue where siblings can have the same father but different mother.

Essentially database wise this would be one table

tblPerson
PersonID (Primary key)
FatherID (Foreign key referencing the Primary key of this table)
MotherID (Foreign key referencing the Primary key of this table)

Using this you would never actually record a sibling relationship. Instead siblings would be worked out using queries on the data.

Looking at this the problem with it from a query point of view is you would have to create a new instance of the table in the query for each parent child relationship.

SELECT P1.PersonID, P2.PersonID As FatherOf, P3.PersonID As MotherOf
FROM ((tblPerson P1 INNER JOIN tblPerson P2
ON P1.PersonID = P2.FatherID)
INNER JOIN tblPerson P3
ON P1.PersonID = P3.MotherID)
 

And this is just a basic one level relationship. After this it would get pretty complicated.

[JosAH]

Relational databases and trees don't like each other. You need at least an
extension to SQL to handle this matter. Have a look how Oracle did it.

kind regards,

Jos

[danp129]

I don't like XML much and don't use it alot so I'm having a hard time picturing how the mother/father (or father/father adoption heh) would look like. Do you have an example XML file for a family tree starting from grandparents on down that your goal is to rebuild from the RDB?

[Purple]

Hi all,

Just to add a MSSQL perspective;

take a look at this which details microsoft's best practice on XML in SQL Server 2005..

Regards Purple

[Motoma]

I'm not sure if this discussion is a better fit for the Access or XML forum and I don't want to double-post, so I'm starting in Access because you've been so helpful to me here.

I am preparing a presentation comparing using a relational database vs. a native XML database within a content management system to manage XML-based documentation. The point I am trying to make is how difficult it can be to reconstruct the XML hierarchy/tree once the data has been burst into an RDB structure. I am using a simple family tree as my example and I have built a sample database (in Access) with one table that records each family member's name and other statistics (age, etc.) and a second table that joins family members and relationships:

family
------
f_id
name
age

rships
------
r_id
f_id
f_id2
rship

(Where you choose two family members and the relationship between them. This is the most efficient way I could think of to represent this recursive data.)

Now, if I were to try to recreate the family tree in a XML-type tree view, how would I go about that? I don't want any specific code, just a general approach that a typical RDB might take to reconstruct the tree, if it is possible to explain that in any meaningful way without getting technical and specific. I know that it would be schema-dependent, complicated, and require maintenance should the family tree structure change, etc. That's exactly the point I'm trying to make.

And I should point out that I'm still feelin' the love for Access and RDBs in general...just trying to make the case that it's not necessarily the best tool for XML. I don't want to use this post to kick off a debate about NXD vs RDB. I would just like a general explanation of the XML reconstruction process, please.

Thanks in advance.

Perhaps I have entirely missed the point of your question, but if I were to try and structure an XML document from the dataset you have givien, it would be identical to the way it was represented in the database:

<xml>
  <family>
    <member f_id=1 name="Mom" age=43 />
    <member f_id=2 name="Dad" age=59 />
    <member f_id=3 name="Child" age=24 />
  </family>
  <rships>
    <rship f_id=1 f_id2=2 rtype=1 />
    <rship f_id=1 f_id2=3 rtype=2 />
    <rship f_id=2 f_id2=3 rtype=2 />
  </rships>
</xml>
 

Did I miss something?

[Dököll]

I would just like a general explanation of the XML reconstruction process, please.

Thanks in advance.

Hello, lwwhite!

You might find this interesting, new post. I am not sure exactly how you are attempting to ditinguish XML and Access databases. If you can do a little reading, please get an idea here:

http://www.thescripts.com/forum/thread672308.html

Any questions, stay tuned, we're here to help...

[MMcCarthy]

Hello, lwwhite!

You might find this interesting, new post. I am not sure exactly how you are attempting to ditinguish XML and Access databases. If you can do a little reading, please get an idea here:

http://www.thescripts.com/forum/thread672308.html

Any questions, stay tuned, we're here to help...

Should that not be in the Articles sections ?

[Dököll]

Should that not be in the Articles sections ?

I am trying to be careful:-)

[MMcCarthy]

I am trying to be careful:-)

I think that one is a pure article.

[Dököll]

I think that one is a pure article.

Looks like our Articles portion are off limits for postings, has anything changed?

[Dököll]

Looks like our Articles portion are off limits for postings, has anything changed?

Please disregard, I figured it out. No questions but ideas/projects yes!

Has been moved, thanks:-)

[Dököll]

Another option: http://www.thescripts.com/forum/show...80#post2631280