what persistence layer (xml or mysql) should i use for this xml data?

Question

i wonder how i could store a xml structure in a persistence layer.

cause the relational data looks like:

<entity id="1000070">
    <name>apple</name>
    <entities>
        <entity id="7002870">
            <name>mac</name>
            <entities>
                <entity id="7002907">
                    <name>leopard</name>
                    <entities>
                        <entity id="7024080">
                            <name>safari</name>
                        </entity>
                        <entity id="7024701">
                            <name>finder</name>
                        </entity>
                    </entities>
                </entity>
            </entities>
        </entity>
        <entity id="7024080">
            <name>iphone</name>
            <entities>
                <entity id="7024080">
                    <name>3g</name>
                </entity>
                <entity id="7024701">
                    <name>3gs</name>
                </entity>
            </entities>
        </entity>
        <entity id="7024080">
            <name>ipad</name>
        </entity>
    </entities>
</entity>

as you can see, it has no static structure but a dynamical one. mac got 2 descendant levels while iphone got 1 and ipad got 0.

i wonder how i could store this data the best way? what are my options. cause it seems impossible to store it in a mysql database due to this dynamical structure.

is the only way to store it as a xml file then? is the speed of getting information (xpath/xquery/simplexml) from a xml file worse or greater than from mysql?

what are the pros and cons? do i have other options? is storing information in xml files, suited for a lot of users accessing it at the same time?

would be great with feedbacks!! thanks!

EDIT: now i noticed that i could use something called xml database to store xml data. could someone shed a light on this issue? cause apparently its not as simple as just store data in a xml file?

Answer 1

entity has a 0..1 relation to entities, and entities has a 0..* relation to entity.

This is not the exact SQL, and most likely not valid in any DBMS, but should help you get started:

CREATE TABLE entity (
  id int(10) AUTOINCREMENT NOT NULL,
  xid int(10) NOT NULL,
  name char(30) NOT NULL,
  entities_id int(10) REFERENCES entities.id NULL,
  PRIMARY KEY(id)
)

CREATE TABLE entities (
  id int(10) AUTOINCREMENT NOT NULL,
  entityref_id int(10) REFERENCES entityref.id NOT NULL,
  PRIMARY KEY(id)
)

CREATE TABLE entityref (
  id int(10) NOT NULL,
  entity_id int(10) REFERENCES entity.id NOT NULL,
  PRIMARY KEY(id,entity_id)
)