Skyotica Posted July 5, 2010 Share Posted July 5, 2010 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! Quote Link to comment Share on other sites More sharing options...
ignace Posted July 5, 2010 Share Posted July 5, 2010 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. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted July 5, 2010 Share Posted July 5, 2010 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.