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.
