It's IMarv!
Here's how I've structured my database.
--All the spells:
CREATE TABLE `spell` (
`Name` varchar(50) default NULL,
`Id` int(11) NOT NULL default '0',
`School` varchar(20) default NULL,
`Subschool` varchar(20) default NULL,
`descriptor` varchar(255) default NULL,
`levels` varchar(255) default NULL,
`Components` varchar(255) default NULL,
`CastingTime` varchar(50) default NULL,
`Range` varchar(50) default NULL,
`EffectType` varchar(100) default NULL,
`Effect` varchar(255) default NULL,
`Duration` varchar(100) default NULL,
`SavingThrow` varchar(100) default NULL,
`Resistance` varchar(50) default NULL,
`ShortDescription` varchar(255) default NULL,
`LongDescription` text,
`Sources` varchar(255) default NULL,
`Materials` varchar(255) default NULL,
`Focus` varchar(255) default NULL,
`Somatic_Comp` tinyint(4) default NULL,
`Verbal_Comp` tinyint(4) default NULL,
`Material_Comp` tinyint(4) default NULL,
`Focus_Comp` tinyint(4) default NULL,
`DivineFocus_Comp` tinyint(4) default NULL,
`XP_Comp` tinyint(4) default NULL,
`XP_Cost` varchar(255) default NULL,
`Expensive_Focus` tinyint(4) default '0',
`Expensive_Material` tinyint(4) default '0',
`Recharge` varchar(50) default NULL,
`Corrupt_Flag` tinyint(4) default NULL,
`Demon_Flag` tinyint(4) default NULL,
`Devil_Flag` tinyint(4) default NULL,
`Disease_Flag` tinyint(4) default NULL,
`Drug_Flag` tinyint(4) default NULL,
`Fiend_Flag` tinyint(4) default NULL,
`Location_Flag` tinyint(4) default NULL,
`Soul_Flag` tinyint(4) default NULL,
`Undead_Flag` tinyint(4) default NULL,
`Abstinence_Flag` tinyint(4) default NULL,
`Archon_Flag` tinyint(4) default NULL,
`Celestial_Flag` tinyint(4) default NULL,
`Eladrin_Flag` tinyint(4) default NULL,
`Guardinal_Flag` tinyint(4) default NULL,
`Sacrifice_Flag` tinyint(4) default NULL,
`MediumDescription` varchar(255) default NULL,
`Added` datetime default NULL,
`Breath_Flag` tinyint(4) default NULL,
`Grayhawk_Cat` varchar(50) default NULL,
`last_upd_dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`Coldfire_flag` int(1) default NULL,
`Frostfell_flag` int(1) default '0',
`sortname` varchar(50) default NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `UK_Name` (`Name`),
KEY `RPGASpell` (`Grayhawk_Cat`),
KEY `SourceBookSpell` (`Sources`),
KEY `spell_sortname` (`sortname`)
)</pre>
--The sources field in the spell table is refers to the version being used.
--If I did it again, I would make Frostfell_Flag just be a type of
Location_Flag, and Coldfire_Flag is just a special expensive component.
--The complete list of sources:
<pre>CREATE TABLE `sourcebook` (
`SourceName` varchar(50) NOT NULL default '',
`Open_Flag` tinyint(4) default NULL,
`Permission_Flag` tinyint(4) default NULL,
`CopyDescription` text,
`LicenseLine` text,
`LongSourceName` varchar(50) default NULL,
`SourcePref` int(11) default '0',
`Release_Dt` date default NULL,
PRIMARY KEY (`SourceName`)
)
--The list of sources for a spell:
CREATE TABLE `spell_source` (
`ID` int(11) NOT NULL default '0',
`SourceName` varchar(50) NOT NULL default '',
`Page_No` int(11) default NULL,
PRIMARY KEY (`ID`,`SourceName`)
)
--A table for the types of components a spell may have:
CREATE TABLE `component` (
`Component_Abbr` varchar(10) NOT NULL default '',
`Component` varchar(50) default NULL,
`MyOrder` int(11) default '0',
`Source` varchar(50) default NULL,
`GroupOrder` int(11) default '0',
PRIMARY KEY (`Component_Abbr`)
)
--The tweener table for spells and components.
CREATE TABLE `spell_component` (
`Id` int(11) default NULL,
`Comp_Abbr` varchar(255) default NULL,
KEY `ComponentSpell_Component` (`Comp_Abbr`),
KEY `SpellSpell_Component` (`Id`)
)
--I use the flags in the spell table when I'm looking for spells "that have all of these" while I use this table for those that I'm looking for "has any of these".
--Spell level families (Sor/Wiz, Cleric, Domains, etc, no levels.)
--This is used to search for a class's spell list.
CREATE TABLE `level_family` (
`SingleLevel` varchar(255) default NULL,
`Family_Name` varchar(50) default NULL,
`LF_ID` int(11) NOT NULL auto_increment,
PRIMARY KEY (`LF_ID`),
KEY `FamilyLevel_Family` (`Family_Name`),
KEY `ID` (`LF_ID`),
KEY `LevelsLevel_Family` (`SingleLevel`)
)
--The actual levels themselves, not placed on any spells yet.
CREATE TABLE `levels` (
`SingleLevel` varchar(255) NOT NULL default '',
`LevelValue` int(11) default '0',
`LongLevel` varchar(50) default NULL,
PRIMARY KEY (`SingleLevel`)
)
--Which spells are for which level:
CREATE TABLE `spell_level` (
`SingleLevel` varchar(255) NOT NULL default '',
`Id` int(11) NOT NULL default '0',
PRIMARY KEY (`SingleLevel`,`Id`),
KEY `LevelsSpell_Level` (`SingleLevel`),
KEY `SpellSpell_Level` (`Id`)
)
--I never broke the descriptors into a proper many-to-many relationship, but I probably should. Extensive use of like queries gets around it though.
--There is a descriptors values list, but I just distinct select the schools and subschools.
I am looking for help to continue maintaining the database, if you wish to help, e-mail me.
It is very easy to add spells, you just have to OCR the spell, spell check it, then copy and paste the "spell splat" into a form that will then pull out the various parts of a spell and you can review it before you commit it to the database. If the spell already exists, it will bring up the existing one.
FYI, I have the full spell descriptions for all the spells in 47 sources. About 5 of those are not WOTC things, but mine or friends of mine. (And the spells on Sean K Reynold's site.) Also, I have one of Monte's PDF books in there.
http://www.imarvintpa.com/dndlive/index.asp
IMarv
I'll post any concerns I see here.
