SRD Spells Database 3.5

[spacemonkey]

This is along the lines of what I am moving to with my spells database (in MySQL). Right now I have it set up with many-many relationships for the spell levels and such, but I am thinking that there will not be an easy way for a user to input new types; that's why I like how Java has it set up here (waves to JavaPadawan - its your datamonkey, Sam )

I would definitely separate the component 'cost' field from the 'type/desc' field. Cheap or lazy spellcasters need a way to search for the '0gp' components so they never have to buy or keep track of any

I would also agree that subschools need to be able to be modified; I've had need of this already for a custom setting I'm working on. I would almost venture to say the same of Schools, but I lean toward making things as customizable as I can. To give you an example, what if one were to put in the Sovereign Stone spells along with the SRD spells? SovStone spells are quite different, but with a customizable system such as the one described above, it should be possible. You would need to put new Schools in though, I believe (I don't think that the spell disciplines they have in that system would fit under subschools, but I don't have my book in front of me, so I could be wrong).

Combine this with attributes for what schools a class has access to, and you can easily combine a couple of different magic systems in one DB. (for instance, you add entries for Sor/Wiz to have access to all the standard schools, but an Elemental mage would only have access to the SovStone schools)

Somewhat ambitious perhaps, but does anyone see any problems with that sort of an arrangement?

 

[javapadawan]

Hello Sam!

I would definitely separate the component 'cost' field from the 'type/desc' field. Cheap or lazy spellcasters need a way to search for the '0gp' components so they never have to buy or keep track of any

That sounds like a good idea. If I go with the value field in the spell_has_attribute table as a number and add the aforementioned note field as text, it would be simple to keep the component text and any material component value separate. This would also be useful when calculating the cost to create any scrolls/potions/wands etc. from specific spells with an extra gp/xp component cost.

The only drawback I see is during user entry of new spells. Trying to parse the information from the user entered long_description, or even the components field would probably be troublesome, unless it's in a specific format. Not sure if many users would want to go through the extra trouble of doing that. Adding them as optional data fields on the spell creation user interface would work though, and leaving them blank could signify no extra costs for the spell being entered.

It looks like I'm definitely going to need to redesign the UI for spell entry.

You would need to put new Schools in though, I believe (I don't think that the spell disciplines they have in that system would fit under subschools, but I don't have my book in front of me, so I could be wrong).

Gotcha. I would like the data model I go with to be flexible enough to account for things like the Sovereign Stone CS, so it looks like I'll be leaving School as an attribute_type after all.

Combine this with attributes for what schools a class has access to, and you can easily combine a couple of different magic systems in one DB.

Sounds reasonable. I suppose if you wanted to get crazy you could have an attribute_relationship table which mapped out the relationship between not only classes and schools, but also schools and subschools, subschools and descriptors, etc. That's probably overkill though, depending on what you want to do with that information.

 

[spacemonkey]

Gotcha. I would like the data model I go with to be flexible enough to account for things like the Sovereign Stone CS

Yeah, I would prefer to be as flexible as possible, while keeping the end user interface easy to understand and customize (especially since the setting I'm working on can be described as a 'melting pot' of magical traditions...). Of course that makes the backend stuff a little more difficult to sort out, but that's the way it should be anyway, IMO.

I wouldn't mind collaborating on a design, as we seem to be in synch for the most part anyway. Two heads are better than one, and it would make any dataset transfer easier too!

---

After taking another look at the SS spell system, I don't think it would pose a problem to integrate into the proposed database design. Here is an example:

HAIL
(Weather)
Element: Air/Water
Casting Threshold: 81
Range: Medium (100 ft. + 10 ft./level)
Effect: Hail within 200 ft. cube
Duration: 1 minute
Saving Throw: None
Spell Resistance: No
This spell allows the caster to cause marble-sized...[]


The block is pretty much standard, with a few exceptions. The Element: line lists what element or elements (max 2 as far as I can tell) the spell comes from. With such a limited number of combinations (4 elements that can be single or mixed, plus the void anti-element that can't be mixed) it would be easy to just plot them all out and map them to schools, which seem to be their closest counterpart.

The (Weather) line is an 'aspect', pretty much identical to a subschool (except there are subschools that are derived from 2 schools instead of just one).

Casting Threshold would need to be added as an attribute_type. It is basically a spell point system (though a caster 'generates' points each round based on how well they are performing the casting) with the CT as the DC for the spell.

There are no levels, access to spells is limited only by the caster type (earth mage can cast earth magic, earth mage/air mage can cast earth, air, and earth/air spells) and the CT.

The rest is pretty much standard d20 system spells. I'm not that familiar with the system, but it seems easy to run alongside standard spells if you wanted, which I think i do

Now I've gotta look at my Arcana Unearthed book and see if any tweaks are necessary for that stuff...

 

[Dimwhit]

I'd be interested in taking a look at this! (I couldn't find a link to it.)

You might consider having it hosted at http://3.5srd.com/

It's a great place to consolidate all these SRD projects. (It's not my site, btw.)

 

