Multiple INT's in one row?

[Devereaux]

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

Code: Select all

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: Select all

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

moveset table has

Code: Select all

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?

[Jackolantern]

Fighters are going to have upwards of 100 moves each?

Well, the way I would probably tackle this is to have a move list table. All moves listed out in singles, one row per one move. Each move will have a move_id. Then you have another table that is called moveset. Inside this table you have each row being an ID'd moveset, and fields for the total possible number of moves. There would be a moveset for every possible combination of moves, so that way each fighter would only need to have one moveset ID and that would cover every possible move combination. It could be many, many movesets, but that isn't really an issue. You could write a program to make all of the moveset combinations for you. Then you can join the fighter with the movelist, and then see if the move is represented in their moveset.

But I definitely have to suggest against having a single-field with multiple comma-separated values in your database. They are essentially unqueryable.

[Devereaux]

Well, the way I would probably tackle this is to have a move list table. All moves listed out in singles, one row per one move. Each move will have a move_id.

That is what I have now with 12 different fields/attributes. (id, name, damage, energy, etc)

Then you have another table that is called moveset. Inside this table you have each row being an ID'd moveset, and fields for the total possible number of moves. There would be a moveset for every possible combination of moves, so that way each fighter would only need to have one moveset ID and that would cover every possible move combination.

This is where I get lost. Movesets seem a bit linear in terms of having a set amount of moves in order assigned to a specific moveset. For example, if a user wants his fighter to ONLY have move23, move105, move276, and move488, and he purchases said moves with in game cash, how would this be put in to a moveset? From what I gather your process seems like it might have to loop?

My biggest question is what would be the positives and negatives for this approach in customization terms over say a "many-to-many" relationship table linking fighter_id to move_id?

[Jackolantern]

Oh, yeah if a fighter could have only 1 move, or they could have 50, that would explode the amount of possible movesets. With a more conservative amount of moves and a fixed number of moves each fighter could have (say they could only have 8 and had to lose one to learn another) my method would likely be the fastest way to handle it. It could still be done, mind you, but there will be so many possibilities that you may end up with 10k - 20k moveset rows (not that MySQL couldn't handle that like a champ, but probably it is not what you are wanting).

How do players use a move? The proper db setup is likely going to have that as a consideration. Will they have a list of all their possible moves and click one?

[Devereaux]

Fighter pulls x amount of moves he has and they are then used randomly throughout a fight. What makes this different is a fight has 5 different sections where certain moves can only be used. Therefore move x can only be used in section 1,2, or 5 in random output.

In each section, the script will look up each fighter and random roll to determine who goes first and see's which moves he "owns" that can be used in that section. From there they would be a random rolled move(only allowed in that section) and output with damage being done if successful, taking a small amount of energy away (whether success of not, just for trying), and repeat for the next sequence.

When someones health reaches 0 the match forwards to the last section to determine the victor.

This is a pretty advanced project I'm taking on. Luckily Xaos from here has been helping me out quite a bit on Skype. Just trying to get a nice efficient DB scheme setup.

[Jackolantern]

How may moves total are there going to be, and how many can each player have?

[Devereaux]

Infinite to both. I will be adding moves as the game goes on and every move I add can be attained in one form or another. I'm launching at 500 moves. If I had to guess a range where moves will be added more sparingly, I'd say around 750.

This is not a normal "fight" game, this is a game based on pro wrestling where everything is based on moves rather than simple strikes and weapons.

[hallsofvallhalla]

I am going to add a seemingly stupid answer here but it might help spawn a way of doing it.

fighter's move column in player table
128,126,130,180,100,50,75

Code: Select all

 var MoveSplit = '<?php echo '$playerinfo3['moves']'; ?>';
Movesplit = MoveSplit.split(",");

for(var i = 0; i < MoveSplit.length; i++)
{
document.getElementById("fighterMoves").innerHTML = document.getElementById("fighterMoves").innerHTML + MoveSplit[i] + "<br>";
}

now i just did a quick php query then convert to js and then throw each ability into the div line by line as to just show a simplistic way to hold several abilities in a small text column. I have used this method to build MASSIVE maps with little queries to the DB. Just a thought.

[Jackolantern]

Another way (and is pretty close to a many-to-many intermediate table) is to have all the moves in their own table, of course. Then have another table that combines the moves to the player with 3 columns: ID, which is just for indexing to speed up queries and won't be used. fighter, which is the ID of the fighter who has the move. And move, which is the ID of the move. Then you just have to query that table for the fighter's ID, and all the fighters moves will be retrieved. This is very cheap to MySQL performance, since it is tuned to work this way. I guess it is basically a many-to-many intermediate table, but there is wisdom in why this is a well-known pattern

[Devereaux]

Another way (and is pretty close to a many-to-many intermediate table) is to have all the moves in their own table, of course. Then have another table that combines the moves to the player with 3 columns: ID, which is just for indexing to speed up queries and won't be used. fighter, which is the ID of the fighter who has the move. And move, which is the ID of the move. Then you just have to query that table for the fighter's ID, and all the fighters moves will be retrieved. This is very cheap to MySQL performance, since it is tuned to work this way. I guess it is basically a many-to-many intermediate table, but there is wisdom in why this is a well-known pattern

Yup, I think that is the process I'm gonna use. I was worried about the amount of data that would be coming but I was assured by many in the MYSQL forum that unless I was dealing with trillions of entries (and if I was just make two m2m tables and join them to decrease load), I will be more than fine. In fact this is where MYSQL thrives. I do very much appreciate the info here.

I'm sure I'll have many more questions as time goes on.