Menu
News
All News
Dungeons & Dragons
Level Up: Advanced 5th Edition
Pathfinder
Starfinder
Warhammer
2d20 System
Year Zero Engine
Industry News
Reviews
Dragon Reflections
White Dwarf Reflections
Columns
Weekly Digests
Weekly News Digest
Freebies, Sales & Bundles
RPG Print News
RPG Crowdfunding News
Game Content
ENterplanetary DimENsions
Mythological Figures
Opinion
Worlds of Design
Peregrine's Nest
RPG Evolution
Other Columns
From the Freelancing Frontline
Monster ENcyclopedia
WotC/TSR Alumni Look Back
4 Hours w/RSD (Ryan Dancey)
The Road to 3E (Jonathan Tweet)
Greenwood's Realms (Ed Greenwood)
Drawmij's TSR (Jim Ward)
Community
Forums & Topics
Forum List
Latest Posts
Forum list
*Dungeons & Dragons
Level Up: Advanced 5th Edition
D&D Older Editions, OSR, & D&D Variants
*TTRPGs General
*Pathfinder & Starfinder
EN Publishing
*Geek Talk & Media
Search forums
Chat/Discord
Resources
Wiki
Pages
Latest activity
Media
New media
New comments
Search media
Downloads
Latest reviews
Search resources
EN Publishing
Store
EN5ider
Adventures in ZEITGEIST
Awfully Cheerful Engine
What's OLD is NEW
Judge Dredd & The Worlds Of 2000AD
War of the Burning Sky
Level Up: Advanced 5E
Events & Releases
Upcoming Events
Private Events
Featured Events
Socials!
EN Publishing
Twitter
BlueSky
Facebook
Instagram
EN World
BlueSky
YouTube
Facebook
Twitter
Twitch
Podcast
Features
Top 5 RPGs Compiled Charts 2004-Present
Adventure Game Industry Market Research Summary (RPGs) V1.0
Ryan Dancey: Acquiring TSR
Q&A With Gary Gygax
D&D Rules FAQs
TSR, WotC, & Paizo: A Comparative History
D&D Pronunciation Guide
Million Dollar TTRPG Kickstarters
Tabletop RPG Podcast Hall of Fame
Eric Noah's Unofficial D&D 3rd Edition News
D&D in the Mainstream
D&D & RPG History
About Morrus
Log in
Register
What's new
Search
Search
Search titles only
By:
Forums & Topics
Forum List
Latest Posts
Forum list
*Dungeons & Dragons
Level Up: Advanced 5th Edition
D&D Older Editions, OSR, & D&D Variants
*TTRPGs General
*Pathfinder & Starfinder
EN Publishing
*Geek Talk & Media
Search forums
Chat/Discord
Menu
Log in
Register
Install the app
Install
Upgrade your account to a Community Supporter account and remove most of the site ads.
Community
General Tabletop Discussion
*Geek Talk & Media
SRD Spells Database 3.5
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="IMarvinTPA" data-source="post: 2385246" data-attributes="member: 29382"><p>It's IMarv!</p><p></p><p>Here's how I've structured my database.</p><p></p><p>--All the spells:</p><p></p><p>CREATE TABLE `spell` (</p><p> `Name` varchar(50) default NULL,</p><p> `Id` int(11) NOT NULL default '0',</p><p> `School` varchar(20) default NULL,</p><p> `Subschool` varchar(20) default NULL,</p><p> `descriptor` varchar(255) default NULL,</p><p> `levels` varchar(255) default NULL,</p><p> `Components` varchar(255) default NULL,</p><p> `CastingTime` varchar(50) default NULL,</p><p> `Range` varchar(50) default NULL,</p><p> `EffectType` varchar(100) default NULL,</p><p> `Effect` varchar(255) default NULL,</p><p> `Duration` varchar(100) default NULL,</p><p> `SavingThrow` varchar(100) default NULL,</p><p> `Resistance` varchar(50) default NULL,</p><p> `ShortDescription` varchar(255) default NULL,</p><p> `LongDescription` text,</p><p> `Sources` varchar(255) default NULL,</p><p> `Materials` varchar(255) default NULL,</p><p> `Focus` varchar(255) default NULL,</p><p> `Somatic_Comp` tinyint(4) default NULL,</p><p> `Verbal_Comp` tinyint(4) default NULL,</p><p> `Material_Comp` tinyint(4) default NULL,</p><p> `Focus_Comp` tinyint(4) default NULL,</p><p> `DivineFocus_Comp` tinyint(4) default NULL,</p><p> `XP_Comp` tinyint(4) default NULL,</p><p> `XP_Cost` varchar(255) default NULL,</p><p> `Expensive_Focus` tinyint(4) default '0',</p><p> `Expensive_Material` tinyint(4) default '0',</p><p> `Recharge` varchar(50) default NULL,</p><p> `Corrupt_Flag` tinyint(4) default NULL,</p><p> `Demon_Flag` tinyint(4) default NULL,</p><p> `Devil_Flag` tinyint(4) default NULL,</p><p> `Disease_Flag` tinyint(4) default NULL,</p><p> `Drug_Flag` tinyint(4) default NULL,</p><p> `Fiend_Flag` tinyint(4) default NULL,</p><p> `Location_Flag` tinyint(4) default NULL,</p><p> `Soul_Flag` tinyint(4) default NULL,</p><p> `Undead_Flag` tinyint(4) default NULL,</p><p> `Abstinence_Flag` tinyint(4) default NULL,</p><p> `Archon_Flag` tinyint(4) default NULL,</p><p> `Celestial_Flag` tinyint(4) default NULL,</p><p> `Eladrin_Flag` tinyint(4) default NULL,</p><p> `Guardinal_Flag` tinyint(4) default NULL,</p><p> `Sacrifice_Flag` tinyint(4) default NULL,</p><p> `MediumDescription` varchar(255) default NULL,</p><p> `Added` datetime default NULL,</p><p> `Breath_Flag` tinyint(4) default NULL,</p><p> `Grayhawk_Cat` varchar(50) default NULL,</p><p> `last_upd_dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,</p><p> `Coldfire_flag` int(1) default NULL,</p><p> `Frostfell_flag` int(1) default '0',</p><p> `sortname` varchar(50) default NULL,</p><p> PRIMARY KEY (`Id`),</p><p> UNIQUE KEY `UK_Name` (`Name`),</p><p> KEY `RPGASpell` (`Grayhawk_Cat`),</p><p> KEY `SourceBookSpell` (`Sources`),</p><p> KEY `spell_sortname` (`sortname`)</p><p>)</pre></p><p>--The sources field in the spell table is refers to the version being used.</p><p></p><p>--If I did it again, I would make Frostfell_Flag just be a type of</p><p>Location_Flag, and Coldfire_Flag is just a special expensive component.</p><p></p><p></p><p></p><p>--The complete list of sources:</p><p></p><p><pre>CREATE TABLE `sourcebook` (</p><p> `SourceName` varchar(50) NOT NULL default '',</p><p> `Open_Flag` tinyint(4) default NULL,</p><p> `Permission_Flag` tinyint(4) default NULL,</p><p> `CopyDescription` text,</p><p> `LicenseLine` text,</p><p> `LongSourceName` varchar(50) default NULL,</p><p> `SourcePref` int(11) default '0',</p><p> `Release_Dt` date default NULL,</p><p> PRIMARY KEY (`SourceName`)</p><p>)</p><p></p><p>--The list of sources for a spell:</p><p></p><p>CREATE TABLE `spell_source` (</p><p> `ID` int(11) NOT NULL default '0',</p><p> `SourceName` varchar(50) NOT NULL default '',</p><p> `Page_No` int(11) default NULL,</p><p> PRIMARY KEY (`ID`,`SourceName`)</p><p>)</p><p></p><p>--A table for the types of components a spell may have:</p><p>CREATE TABLE `component` (</p><p> `Component_Abbr` varchar(10) NOT NULL default '',</p><p> `Component` varchar(50) default NULL,</p><p> `MyOrder` int(11) default '0',</p><p> `Source` varchar(50) default NULL,</p><p> `GroupOrder` int(11) default '0',</p><p> PRIMARY KEY (`Component_Abbr`)</p><p>)</p><p></p><p>--The tweener table for spells and components.</p><p>CREATE TABLE `spell_component` (</p><p> `Id` int(11) default NULL,</p><p> `Comp_Abbr` varchar(255) default NULL,</p><p> KEY `ComponentSpell_Component` (`Comp_Abbr`),</p><p> KEY `SpellSpell_Component` (`Id`)</p><p>)</p><p></p><p>--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".</p><p></p><p>--Spell level families (Sor/Wiz, Cleric, Domains, etc, no levels.)</p><p>--This is used to search for a class's spell list.</p><p>CREATE TABLE `level_family` (</p><p> `SingleLevel` varchar(255) default NULL,</p><p> `Family_Name` varchar(50) default NULL,</p><p> `LF_ID` int(11) NOT NULL auto_increment,</p><p> PRIMARY KEY (`LF_ID`),</p><p> KEY `FamilyLevel_Family` (`Family_Name`),</p><p> KEY `ID` (`LF_ID`),</p><p> KEY `LevelsLevel_Family` (`SingleLevel`)</p><p>)</p><p>--The actual levels themselves, not placed on any spells yet.</p><p>CREATE TABLE `levels` (</p><p> `SingleLevel` varchar(255) NOT NULL default '',</p><p> `LevelValue` int(11) default '0',</p><p> `LongLevel` varchar(50) default NULL,</p><p> PRIMARY KEY (`SingleLevel`)</p><p>)</p><p></p><p>--Which spells are for which level:</p><p>CREATE TABLE `spell_level` (</p><p> `SingleLevel` varchar(255) NOT NULL default '',</p><p> `Id` int(11) NOT NULL default '0',</p><p> PRIMARY KEY (`SingleLevel`,`Id`),</p><p> KEY `LevelsSpell_Level` (`SingleLevel`),</p><p> KEY `SpellSpell_Level` (`Id`)</p><p>)</p><p></p><p>--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.</p><p>--There is a descriptors values list, but I just distinct select the schools and subschools.</p><p></p><p>I am looking for help to continue maintaining the database, if you wish to help, e-mail me.</p><p></p><p>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.</p><p></p><p>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.</p><p></p><p><a href="http://www.imarvintpa.com/dndlive/index.asp" target="_blank">http://www.imarvintpa.com/dndlive/index.asp</a></p><p></p><p>IMarv</p></blockquote><p></p>
[QUOTE="IMarvinTPA, post: 2385246, member: 29382"] 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. [url="http://www.imarvintpa.com/dndlive/index.asp"]http://www.imarvintpa.com/dndlive/index.asp[/url] IMarv [/QUOTE]
Insert quotes…
Verification
Post reply
Community
General Tabletop Discussion
*Geek Talk & Media
SRD Spells Database 3.5
Top