[spacemonkey]

Well Dimwhit, we are actually discussing a number of projects in this thread. I know mine isn't ready for public consumption at the moment, but you may want to check out javapadawan's magelab (link is in sig), it's pretty polished compared to mine

I took a look at the AU spells, and they should be easy to add, as they use all standard mechanics except for the (Simple, complex, Exotic) line, which could be added as an attribute_type, and the diminished and heightened effects, which can probably just go in the spell description block without problem. I don't know if there would ever be a need to search on those, but it seems unlikely.

I think I'll whip up an extra database later on today and try out the new design to see how it works.

 

[javapadawan]

Maybe it would be better not to try to lump the Elements and Aspects in as Schools and Subschools? They could easily be new attribute_types like Casting Threshold. Then you could group attribute types by campaign setting, perhaps in a table called attribute_group. Attribute_group could look something like this:

attribute_group_id [primary key]
attribute_type_id [foreign key : attribute_type]
attribute_group [text : campaign setting]

Then, your default campaign setting group may contain attribute types school, subschool, descriptor, etc. while your sovereign stone setting group would have elements, aspects, casting thresholds, etc. The only catch is that we're assuming then that spell attributes like range, duration, and spell resistance are universal, since they reside currently in the spell table as text fields. Maybe that's a safe assumption if all we want to support are d20 spells?

Assumptions aside, with a system like this, it would then be pretty easy to support new campaign spell models simply by adding a new attribute group and some new attribute types. Even your spell entry GUI could then be built dynamically by showing data entry fields for only those attributes which apply to the campaign setting the user has chosen to enter spells for.

What do you think?

 

[spacemonkey]

I had been thinking of just doing a separate 'system' attribute, to group everything within its own magic system if need be, but that raised the problem of making a template for each magic system, or having all fields present, even if that magic system didn't posess them for display purposes (a blank CastingThreshold on the regular spells, for example). Or at least putting in a bunch of 'if it has X, then put X in, if not...' statements, which would need to be hard coded in probably.

The attribute group seems a good way to 'template' the spells right in the db, so I'm all for it. With that in mind, would we move the School back into the main spell table, or leave it as an attribute? I'm still in favor of the latter, if only because those spells that don't have a traditional School wouldn't need to have a blank field there (or extra handling to display them without the blank field).

As far as system support goes, I certainly don't think that we need to encompass anything larger than d20 (it's a big enough project as it is ) and I haven't seen anything d20 that doesn't have at least range, duration, etc.. standardized, though it could conceivably happen.

It's really a question of defining the scope of the DB and the project. I'm usually in favor of flexibility for the app, but there comes a point of diminishing returns. I would draw that line here, and if there are some spells that break it in the future, we could add a few special attributes for them if we really thought they should be included... but if they don't have range, duration, etc.. then they are probably different enough to cause other problems anyway.

---

Those issues aside, I had been meaning to add a field to my database, and now seems like a good time to do it, if changes are being made anyway. An Eratta field is needed, I think. I had previously been updating the spells with eratta when I could get to it, and adding a note at the bottom of the description of what had changed, but I find it difficult to search for which spells have been modified already. The eratta text itself needs to be integrated into the longdesc field, so perhaps a date field noting when the last eratta was applied? Null means it hasn't been eratta'd, and you could update the field if there was more than one eratta (as I have seen for a couple of spells). The date would be based off when that particular eratta was released, to avoid confusion.

The only real problem I can see with this is that it may not always be easy to locate the date eratta was published, and could be confusing to determine solely on that if eratta has been applied. You can always look at the spell to see if it has been changed, however.

---

Another field I find handy to have is a 'verified' checkbox/date field (I prefer date, really - just for the extra info). Mistakes can and will be made when entering data, especially if it is scanned/ocr'd. It is a very slow process, but I would like to eventually hand-check every spell to verify that it is exactly the same as the book it was pulled from. Maybe I'll force my players to do this one.. 5xp per spell, get crackin'

 

