Secrets, I took a look at your code and have a question. Why did you put the merc_npc_type_id in the merc_types table instead of putting the merc_type_id, merc_template_id, or something else within merc_npc_types to designate what it belongs to?
Because the stats aren't dependent on race, the table I created for holding merc stats had the class, proficiency, tier, and level to designate which record to use. Unless I'm missing something, I'm not sure how to get a specific record within the merc_npc_types table, although I guess id isn't auto_incremented, so I guess you could reuse the id multiple times. But, again, the stats aren't race dependent (I guess the appearance fields may be; although they are actually randomly generated on hire and may not be necessary to have in the table. They aren't level dependent anyway so it may be best to put that stuff in another table to avoid duplication). Plus, having to update the merc_types table with an arbitrary id from another table after the data has been generated, will most likely be tedious, and to me, harder to maintain.
If we add in trevius' suggestion to use the npc tints table (or at least a copy of it for mercs), it may be best to pull out the textures and tints to another table, especially since they will not be changing every level (I don't think armor changed but every 10-20 levels, where weapons changed every 5). I haven't done this in my example, but could easily be done.
Also, some of the fields in npc_types aren't relevant to mercs, and could most likely be removed.
Just for reference, here's how I had it broken down (I could easily remove the id and just use class, proficiency, tier, and level as the primary key - I just prefer one field for the key that is not dependent on any other field or in any way related to the actual data):
Code:
CREATE TABLE merc_stats (
id int(11) NOT NULL auto_increment,
class tinyint(2) unsigned NOT NULL default '1',
proficiency tinyint(3) unsigned NOT NULL default '1',
tier tinyint(3) unsigned NOT NULL default '1',
level tinyint(2) unsigned NOT NULL default '1',
hp int(11) NOT NULL default '1',
mana int(11) NOT NULL default '0',
AC smallint(5) NOT NULL default '1',
ATK mediumint(9) NOT NULL default '1',
STR mediumint(8) unsigned NOT NULL default '75',
STA mediumint(8) unsigned NOT NULL default '75',
DEX mediumint(8) unsigned NOT NULL default '75',
AGI mediumint(8) unsigned NOT NULL default '75',
_INT mediumint(8) unsigned NOT NULL default '80',
WIS mediumint(8) unsigned NOT NULL default '80',
CHA mediumint(8) unsigned NOT NULL default '75',
MR smallint(5) NOT NULL default '15',
CR smallint(5) NOT NULL default '15',
DR smallint(5) NOT NULL default '15',
FR smallint(5) NOT NULL default '15',
PR smallint(5) NOT NULL default '15',
Corrup smallint(5) NOT NULL default '15',
mindmg int(10) unsigned NOT NULL default '1',
maxdmg int(10) unsigned NOT NULL default '1',
attack_count smallint(6) NOT NULL default '-1',
attack_speed float NOT NULL default '0',
specialattks varchar(36) NOT NULL default '',
Accuracy mediumint(9) NOT NULL default '0',
hp_regen_rate int(11) unsigned NOT NULL default '1',
mana_regen_rate int(11) unsigned NOT NULL default '1',
runspeed float NOT NULL default '0',
PRIMARY KEY (id)
)
CREATE TABLE merc_armortextures (
id int(11) NOT NULL auto_increment,
class tinyint(2) unsigned NOT NULL default '1',
proficiency tinyint(3) unsigned NOT NULL default '1',
tier tinyint(3) unsigned NOT NULL default '1',
minlevel tinyint(2) unsigned NOT NULL default '0',
maxlevel tinyint(2) unsigned NOT NULL default '0',
texture tinyint(2) unsigned NOT NULL default '0',
helmtexture tinyint(2) unsigned NOT NULL default '0',
armortint_id int(10) unsigned NOT NULL default '0',
armortint_red tinyint(3) unsigned NOT NULL default '0',
armortint_green tinyint(3) unsigned NOT NULL default '0',
armortint_blue tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (id)
)
CREATE TABLE merc_meleetextures (
id int(11) NOT NULL auto_increment,
class tinyint(2) unsigned NOT NULL default '1',
proficiency tinyint(3) unsigned NOT NULL default '1',
tier tinyint(3) unsigned NOT NULL default '1',
minlevel tinyint(2) unsigned NOT NULL default '0',
maxlevel tinyint(2) unsigned NOT NULL default '0',
d_meele_texture1 int(10) unsigned NOT NULL default '0',
d_meele_texture2 int(10) unsigned NOT NULL default '0',
prim_melee_type tinyint(4) unsigned NOT NULL default '28',
sec_melee_type tinyint(4) unsigned NOT NULL default '28',
prim_melee_delay tinyint(4) unsigned NOT NULL default '30',
sec_melee_delay tinyint(4) unsigned NOT NULL default '30',
PRIMARY KEY (id)
)
Any thoughts, or am I thinking too deeply about avoiding duplicate data within the table? I'm mostly just trying to keep it as easy as possible for admins to manage, as well as us if we need to make tweaks (which we will).