SRD Spells Database 3.5 Revisited

javapadawan

First Post
A few months ago, in a previous discussion (found here: http://www.enworld.org/forums/showthread.php?t=63377) myself and a few others got off on a data modeling tangent near the end of the thread. Since that time I've been working on and off (off, mostly :)) on importing the SRD spells into an Access database in the format we discussed.

Now that it's finally done, I thought I'd share. The database can be downloaded here if you're interested: http://www.javapadawan.com/files/spells3.5.zip.

The zip file contains an MS Access 2000 database with all the spells from the 3.5 SRD in a normalized format, which is described and discussed in the aforementioned thread. Feel free to use the data for your own nefarious purposes and programs, but keep in mind that as of yet I've only done a cursory glance at the final data; there may very well be errors here and there... and if you find any, please let me know!
 

log in or register to remove this ad

Sir Whiskers

First Post
javapadawan said:
A few months ago, in a previous discussion (found here: http://www.enworld.org/forums/showthread.php?t=63377) myself and a few others got off on a data modeling tangent near the end of the thread. Since that time I've been working on and off (off, mostly :)) on importing the SRD spells into an Access database in the format we discussed.

Now that it's finally done, I thought I'd share. The database can be downloaded here if you're interested: http://www.javapadawan.com/files/spells3.5.zip.

The zip file contains an MS Access 2000 database with all the spells from the 3.5 SRD in a normalized format, which is described and discussed in the aforementioned thread. Feel free to use the data for your own nefarious purposes and programs, but keep in mind that as of yet I've only done a cursory glance at the final data; there may very well be errors here and there... and if you find any, please let me know!

Thanks for posting this. I've only had time to take a quick look, but I noticed a couple things:

1. The first thing I look at when figuring out someone else's db is the relationships screen - it's a quick way to see how everything fits together. I was rather surprised to see that it was blank. Did you create the relationships at the form level? Do you mind posting a short summary of the relationships between the various tables? (Something easy, like "tblAttribute.attributeID [one to many] tblSpell.attribute" - and yes, I made up these table/field names for example purposes)

2. The spell attributes form allows a user to enter/modify attributes for spells, but not new spells. Is there no spell form because you imported the data? I'm just guessing that you used this form to break out the attribute info for each imported spell, but otherwise I can't think what purpose it serves.

I don't know when I'll have time, but if I (or someone else) creates forms and reports for the db, do you want copies? Alternately, we can post mods to this thread, though I'd recommend we each change the db filename slightly - I don't want to over-write my db with someone else's, when I just plan to import an object or two.

Again, many thanks for all the hard work!
 
Last edited:

javapadawan

First Post
Hi Sir Whiskers,

Thanks for pointing out the missing relationship set up... I should have put that in to make it easier for others looking at the db to see how it all fits together. Since I only use the database to store data, and manipulate it mainly through a Java interface, I tend to forget stuff like that. *smacks forehead*

Tomorrow afternoon I'll post a brief summary here of how it all fits together, and upload another copy of the db with the relationships set up (I'd do it now, but I'm home, and my db and all my notes are at work). I didn't post it previously cause it's a lot of information, and I wasn't sure if anyone would actually be interested in reading it. :)

I did import all the spells from a plain text file of the SRD, which is indeed why I have no form set up for entering new spells. I set up the attribute form for a couple reasons, though. The first was just to make it easier to verify that the information was parsed correctly; I find it much easier to match up the text descriptions for the attributes and types than the IDs. :) In addition, I plan to add more information for some of the attributes, like prices for costly components, and page numbers for the source attribute, and since I don't have a Java UI set up for that yet, the form was just the quickest way to go.

If you or anyone else wants to add more forms and reports, that would be great! I'd love to see what people who actually know how to use Access can do (as opposed to myself; I'm a very basic user. I can create tables, simple forms... and, uh... did I mention I can create tables? :D)

Anyway, you're quite welcome, and I'll post the updated db and more details tomorrow.
 

javapadawan

First Post
I've uploaded an updated version of the database with the relationship information in place:
http://www.javapadawan.com/files/spells3.5.zip

In addition, I've recapped most of the pertinent information on the database structure below, along with examples and reasons why I built it this way for any who might be interested. It's rather long-winded I'm afraid, so if you have no interest in data modeling discussions, you're probably better off just skipping it. :)

Firstly, I guess it makes sense to point out that I built this database specifically to be the back end of the next version of my MageLab program, so many of the decisions I made are based on the fact that eventually I will have an extensive Java UI in front of all this, hopefully making any complexity transparent to the end user.