[javapadawan]

It's really a question of defining the scope of the DB and the project. I'm usually in favor of flexibility for the app, but there comes a point of diminishing returns. I would draw that line here, and if there are some spells that break it in the future, we could add a few special attributes for them if we really thought they should be included... but if they don't have range, duration, etc.. then they are probably different enough to cause other problems anyway.

I agree. And in that case, it does seem as though it would be best to keep School as an attribute.

I think an errata field is a good idea. In addition to a date field though, it might also be helpful to have a source field. This way you'd know that a particular piece of errata came from Sage Advice, from an official WotC errata document, from a reprinting of the spell in a later sourcebook, etc.

It would be easy enough to add these fields to the spell table, though as you mentioned, if a spell was errata'd more than once you'd lose the original record of that... unless errata was stored in another table. (For someone who dislikes adding extra tables, I sure seem to be suggesting a lot of them, don't I? )

What if you had something like this:

spell_has_errata
----------------
spell_has_errata_id (AutoNumber)
spell_id (Number, foreign key : spell)
errata (Memo)
source (Text)
date (Date or Text, text could allow an exact date or a general date, like 5/05/2003 vs. 5/2003)

The drawback of this is that we'd need to do an outer join to pull back both spells that have errata and those that don't, but the upside is that no matter how many times a spell is errata'd, we would have access to not only the original text of the spell but also each subsequent change. Additionally, there may be those who don't like the errata and would rather use a spell as originally written, or might just be curious as to how much has changed over the course of a spell's life.

Overkill? Heh, maybe. But all this could be transparent to the end user. When printing out the spell description for viewing, we could just append the errata to the end of the long description, prefixed by the source and date, looking something like this:

...
This spell does blah blah blah to all creatures within a fifty foot radius.

ERRATA: Sage Advice, May 2003
This spell does blah blah blah to all living creatures within a fifty foot radius.

ERRATA: PHB v3.5 Official Errata, July 15, 2003
This spell does blah blah blah to all living creatures within a sixty foot radius.
...

Another field I find handy to have is a 'verified' checkbox/date field (I prefer date, really - just for the extra info). Mistakes can and will be made when entering data, especially if it is scanned/ocr'd. It is a very slow process, but I would like to eventually hand-check every spell to verify that it is exactly the same as the book it was pulled from. Maybe I'll force my players to do this one.. 5xp per spell, get crackin'

To paraphrase a popular commercial series...

Adding a Verified checkbox to the spell table: Good idea.

Adding a Verified date field to the spell table: Great idea.

Getting your players to do the work for you: Priceless.

 

[spacemonkey]

I agree that a source field would be a good addition to the eratta.

we could just append the errata to the end of the long description, prefixed by the source and date, looking something like this:

...
This spell does blah blah blah to all creatures within a fifty foot radius.

ERRATA: Sage Advice, May 2003
This spell does blah blah blah to all living creatures within a fifty foot radius.

ERRATA: PHB v3.5 Official Errata, July 15, 2003
This spell does blah blah blah to all living creatures within a sixty foot radius.
...

Except that would break some searches and such, like trying to find all Conjuration spells for instance. If a spell had been changed to conjuration, and you only list the eratta at the end of the long description, it wouldn't find that spell. Unless you are saying to change the full spell to the updated/eratta'd version and then append the specific eratta to the end of the longdesc, sort of like a change log? That would work, and could be easily handled by an attribute I would think, with each spell being able to have 0 or more 'eratta' attributes. You could have them print or not at the user's option then as well.

 

[javapadawan]

Except that would break some searches and such, like trying to find all Conjuration spells for instance. If a spell had been changed to conjuration, and you only list the eratta at the end of the long description, it wouldn't find that spell. Unless you are saying to change the full spell to the updated/eratta'd version and then append the specific eratta to the end of the longdesc, sort of like a change log? That would work, and could be easily handled by an attribute I would think, with each spell being able to have 0 or more 'eratta' attributes. You could have them print or not at the user's option then as well.

Oh good point... I hadn't thought about changes in school or that sort of thing. However changing the full spell and just appending the errata as you suggested would take care of that neatly. Allowing the user to toggle the information on or off is icing on the cake.

It's beginning to look like we've got a pretty decent model here... have we missed anything?