Help with logic

[Skyotica]

I'm trying to think of the best way to go about doing this:

 

I am going to be adding a challenger to a database and these challengers use weapons. 

I already am able to add challengers but am thinking of how I should set it up to use weapons.  I was thinking of adding a field and just separating each weapon by a comma and using the sql "IN" clause when searching, however; the number of weapons being used is unknown and different.

 

EDIT: Forgot to include that the actual weapon information has a different table than the challenger information.

 

Does anyone know of a function I should research a little or any suggestions?  Thanks!

[ignace]

I assume that like most RPG's you have a screen with slots where you can drag/drop a weapon/shield/helmet/.. which has a number of fixed slots for each player:

 

character (character_id);
weapon (weapon_id)
challenge (attacker_id, defender_id)
character_equiment (character_id, .., slot_prim_weapon_id, slot_sec_weapon_id, ..)

 

character_equipment table is what I call a "config" table, it holds references to different other tables, like:

 

character_id, slot_helmet_id, slot_shield_id, slot_prim_weapon_id, slot_sec_weapon_id, slot_ring_id
1           , 3             , 5             , 15                 , 2                 , 7

 

A join on this table will give you all of the player's equipment. I am not an advanced DB modeler so some other members may have a better way of doing this. An example query would be:

 

SELECT ..
FROM challenge AS c

JOIN character AS p1 ON p1.character_id = c.attacker_id
JOIN character_equipment AS e1 ON p1.character_id = e1.character_id
JOIN weapon AS w1 ON w1.weapon_id = e1.slot_prim_weapon_id

JOIN character AS p2 ON p2.character_id = c.defender_id
JOIN character_equipment AS e2 ON p2.id = e2.character_id
JOIN weapon AS w2 ON w2.weapon_id = e1.slot_prim_weapon_id

 

Which would get your 2 characters and their weapons.

[Pikachu2000]

You'll want another table to store the information. For each weapon a challenger has, there should be a record in the table with the challenger's primary key id as a foreign key, and a field with the weapon's primary key id as a foreign key.

 

challengers          weapons
pk| name              pk| desc, etc.
-------------         -------------------
1 | Dave              1 | Death Maul
2 | Joe               2 | Sabre
3 | Jane              3 | Broadsword
                      4 | Box Cutter
                      5 | Curling Iron

connector_table
user_pk  |  weapon_pk
--------------------------
1            |     1
1            |     3
2            |     1
2            |     2
2            |     4
3            |     5
1            |     2

 

This would show Dave has Death Maul, Broadsword and a Sabre, Joe has a Death Maul, Sabre, and Box Cutter, while poor Jane has but a lowly Curling Iron.