spell
-----
spell_id (Number, primary key)
spell (Text)
casting_time (Text)
spell_range (Text)
effect_type (Text)
effect (Text)
duration (Text)
saving_throw (Text)
spell_resistance (Text)
short_desc (Memo)
long_desc (Memo)

The missing fields, like school, subschool, descriptor, etc., have been implemented as attributes, and I'll get into those and explain more below. I also separated effect_type from effect, hoping to make searching for spells which have a Target as opposed to Area effect spells easier. The rest of the fields are pretty self-explanatory, with short_desc being the brief, one line spell description, and long_desc being the full description.

attribute_type
--------------
attribute_type_id (Number, primary key)
attribute_type (Text)

The attribute_type table holds the descriptions for the attributes left out of the spell table above. The database currently contains the attribute types School, Subschool, Descriptor, Class, Domain, Component, and Source. One of the reasons I built it this way was so that users could add new attribute types easily, allowing for support of different d20 campaign settings, or custom homebrew spells.

attribute
---------
attribute_id (Number, primary key)
attribute_type_id (Number, foreign key [attribute_type])
attribute (Text)
abbrev (Text, nullable)
note (Memo, nullable)

The attribute table is where I list all the attributes of a particular attribute_type. For example, spell schools like Conjuration, Abjuration, etc. are represented here with an attribute_type_id matching that of the School attribute_type. The note field is for holding miscellaneous data, like granted powers for the domains. The abbrev field is just what it sounds like it is, so for example, the attribute Sorcerer/Wizard (attribute_type Class) would have Sor/Wiz in its abbrev field. Like with attribute types, these are listed in a separate table so that new attributes (like additional domains, classes, etc.) can be added easily. Attributes can also be grouped, but more on that later.

spell_has_attribute
-------------------
spell_has_attribute_id (AutoNumber, primary key)
attribute_type_id (Number, foreign key [attribute_type])
attribute_id (Number, foreign key [attribute])
spell_id (Number, foreign key [spell])
qualifier (Number, nullable)
note (Text, nullable)

This is the table that ties attributes to a particular spell, and each spell may have none, one, or many of any particular attribute type. The qualifier field can hold different numeric information depending on the attribute. To give an example, the spell Acid Fog would have a record in spell_has_attribute with an attribute_type_id matching that of the Class attribute_type, and an attribute_id which would match that of the Sorcerer/Wizard attribute. In that case, the qualifier would be 6 (the level at which a Sorcerer or Wizard could cast the Acid Fog spell). Likewise, there would also be a record for that spell tying it to the Domain attribute_type and the Water attribute, with a qualifier of 7.

The note field is for holding optional extraneous information, such as the material components required for a spell. To go back to the Acid Fog example, you would have another spell_has_attribute record for Acid Fog with an attribute_type of Component and an attribute of Material. The note field could then contain 'A pinch of dried, powdered peas combined with powdered animal hoof.' Of course, this information is already contained in the long_desc field for this spell in the spell table, which is why this is an optional step... in fact, the database I linked to above does not have the material/focus component entered in these fields yet.

attribute_group
---------------
attribute_group_id (Number, primary key)
attribute_group (Text)

group_has_attribute
-------------------
group_has_attribute_id (AutoNumber, primary key)
attribute_group_id (Number, foreign key [attribute_group])
attribute_type_id (Number, foreign key [attribute_type])
attribute_id (Number, foreign key [attribute])

The attribute_group and group_has_attribute tables are a mechanism for grouping attributes by various criteria, like campaign setting. In the current database, there is one attribute_group, Core Rules v3.5. All the attributes are tied to this group in the group_has_attribute table. If you wanted to add spells from the Forgotten Realms books (FRCS, MoF, etc.) any new attributes (like the additional cleric domains, Elf, Dwarf, etc.) could be added to a new attribute group. Likewise, custom attributes from home campaigns could also have their own attribute group. I put this in place to act as a switch, so that users could turn certain groups of attributes on and off.

spell_has_errata
----------------
spell_has_errata_id (AutoNumber, primary key)
spell_id (Number, foreign key [spell])
errata (Memo)
source (Text)
date (Date)

As it sounds, this table is a way to store multiple pieces of errata per spell without necessarily overwriting the original spell text. It includes fields to store the text of the errata, the source (official WotC document, or Sage Advice, for example) and the data the errata was released. I had planned to include an outer join to this table when displaying spell information in the UI, and simply append any errata text to the end of the long description. This table could also be used to hold house rules specific to certain spells without overwriting the spell as it appears officially.

That about covers it I think. :) Of course questions, comments, and suggestions are welcome.
 

Remove ads

Top