question on database structure

[NerissaGoldenrose]

This is probably going to be more important with the artisan recipes than the more advanced ones, but... Does your database structure permit including the type of crafting station used for each item? Also, this again may be something of limited use in higher-level recipes, but what about skill used? If I'm looking at raising my tailoring skill, for example, I'd really rather not have to sort through all of the recipes for food, chemicals, etc. to find the recipes I'm looking for.

[Ariadne]

Hi Nerissa

As one of the inputters, I can confirm that one of the fields we do enter is where the recipe is to be made (ie crafting station). Perhaps Ngreth could confirm that this is a searchable field :)

[Ngreth Thergn]

I can be sure to include that in an advanced search :) Skill and device are fields within the database, so theoretically searchable as long as I properly set up the search :) The current quick search, and browsing methods I am working on do not allow that, but the advanced searches when I get to them should :)

[NerissaGoldenrose]

Cool, thanks. :) As much as I'd love to see things "now" I know setting up a database interface and getting all the data entered is a huge process (boy, do I know it...) so I'm not going to complain if it takes a while. I was just kinda wondering if it was something that was being planned... or if not, I figured I'd at least drop the suggestion. :)

[Ashago]

I'm not sure if the database includes these fields, but I thought they might be helpful as well.

Tier (of recipe)

Level (of recipe)

Device (as listed in above request)

Chemistry Table
Engraved Desk
Forge
Sewing Table & Mannequin
Stove & Keg
Woodworking Table
Work Bench


Knowledge

Alchemy
Arcana
Culinary
HeavyArmoring
LightArmoring
Runecraft
Weaponry
Woodworking


Process

Finish
Interim
Refine


Technique

Artificing
Artistry
Chemistry
Fletching
Metalshaping
Metalworking
Scribing
Tailoring
Woodworking


It also seems that the database is currently having an issue with woodworker recipes. It's pointing to level 10 recipes, which are the woodworking knowledge, but the craftsman class.

[Ariadne]

the only item the database doesn't list separately is tier, but that can be derived from the level of the recipe..

[Niami DenMother]

It also seems that the database is currently having an issue with woodworker recipes. It's pointing to level 10 recipes, which are the woodworking knowledge, but the craftsman class.

I'll nudge Ngeth to peek at this later tonight as that's a linking/display issue, not a problem with the data itself. :)

[Ngreth Thergn]

I'm not sure if the database includes these fields, but I thought they might be helpful as well.

Tier (of recipe)

Level (of recipe)

Device (as listed in above request)

Chemistry Table
Engraved Desk
Forge
Sewing Table & Mannequin
Stove & Keg
Woodworking Table
Work Bench


Knowledge

Alchemy
Arcana
Culinary
HeavyArmoring
LightArmoring
Runecraft
Weaponry
Woodworking


Process

Finish
Interim
Refine


Technique

Artificing
Artistry
Chemistry
Fletching
Metalshaping
Metalworking
Scribing
Tailoring
Woodworking


yup, in there. Should be part of an eventual advanced search. I will note the curent structure after I answer your next question.

It also seems that the database is currently having an issue with woodworker recipes. It's pointing to level 10 recipes, which are the woodworking knowledge, but the craftsman class.

I refer you to this thread: http://www.mboards.eqtraders.com/eq2/showthread.php?t=1784 where it was requested I do this :)
it was a request to break it down into smaller chunks.
but in light of the confusion, I slightly changed it. see what you think now :)

and now the curent structure: (note this may change as I need, and eq2 data changes)
----------------------------------------------------
#
# Table structure for table `items`
#

CREATE TABLE items (
item_id int(10) unsigned NOT NULL auto_increment,
crude_id int(10) unsigned default NULL,
shaped_id int(10) unsigned default NULL,
normal_id int(10) unsigned default NULL,
pristine_id int(10) unsigned default NULL,
item_name varchar(125) NOT NULL default '',
flavor_text text,
type enum('resource','refine','interim','finished') NOT NULL default 'resource',
lore tinyint(1) unsigned default NULL,
norent tinyint(1) unsigned default NULL,
notrade tinyint(1) unsigned default NULL,
novalue tinyint(1) unsigned default NULL,
attunable tinyint(1) unsigned default NULL,
strength smallint(6) default NULL,
agility smallint(6) default NULL,
stamina smallint(6) default NULL,
intelligence smallint(6) default NULL,
wisdom smallint(6) default NULL,
health smallint(6) default NULL,
power smallint(6) default NULL,
cold smallint(6) default NULL,
disease smallint(6) default NULL,
divine smallint(6) default NULL,
heat smallint(6) default NULL,
magic smallint(6) default NULL,
mental smallint(6) default NULL,
poison smallint(6) default NULL,
cast_power smallint(5) default NULL,
cast_concentration tinyint(2) default NULL,
cast_cast float default NULL,
cast_recover float default NULL,
cast_recast float default NULL,
ac smallint(6) default NULL,
wield_style enum('One-Handed','Dual Wield','Ranged','Two-Handed') default NULL,
damage varchar(50) default NULL,
delay float default NULL,
range smallint(5) default NULL,
skill enum('Archegenesis','Axe','Bow','Buckler','Craftsm anship','Crushing','Dagger','Enchantments','Evocat ions','Great Axe','Great Hammer','Great Sword','Hammer','Heavy Armor','Inspirations','Kite Shield','Legerdemain','Light Armor','Mace','Magic Affinity','Martial Arts','Medium Armor','Melodies','Piercing','Poisoning','Rapier', 'Reconnaissance','Round Shield','Skin','Sorcery','Spear','Staff','Stalking ','Sword','Symbols','Talismans','Theurgy','Thrown Weapon','Tower Shield','Vanguard Armor','Very Light Armor') default NULL,
skill_required smallint(6) NOT NULL default '0',
skill_recomended smallint(6) NOT NULL default '0',
slot set('Activatable','Primary','Secondary','Ranged',' Ammo','Head','Chest','Shoulders','Forearms','Hands ','Legs','Feet','Neck','Ears','Left Ring','Right Ring','Left Wrist','Right Wrist','Waist') default NULL,
slots smallint(3) NOT NULL default '0',
weight_reduction smallint(3) NOT NULL default '0',
weight float NOT NULL default '0',
source_type set('Mob Drop','Harvest','Vendor','Quest','Trade Skill') NOT NULL default '',
purchase_price int(11) default '0',
handmade_price int(11) default '0',
sellback_price int(11) default '0',
status_price int(11) default NULL,
image_name varchar(255) default NULL,
eq_item_id int(11) default NULL,
notes varchar(255) NOT NULL default '',
isabook tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (item_id),
UNIQUE KEY eq_item_id (eq_item_id),
KEY crude_id (crude_id),
KEY shaped_id (shaped_id),
KEY normal_id (normal_id),
KEY pristine_id (pristine_id),
FULLTEXT KEY item_name (item_name),
FULLTEXT KEY notes (notes)
) TYPE=MyISAM COMMENT='Items Table';

# --------------------------------------------------------

#
# Table structure for table `recipes`
#

CREATE TABLE recipes (
recipe_id int(11) unsigned NOT NULL auto_increment,
recipe_name varchar(75) NOT NULL default '',
flavor_text varchar(255) NOT NULL default '',
level smallint(3) NOT NULL default '0',
book_id int(11) NOT NULL default '0',
primary_component varchar(50) NOT NULL default '',
pc_num smallint(3) NOT NULL default '0',
build1 varchar(50) NOT NULL default '',
b1_num smallint(3) NOT NULL default '0',
build2 varchar(50) default NULL,
b2_num smallint(3) default NULL,
build3 varchar(50) default NULL,
b3_num smallint(3) default NULL,
build4 varchar(50) default NULL,
b4_num smallint(3) default NULL,
fuel_id int(11) unsigned NOT NULL default '0',
fuel_num smallint(3) NOT NULL default '0',
class enum('Artisan','Craftsman','Outfitter','Scholar',' Armorer','Tailor','Weaponsmith','Carpenter','Provi sioner','Woodworker','Alchemist','Jeweler','Sage') NOT NULL default 'Artisan',
technique enum('Artificing','Artistry','Chemistry','Fletchin g','Metal Shaping','Metalworking','Tailoring','Scribing','Sc ulpting') NOT NULL default 'Artificing',
tool enum('XXX','Chemistry Table','Forge','Sewing Table & Mannequin','Work Bench','Woodworking Table','Stove & Keg','Engraving Desk') NOT NULL default 'XXX',
knowledge enum('Alchemy','Arcana','Craftsmanship','Culinary' ,'Heavy Armoring','Light Armoring','Runecraft','Weaponry','Woodworking') NOT NULL default 'Alchemy',
process enum('Refinement','Interim','Final Product') NOT NULL default 'Refinement',
purchase_price int(11) default NULL,
handmade_price int(11) default NULL,
best_price int(11) default NULL,
failure_return int(11) unsigned default NULL,
crude_id int(11) unsigned NOT NULL default '0',
shaped_id int(11) unsigned NOT NULL default '0',
normal_id int(11) unsigned NOT NULL default '0',
pristine_id int(11) unsigned NOT NULL default '0',
crude_num smallint(3) NOT NULL default '0',
shaped_num smallint(3) NOT NULL default '0',
normal_num smallint(3) NOT NULL default '0',
pristine_num smallint(3) NOT NULL default '0',
srcrude_id int(11) default NULL,
srshaped_id int(11) default NULL,
srnormal_id int(11) default NULL,
srpristine_id int(11) default NULL,
srcrude_num smallint(3) default NULL,
srshaped_num smallint(3) default NULL,
srnormal_num smallint(3) default NULL,
srpristine_num smallint(3) default NULL,
notes varchar(255) NOT NULL default '',
PRIMARY KEY (recipe_id),
KEY shaped_IDX (shaped_id),
KEY book_IDX (book_id),
KEY failure_return (failure_return),
KEY crude_id (crude_id),
KEY normal_id (normal_id),
KEY pristine_id (pristine_id),
KEY srcrude_id (srcrude_id),
KEY srshaped_num (srshaped_num),
KEY srnormal_num (srnormal_num),
KEY srpristine_num (srpristine_num),
FULLTEXT KEY recipe_name (recipe_name)
) TYPE=MyISAM COMMENT='recipes for eq2';