Heyas; I'm a guy that is just getting into game development. I have a ton to learn yet, and I have been doing it all on the fly. With that said, let me get to the reason of my post. Currently, I am having trouble in the building of my database, so I thought I'd throw the problem to some of you, in the hopes that someone can make a suggestion that would work. I'm currently working ona sci-fi, turn-based MMO strategy game that deals with the 'mecha' genre. Anyhow, I'm using mysql as the database in which to store all the relevant information for my mechs, but am having trouble. You see, each mech consists of several sections; head, L/R arm, L/R torso, center torso, L/R leg, as well as R/L rear torso, and center rear torso. Each section has an assignment of armor points & internal points. Also, weapons are location specific, so that when a mech loses an arm, the weapons in the arm are also lost. With that foundation established, the problem I am having is getting away from creating duplicate entries, as duplicate entries would quickly make the database grow too quickly. The primary key that I'm using is a code specific to each mech - a 'mech code'. So far, I have the following tables that are relevant building blocks of a mech; weapon table - a list of weapons, their ranges, their damages, etc. mech_armor - armor & internal per section of the mech mech_eq - equipment of the mech, i.e. gyro's, actuators, sensors, ecm, & etc. In the above tables, there weren't any problems in duplicate entries, as each section so far has had one value associated to each section of the mech. The problem begins when I try to assign multiple weapons to each mech section. For example, if i want to add 2 lasers to the left torso. Any way I create this table, I get duplicate entries. As some of you may have guessed, I am just learning MySQL, and I am convinced that there is an easy solution to this problem. Anyone willing to give me a push in the right direction?

Are there any other tables involved here? So far what I see are three tables that hold a lot of stats on mech weaponry, and you want to associate weaponry to a specific mech. Is this correct? Is there a mech table, or something of that sort?

there are other tables, but not associated with the mech. The other tables have to do with user information, empire information, research, and politics.

Ok, the weapon table is a list of weapons in the entire game, because there will be three basic types of units (mechs, vehicles, infantry), with 30-40 models of units per type. Each model and variant has already been planned, so it is just a matter of getting the database structure built so we can start entering in all the data.

The vehicles and mechs share the same weaponry, so I thought a weapons table, listing all the weapons, would be most advantageous.

the actual mech would be build off 3 or 4 tables, ideally, with all data involved assiociated via the 'mech code'. (Not sure how to explain this w/o actually showing you.) So it would look something like this;

table 1: mech_armor
(legend; H=head, LT/RT=left/right torso, CT=center torso, etc, etc.)
SVR-KNT(mech code),10(H),20(LA),20(LT),20(CT),20(RT),20(RA),20(LL),20(RL)

table 2: mech_equipment
SVR-KNT (mech code), sensors(H), actuator(LA), engine(CT), actuator(RA)....

table 3: mech_weapons
SVR-KNT (mech code), large laser (RT), plasma cannon (RT).....


....well, you get the picture. From those three tables, you would compile the complete mech, but if I have two weapons - especially two different weapons - in the same location, I need to figure out how to indicate that without duplication, because if I have to have duplicate entries for each time 2 or more weapons are in the same area of a mech, with approximately 120 various units (give or take), the database would grow quite quickly.

First, the game idea sounds pretty cool. Let us know when you start building your website for it so we can check it out.

Second, I think I understand your problem... but I'm still a little unclear. If I undestand you correctly, you're worrying about this happening:

Table Mech_Body_Parts

<mech_id><mech_body_part><mech_item>
<1><right_arm><laser>
<1><right_arm><ion cannon>
<1><right_arm><sonar>


If thats the case, I really don't see what you're worried about. MySql databases are really really fast (ie, you could probably have 1,000,000 entries, and it could still go through it in under a second), and having a few rows like that associated with each mech body part really shouldn't slow you down that much. But, if you're REALLY worried about it, try this format instead.

<mech_id><mech_body_part><mech_item1><mech_item2><mech_item3>
<1><right_arm><laser><ion cannon><sonar>

The trade off here is that if the user doesn't have multiple items equiped, then you're storing a bunch of un-needed values (ie, lots of 0 or 'empty' values). But, if the user has all his slots full, then you get to save a little bit of query time. Also, if you associated HP with this table, you wouldn't have to worry about redundant values for HP (which can get REALLY messy).

You may consider setting up the databases as follows:

MechOverview
<Mech_ID><Mech_Class><Mech_General_Info>

In this case, Mech_General_Info may consist of several more columns with standard info related to all mech classes. It may include the Mech's Age, the Mech's Owner, which kingdom its a part of, etc.

MechBodyParts
<Mech_ID><Mech_Body_Part><Hit_Points><Other_BodyPart_Info><BodyPartID>

MechEquipment
<BodyPartID><ItemID>

MechItems
<ItemID><Item_Damage><Item_Range><Item_Frequency><etc>

It would be a pain in the butt to code, but if you're REALLY that worried about redundant data, this is probably your best option. Just be sure to make good use of union statments where needed.

Quote:Original post by Beemer
The problem begins when I try to assign multiple weapons to each mech section. For example, if i want to add 2 lasers to the left torso. Any way I create this table, I get duplicate entries.

Simply give weapons a unique "weapon_id" as the primary key. Probably auto incremented. That will solve the duplication issue. The primary key just has to be a value that is unique for each row; it doesn't have to be the criteria by which you select rows. That can be anything you like.