Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015
    Posts
    1

    Red face Unanswered: Best Way To Use XML Dynamically With MySQL

    I am using MySQL for the back end database for my website and I would like to design my database to work like this:

    The table is set up with generic fields and a field labeled TYPE. (NOTE All generic field types will be String, 255 chars just to make things easy).

    The user selects a data entry 'Type' from a dropdown and enters data.

    Based on the Type selected in the dropdown the database should use an XML file that will know what is in each generic field.

    For example:

    User selects 'fruit' from the type and the data entered is for a 'banana'.

    The xml file for fruit might look like this:

    Code:
    <fruit>
    	<colors></colors>
    	<shape></shape>
    	<skintype></skintype>
    	<locations></locations>
    	<seeds></seeds>
    </fruit>
    So in the database I have the TYPE, NAME and maybe 40 generic fields: TYPE, NAME, GENFIELD01, GENFIELD02, GENFIELD03, GENFIELD04, GENFIELD05...

    In the above example the database would be populated with:

    fruit, banana, yellow|green, long|cylindrical, peel, tropical areas, very tiny seeds


    Another selection Type might be 'actors' and the xml will be as follows:

    Code:
    <actors>
    	<movies></movies>
    	<tv></tv>
    	<directed></directed>
    	<datebirth></datebirth>
    	<datedeath></datedeath>
    </actors>
    and the data will popluate (for example) as:

    actors, Bryan Cranston, Godzilla|Argo|Drive, Breaking Bad|Malcolm In The Middle, 3/7/1965, N/A


    My question is this: Can I save my xml files within my database? Is that the best way? What is the best way?

    I actually have been working with MySQL for some time, but I have not been using xml that long. I get the general idea and thought it would work well for this particular database I am creating.

    Thank you very much.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    best way in my books its not to use XML for storage, but then again I wouldn't use this EAV model for data storage within a relational DB. i

    its a very attractive model if:_
    you don't expect there to be too much data,
    you don't expect to need to do that much searching or data manipulaiton,
    you don't give a fig for performance / database server usage.
    your intended consumption of the data is linear (IE you know now precisley how the data will be consuimed in, say a book / published form.
    But frankly its pretty awful if you need to start searching for data, finding common elements and so on.
    far better to design the db properly using realtional dataabse principles, or don't use a relational DB at all

    MySQL does have a set of fucntion for using XML
    https://www.google.co.uk/webhp?sourc...-8#q=mysql+xml
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •