Just wrote some SQL off the top of my head, so far untested.
- May need to update some of the integer references I am using into actual enums for ease of use.
- May need to add some keys for faster access, like the compound keys on the bridge tables.
- Will see if I can design a simple script soon to populate the DB so I can let other people do the boring data entry work.
- Simple XML API forthcoming.
EDIT: Additional notes to self:
- Add is_open_content to be able to hide closed content in case WotC decide to be evil
- Add effect table for the effect of feats and such and a LOT of bridge tables to make it work
- Add spell list (and bridge table) so it can be filtered by spell list and level.
- Add stat to HP is more flexible than add con to hp, update
/EDIT:
CREATE TABLE source (
`source_id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`name` varchar (256),
`type` varchar (256), -- Book, Magazine, Web article
`sequence_number` int, -- Magazine number
`edition` varchar(16), -- 3.0, 3.5
`description` varchar(256)
);
CREATE TABLE monster (
`monster_id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`name` varchar(256),
`source_id` int unsigned FOREIGN KEY references source.source_id,
`page_number` int,
`size` tinyint DEFAULT 0, -- 0 for medium, 1 for large, -1 for small, etc.
`space` smallint, -- in feet
`reach` smallint DEFAULT 0, -- in feet
`d4` float DEFAULT 0, -- HD
`d6` float DEFAULT 0, -- HD
`d8` float DEFAULT 0, -- HD
`d10` float DEFAULT 0, -- HD
`d12` float DEFAULT 0, -- HD
`extra_hp` smallint DEFAULT 0,
`add_con_to_hp` bool DEFAULT 1, -- Needed for undead and such
`str` smallint,
`dex` smallint,
`con` smallint,
`wis` smallint,
`int` smallint,
`cha` smallint,
`speed` smallint, -- in feet
`swim_speed` smallint, -- in feet
`fly_speed` smallint, -- in feet
`fly_maneuverability tinyint, -- 0 is Average, +1 good, -1 Poor etc
`burrow_speed` smallint, -- in feet
`speed_reduced_by_armor` bool DEFAULT 0,
`initiative` smallint,
`fort` smallint,
`ref` smallint,
`will` smallint,
`grapple` smallint,
`AC` smallint,
`touch_AC` smallint,
`flat_footed_AC` smallint,
`environment` varchar(256),
`organization` varchar(256),
`challenge_rating` float,
`advancement` varchar(256),
`LA` varchar(256),
`description` varchar(65,535)
);
CREATE TABLE attack(
`attack_id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`attack_name` varchar(64), -- Claw
`attack_stat` tinyint, -- 1: Str, 2: Dex, 3: Con etc.
`damage_stat` tinyint, -- 1: Str, 2: Dex, 3: Con etc.
`attack_type` varchar(256), -- Normal/ranged/touch
`attack_bonus` smallint,
`range_increment` smallint,
`attack_note` varchar(256),
`damage_dice_type` smallint,
`damage_dice_number` smallint,
`damage_bonus` smallint,
`damage_type` varchar(256), -- P/B/S/Cold/Half-Fire, Half Divine
`damage_note` varchar(256),
`is_manufactured` bool,
`is_primary` bool,
`is_two_handed` bool, -- Apply 1.5 damage_stat to damage
`additional_effect_hit_dc` smallint,
`additional_effect_hit_stat` tinyint,
`additional_effect_damage_dc` smallint, -- Poison and such
`additional_effect_damage_stat` tinyint -- 1: Str, 2: Dex, 3: Con etc.
);
CREATE TABLE monster_attack_sequence( -- Bridge table to organize lists of attack sequences
`monster_attack_sequence_id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`monster_id` int unsigned FOREIGN KEY references monster.monster_id,
`name` varchar(128),
is_full_attack bool
);
CREATE TABLE attack_sequence( -- Bridge table grouping attacks into attack sequences
`attack_id` int unsigned FOREIGN KEY references attack.attack_id,
`monster_attack_sequence_id` int unsigned FOREIGN KEY references monster_attack_sequence.monster_attack_sequence_id,
`attack_modifier` smallint DEFAULT 0,
`damage_modifier` smallint DEFAULT 0
);
CREATE TABLE feat (
`feat_id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`name` varchar(256),
`description` varchar(16384)
);
CREATE TABLE monster_feat (
`feat_id` int unsigned FOREIGN KEY references feat.feat_id,
`monster_id` int unsigned FOREIGN KEY references monster.monster_id,
`is_bonus` bool DEFAULT 0
)
CREATE TABLE creature_types ( -- List of all types and subtypes
`creature_type_id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`name` varchar(256),
`description` varchar(16384),
`is_subtype` bool DEFAULT 0
)
CREATE TABLE monster_type(
`monster_id` int unsigned FOREIGN KEY references monster.monster_id,
`creature_type_id` int unsigned FOREIGN KEY references creature_types.creature_type_id,
`is_augmented` bool DEFAULT 0
);
CREATE TABLE skill (
`skill_id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`related_stat` tinyint
);
CREATE TABLE monster_skill (
`monster_id` int unsigned FOREIGN KEY references monster.monster_id,
`skill_id` int unsigned FOREIGN KEY references skill.skill_id,
`modifier` smallint
);
CREATE TABLE special_quality(
`special_quality_id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`description` varchar(16384),
`type` varchar(16)
);
CREATE TABLE monster_special_quality(
`monster_id` int unsigned FOREIGN KEY references monster.monster_id,
`special_quality_id int unsigned FOREIGN KEY references special_quality.special_quality_id
);
CREATE TABLE special_attack(
`special_attack_id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`description` varchar(16384),
`type` varchar(16),
`save_dc` smallint,
`save_stat` tinyint -- 1: Str, 2: Dex, 3: Con etc.
);
CREATE TABLE monster_special_attack(
`monster_id` int unsigned FOREIGN KEY references monster.monster_id,
`special_special_attack int unsigned FOREIGN KEY references special_attack.special_attack_id
);
