#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    Multiple INT's in one row?


    I am trying to figure out the best way to store some data in my database. I plan on joining 3 different tables.

    Code:
    users table
    fighters table
    moveset table
    In the fighters table I have a row for 'moveset' in which I am trying to figure out a way that move_id's from the moveset table can then be associated within the fighters table under the moveset row so each fighter can list a bunch of moves that belongs to them.

    Ex.

    fighters table has

    Code:
    fighter_id,
    fighter_name,
    moveset (preferably listing multiple move_id's separated by commas)

    moveset table has

    Code:
    move_id,
    move_name,
    move_damage
    Multiple move_id's need to get put in the moveset row in the fighters table or something equivalent.

    I've thought about compound primary keys but again if a fighter has anywhere from 100 to 200 moves mixed in that there are over 1,000 fighters, the queries and the size of the db alone could get pretty problematic, no?

    Any help on how to set this up properly in mysql?
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,249
    Rep Power
    9400
    Code:
    moveset (preferably listing multiple move_id's separated by commas)
    No. That may seem easier but trust me: it isn't.

    You need a fourth table relating fighters to moves.
    Code:
    fighter_id,
    move_id
    Originally Posted by woj56k
    I've thought about compound primary keys but again if a fighter has anywhere from 100 to 200 moves mixed in that there are over 1,000 fighters, the queries and the size of the db alone could get pretty problematic, no?
    Nope. MySQL can handle that just fine. It's specifically made to handle things like that.

IMN logo majestic logo threadwatch logo seochat tools logo