SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Database Design Help!

    Basically I have a bunch of XML files formatted like the following that I want to convert into a MySQL. The problem is I don't really have alot of experience with database design so I'm not sure how to set it up. Also what type of database would be best for this?

    Some other notes. Not every item has every attribute (for example this one doesn't have bonusAgility, bonusSpirit, etc). An item can have up to 3 sockets of colors red, yellow, or blue. An item can have up to 10 spells associated with it (this example has two). And there is about 20,000 of these items to be stored in the database.

    Code XML:
    <?xml version="1.0" encoding="UTF-8"?><?xml-stylesheet type="text/xsl" href="/layout/item-tooltip.xsl"?><page globalSearch="1" lang="en_us" requestUrl="/item-tooltip.xml">
      <itemTooltips>
        <itemTooltip>
          <id>30893</id>
          <name>Sun-touched Chain Leggings</name>
          <icon>inv_pants_mail_17</icon>
          <overallQualityId>4</overallQualityId>
          <bonding>1</bonding>
          <classId>4</classId>
          <equipData>
            <inventoryType>7</inventoryType>
            <subclassName>Mail</subclassName>
          </equipData>
          <damageData/>
          <bonusStamina>39</bonusStamina>
          <bonusIntellect>28</bonusIntellect>
          <armor armorBonus="0">864</armor>
          <socketData>
            <socket color="Yellow"/>
            <socket color="Blue"/>
            <socket color="Blue"/>
            <socketMatchEnchant>+5 Spell Power</socketMatchEnchant>
          </socketData>
          <durability current="0" max="105"/>
          <requiredLevel>70</requiredLevel>
          <spellData>
            <spell>
              <trigger>1</trigger>
              <desc>Increases spell power by 59.</desc>
            </spell>
            <spell>
              <trigger>1</trigger>
              <desc>Restores 16 mana per 5 sec.</desc>
            </spell>
          </spellData>
          <itemSource areaId="3606" areaName="Hyjal Summit" creatureId="17888" creatureName="Kaz'rogal" difficulty="n" dropRate="3" value="sourceType.creatureDrop"/>
        </itemTooltip>
      </itemTooltips>
    </page>


    What I was thinking was an Item's table with basic info (name, id, etc). Then separate ItemStats, ItemSpells, ItemSockets, and ItemSource tables associated to the Item's table by ID. Would this be the best way? Thanks for any help!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by smajin View Post
    What I was thinking was an Item's table with basic info (name, id, etc). Then separate ItemStats, ItemSpells, ItemSockets, and ItemSource tables associated to the Item's table by ID. Would this be the best way?
    it certainly would -- good thinking

    the easiest way would be to load your xml data into one large "holding" or "landing" table, and then use SQL to split the information off into the proper final tables

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply I guess I was on the right track after all.

    Also I was planning on just using PHP to migrate everything into the database but again this could just be my lack of experience with MySQL. Is loading the XML directly into a table and migrating it from there relatively simple?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by smajin View Post
    Is loading the XML directly into a table and migrating it from there relatively simple?
    i dunno, i never did it, but twenty seconds on google reveals that there are a number of ways to do it

    once it's loaded, then yes, splitting it up into the proper tables is easy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I also have a good amount of character information in similar XML files. Currently my site works by parsing these XML files on a per user basis (they enter their user name and php loads their XML file (the file is actually loaded twice per page by different php pages)). Is this a really bad way of doing things? Or is there a certain point where this wouldn't be feasible in terms of performance?

    I decided to migrate the item information so that I could perform quick and effective searches but that isn't really necessary with the character information so I'm just wondering if its worth the trouble to migrate it as well.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    do you have a DTD for this XML file?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  7. #7
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's a DTD I tossed together quickly for the Item XML files. Please pardon any errors/typos but it should give a pretty good idea.

    Also here's some more info on what i'll be doing with this database if it matters. The kind of queries I'll be using are return all an item's information by id, return a list of item names from wildcard search (for autocomplete form), return a list of item ids where bonusStamina > 5 or where allowableClasses includes Priest or where subclassName = cloth, etc..

    Code XML:
    <!ELEMENT itemTooltip (
    	id, name, icon, overallQualityId, bonding, maxCount, classId, equipData, damageData,
    	bonusStrength?, bonusAgility?, bonusStamina?, bonusIntellect?, bonusSpirit?,
    	fireResist?, natureResist?, frostResist?, shadowResist?, arcaneResist?,
    	bonusDefenseSkillRating?, bonusDodgeRating?, bonusParryRating?, bonusBlockRating?,
    	bonusHitRating?, bonusCritRating?, bonusResilienceRating?, bonusHasteRating?,
    	bonusSpellPower?, bonusAttackPower?, bonusManaRegen?, bonusArmorPenetration?,
    	bonusExpertiseRating?, armor?, socketData?, gemProperties?,
    	requiredLevel?, requiredSkill?, requiredAbility?, requiredFaction?,
    	allowableRaces?, allowableClasses?, spellData?, setData?, itemSource, desc? )>
     
    <!ELEMENT name (#PCDATA)>
    <!ELEMENT id (#PCDATA)>
    <!ELEMENT icon (#PCDATA)>
    <!ELEMENT overallQualityId (#PCDATA)>
    <!ELEMENT bonding (#PCDATA)>
    <!ELEMENT maxCount (#PCDATA)>
    <!ELEMENT classId (#PCDATA)>
     
    <!ELEMENT equipData (inventoryType, subclassName?)>
    	<!ELEMENT inventoryType (#PCDATA)>
    	<!ELEMENT subclassName (#PCDATA)>
     
    <!ELEMENT damageData (damage, speed, dps)>
    	<!ELEMENT damage (type, min, max)>
    		<!ELEMENT type (#PCDATA)>
    		<!ELEMENT min (#PCDATA)>
    		<!ELEMENT max (#PCDATA>
    	<!ELEMENT speed (#PCDATA)>
    	<!ELEMENT dps (#PCDATA)>
     
    THIS APPLIES TO ALL BONUS ELEMENTS
    <!ELEMENT bonus... (#PCDATA)>
     
    THIS APPLIES TO ALL RESIST ELEMENTS
    <!ELEMENT...Resist (#PCDATA)>
     
    <!ELEMENT armor (#PCDATA)>
    	<!ATTLIST armor armorBonus CDATA "0">
     
    <!ELEMENT socketData (socket+, socketMatchEnchant)>
    	<!ELEMENT socket>
    		<!ATTLIST socket color CDATA #REQUIRED>
    	<!ELEMENT socketMatchEnchant (#PCDATA)>
     
    <!ELEMENT desc (#PCDATA)>
    <!ELEMENT gemProperties (#PCDATA)>
    <!ELEMENT requiredLevel (#PCDATA)>
    <!ELEMENT requiredSkill>
    	<!ATTLIST requiredSkill 
    		name CDATA #REQUIRED
    		max CDATA #REQUIRED>
    <!ELEMENT requiredAbility (#PCDATA)>
    <!ELEMENT requiredFaction>
    	<!ATTLIST requiredFaction
    		name CDATA #REQUIRED
    		rep CDATA #REQUIRED>
     
    <!ELEMENT allowableRaces (race+)>
    	<!ELEMENT race (#PCDATA)>
    <!ELEMENT allowableClasses (class+)>
    	<!ELEMENT class (#PCDATA)>
     
    <!ELEMENT spellData (spell+)>
    	<!ELEMENT spell (trigger, desc)>
    		<!ELEMENT trigger (#PCDATA)>
    		<!ELEMENT desc (#PCDATA>
     
    <!ELEMENT setData (name, item+, setBonus*)>
    	<!ELEMENT name (#PCDATA)>
    	<!ELEMENT item>
    		<!ATTLIST item name CDATA #REQUIRED>
    	<!ELEMENT setBonus>
    		<!ATTLIST setBonus
    			desc CDATA #REQUIRED
    			threshold CDATA #REQUIRED>
     
    <!ELEMENT itemSource>
    	<!ATTLIST itemSource
    		areaId CDATA #IMPLIED
    		areaName CDATA #IMPLIED
    		creatureId CDATA #IMPLIED
    		creatureName CDATA #IMPLIED
    		difficulty CDATA #IMPLIED
    		dropRate CDATA #IMPLIED
    		value CDATA #REQUIRED>

  8. #8
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I've been searching the web a bit and reading about database normalization and I have to admit I'm a bit confused. First of all I've been reading conflicting info about whether null values are allowed. Certainly for some of the data where the values aren't atomic (say spell or set data) I'll have to have separate tables. However what about the bonus and resist stats? Would it be incorrect to includes these stats in the base items table and just have some of them be null?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no it wouldn't

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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