MYSQL multiple INT's in one row

Question

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

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

fighter_id,
fighter_name,
moveset

moveset table has

move_id,
move_name,
move_damage

Multiple move_id's need to get put in the moveset row in the fighters table.

Any help on how to set this up properly in mysql?

Do you have the concept if a "set of moves" that have reasonable reuse? Like a "move profile" that could be used to assign to one or more flights as a single thing? — Bohemian
– Bohemian ♦, Commented Jul 28, 2013 at 0:21

Bohemian · Accepted Answer · 2013-07-28 00:21:33Z

2

You need a "many-to-many" relationship.

To store this, the best way is by creating an association table, which has the key if both tables being related:

fighter_moves
-------------
id
fighter_id
move_id

You can even add other non-key columns, called assoiation data, that describe the relationship. For example, speed to define how fast the fighter does the move.

edited Jul 28, 2013 at 0:21

answered Jul 27, 2013 at 22:47

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Devereaux Over a year ago

So basically create 4 tables. The 3 I have a above for details and 1 to tie them all together by id?

Bohemian Over a year ago

Yes. It's the only (sane) way to represent a many to many, which is what you have. Your design fine.

Devereaux Over a year ago

But if I have over 500 moves and over 1000 fighters, and each fighter has 50-100 moves, that could get problematic, no?

Anthony Accioly Over a year ago

No, it will be ok. Another option, if you don't need to have a fighter_moves id column, is to make fighter_id and move_id a compound primary key of the fighter_moves table (this will act as a unique constraint + index for free).

Devereaux Over a year ago

So then no matter what I would have to associate each move to each fighter over and over rather than have an "array-like" or something similar in MYSQL to link multiple moves to one fighter in a database?

|

Collectives™ on Stack Overflow

MYSQL multiple INT's in one row

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related