There's too much simularities between weapons, armors and items to not put part of their stats in a single table. Specific details of a type could be found in another table. I'd do something like this: 
    table ITEM_TYPE (
        ITEM_TYPE_ID   numeric  --  Primary key.
        ITEM_TYPE_DESC char     --  Description (weapon, armour, ...)
    )
        
    table ITEM (
        ITEM_ID        numeric  --  Primary key.
        ITEM_TYPE_ID   numeric  --  Foreign key into ITEM_TYPE
        DESC           char     --  Description.
        -- More common attributes like weight, value, etc.
    )
        
    table WEAPON_DETAILS (
        ITEM_ID        numeric  --  Primary/Foreign key.
        WEAPON_CLASS   numeric  --  Strength of weapon
        -- More weapon attributes.
    )
        
    table ARMOUR_DETAILS (
        ITEM_ID        numeric  --  Primary/Foreign key.
        ARMOUR_CLASS   numeric  --  Strength of armour
        -- More armour attributes.
    )
     
    table STORE (
        STORE_ID       numeric  --  Primary key
        -- Store attributes.
    )
      
    table STORE_INVENTORY (
        STORE_ID       numeric  --  Primary/foreign key *not unique* 
        ITEM_ID        numeric  --  Foreign key into ITEM.
    );

[download]

Of course, were I to really sit down and take the time to create such a game, I'd probably come up with a totally different idea of how to make the database. ;-)

Abigail

In reply to Re: Efficiency MySQL question by Abigail-II
in thread [untitled node, ID 308077] by Samn

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.