SRD Spells Database 3.5

[Pielorinho]

Oooo, data modeling! Can I play?
For example, Acid Fog would have one record in spell_has_attribute with an attribute_type_id matching that of the Class attribute_type, whose attribute_id would match that of the Sorcerer/Wizard attribute, and whose value would be 6 (the level a Sorcerer or Wizard could cast the Acid Fog spell at). Likewise, there would also be a record for that spell tying it to the Domain attribute_type and the Water attribute, with a value of 7.

I'm trying to wrap my brain around this; tell me if I'm misunderstanding what Acid Fog's records in spell_has_attribute would look like

spell_has_attribute_id
attribute_type_id
attribute_id
Spell_ID
value

01
[Descriptor]
[Acid]
[Acid Fog]
-

02
[Class]
[Sorc/Wiz]
[Acid Fog]
6

03
[Domain]
[Water]
[Acid Fog]
7

04
[Component]
[Verbal]
[Acid Fog]
-

05
[Component]
[Somatic]
[Acid Fog]
-

06
[Component]
[Arcane Material]
[Acid Fog]
A pinch of dried, powdered peas combined with powdered animal hoof

07
[Component]
[Divine Focus]
[Acid Fog]
-

(edit: dammit, my cool table didn't work! Anyway, I think you can read this, even if it's not as cool as I would've liked. Note that text in brackets would be replaced by numerical foreign keys pointing toward the appropriate primary keys in the appropriate tables, of course).

Is this how you're imagining it? Note that I changed "value" to a text field, to allow for material components to have a description there. This is probably a bad idea -- rarely do you need to sort by material component, so this is probably better left in the spell's main description -- but I wanted to throw it out there as an idea. (I also left out records for school, subschool, and source, since I think those belong in the spell's main record).

Daniel

 

[andargor]

Gentlemen, if I may respectfully ask, what is the purpose of making the schema so complex?

My database uses a flat model. If I want spells from a specific level (say, Druid 3rd level), I just do a simple:

SELECT * FROM spells35 WHERE level LIKE '%Drd 3%' ORDER BY name

I've developed a small spellbook manager program that uses these kinds of queries, and it works perfectly...

Andargor (perplexed)

 

[Pielorinho]

Gentlemen, if I may respectfully ask, what is the purpose of making the schema so complex?

My database uses a flat model. If I want spells from a specific level (say, Druid 3rd level), I just do a simple:

SELECT * FROM spells35 WHERE level LIKE '%Drd 3%' ORDER BY name

I've developed a small spellbook manager program that uses these kinds of queries, and it works perfectly...

Andargor (perplexed)

The idea behind normalization is to prepare for queries you haven't even THOUGHT OF yet, to design for purposes that haven't even occurred to you yet. If your data isn't normalized (e.g., if you have multiple pieces of information in a single field, such as spell levels for various classes and domains), it gets increasingly difficult to work with the data as you add new elements on to the database.

'Sides, it's a fun challenge .

Daniel

 

[javapadawan]

Java, that looks great! I really like the idea of lumping everything together in one "attribute" table, with the "attribute type" table differentiating between things like level and descriptors. Nice touch!

Thanks Pielorinho! I'm a big fan of using as few tables as possible.

1) Why have school, subschool, and source as attribute types? AFAIK, every spell has one and only one school and source, and spells have either zero or one subschools. I'd think these would be better left in the spell school.

My orginal intent was partly to try to keep any attributes that the user might conceivably want to add to, edit or remove, in the attribute table. Although not necessarily a common occurence, I figured adding new subschools, possibly for custom homebrew spells, or alternative campaign settings, might be a feature users would be interested in. I think a couple new subschools were added in 3.5, like Scrying, for example. Also, there are a very small number of spells that do have more than one subschool. I'm not sure about 3.5, but in 3.0 the spell Gate was listed as having both Creation and Calling as subschools.

I put Source in there for much the same reason. In 3.0 spells like Eagle's Splendor appeared both in Tome & Blood and in the Forgotten Realms Campaign Setting, and I wanted to have separate records for both. This was because I have a sorting feature which allows a user to "turn off" certain sourcebooks so that spells from those books don't show up when searching, but I wanted them to still be able to see the spell if it also existed in a source they had "turned on".

School on the other hand is mostly in there for data quality reasons. See, I also plan to use the attributes as drop down list options when creating new spells. This should help reduce data entry errors which might cause the spell search to behave in unexpected ways, such as mistyping Abjuration as Ajburation when entering a spell, and then wondering why that spell doesn't show up when searching for all Abjuration spells.

2) Components definitely needs to be separated either into multiple fields or into an attribute -- probably the latter, on reflection. Otherwise, you'll have multiple pieces of data in a single field, bad normalization; when you're grappled and want to search for spells lacking a somatic component, or when you've been captured and you need spells lacking a material component, or when that jerk of a cleric casts silence on you and you need a spell with no verbal component, it'd be real nice to be able to query based on those components (or their lack).

Yeah, I agree. I'd been kind of wavering as to whether or not to include components in the attribute table, but as you mentioned, it would make searching easier, and Silveras brought up a good point earlier in this thread about sources like the Book of Vile Darkness having new types of components. Good call.

I'm trying to wrap my brain around this; tell me if I'm misunderstanding what Acid Fog's records in spell_has_attribute would look like

You've got it exactly right. And I like the idea of adding the component description to the value field... It might not be necessary, but it would definitely be a nice touch. That would give me the ability to add something like a "Generate Shopping List" option to a wizard's spellbook that would list all necessary components for his or her spells. Again, not everyone would find such a thing useful, but I definitely think it would be cool. Thanks for the idea!

 

[javapadawan]

Gentlemen,

And ladies.

if I may respectfully ask, what is the purpose of making the schema so complex?

My database uses a flat model. If I want spells from a specific level (say, Druid 3rd level), I just do a simple:

SELECT * FROM spells35 WHERE level LIKE '%Drd 3%' ORDER BY name

You make a very good point Andargor. The old database I was using was exactly how you describe, and I got along just fine with SQL like your example.

But then I wanted to add some new features to my program, like sorting spells by level, doing a pages used count on a stored spellbook, and a few other things. With the data model as it was, those things would have been a difficult task at best, and the process would probably have been pretty slow as well (not to mention I use Java to boot ).

With the new model, I have a lot more freedom to do interesting stuff like that. And, like Pielorinho pointed out, it's just good, clean fun!

 

[Pielorinho]

School on the other hand is mostly in there for data quality reasons. See, I also plan to use the attributes as drop down list options when creating new spells. This should help reduce data entry errors which might cause the spell search to behave in unexpected ways, such as mistyping Abjuration as Ajburation when entering a spell, and then wondering why that spell doesn't show up when searching for all Abjuration spells.

Hmm -- wouldn't this be better to set off in a validation table, then? It just seems to me inefficient to set it off with a foreign key; instead, you can create a combo-box on the appropriate form, set it so that you can only choose items from a list, and set the list to items on the "school" list. That'll make the database smaller. In your way, each spell will have an extra records, like

spell_has_attribute_id: 07
attribute_type_id: [School]
attribute_id: [Conjuration]
Spell_ID: [Acid Fog]
value: -

in addition to the nine records in the attribute table and the one entry in the attribute_type table. My method adds only one field to the database, in the spell's description, as well as adding in a new table for validation purposes.

If you hate new tables, you can avoid it in two ways: first, put all the schools in the attribute table anyway, along with the attribute_type of school, and then set the combo box to validate according to a query on the attribute table for all attributes with the attribute_type of "school". Second, you can manually list the acceptable values for a combo box in the box's properties. I'd not recommend doing the last, because the info on the form won't propagate to other forms or uses of the database; either the first (a separate validation table) or the second (a query based on attributes with the type "school") would work fine.

And I like the idea of adding the component description to the value field... It might not be necessary, but it would definitely be a nice touch. That would give me the ability to add something like a "Generate Shopping List" option to a wizard's spellbook that would list all necessary components for his or her spells. Again, not everyone would find such a thing useful, but I definitely think it would be cool. Thanks for the idea!

Glad you like the idea! I'm still a little iffy on it: how well does Access do on performing numerical equations on text fields? That is, if a given field contains the values, "9", "3", "5", and "A lump of bitumen", can Access list all fields with a value between 2 and 6? If not, or if it's difficult, it might be best to find a different way to deal with material components.

At the risk of expanding the database further, perhaps the spell_has_attribute table can contain one more field, "attribute_note"? This would be useful on only two current types of attributes -- focuses and material components -- but if someone added in a new type of attribute (maybe, for example, a new type of spell that required a certain skill level as a component, or required a feat as a component, or something like that), they'd have the "notes" field already there for them. It'd be reserved for non-numerical information about the attribute that ends up being unique to a given spell.

This is fun to think about!
Daniel

 

[javapadawan]

Hmm -- wouldn't this be better to set off in a validation table, then? It just seems to me inefficient to set it off with a foreign key; instead, you can create a combo-box on the appropriate form, set it so that you can only choose items from a list, and set the list to items on the "school" list.

True. That was originally how I did things with the old database, by having a validation table of sorts for combo box options on create and search. It is pretty unlikely that anyone would want/need to add or modify spell schools... so it's probably a safe bet to move school out of attribute and back into the spell table.

Glad you like the idea! I'm still a little iffy on it: how well does Access do on performing numerical equations on text fields? That is, if a given field contains the values, "9", "3", "5", and "A lump of bitumen", can Access list all fields with a value between 2 and 6? If not, or if it's difficult, it might be best to find a different way to deal with material components.

Good point. If I was going to be doing a spell sort based on level, it would probably be something like, where attribute_type_id = [class] and attribute_id = [cleric] order by value. Then the data doesn't necessarily even need to be converted.

But in your example, I need to check for spells whose levels fall within a certain range. I could still say where attribute_type_id = [class] to avoid an attempt to parse "a pinch of wool" to an integer, however, that still leaves me with my numbers as strings. Same goes for if I want to try to calculate how many pages a spellbook with a given list of spells uses up.

I generally use SQL in Java to query the database, even though it's that nasty JetSQL Access uses. Oracle PL/SQL gives me a nice TO_NUMBER function I can fall back on, but I don't know if JetSQL offers anything similar. If not, or if their version is too time-intensive, we might well be better off having a note field or something similar to store non-numeric data, and keep our value field pure.

This is fun to think about!

Indeed. MUCH more fun than actually working.

 

[Cyberknight]

But in your example, I need to check for spells whose levels fall within a certain range. I could still say where attribute_type_id = [class] to avoid an attempt to parse "a pinch of wool" to an integer, however, that still leaves me with my numbers as strings. Same goes for if I want to try to calculate how many pages a spellbook with a given list of spells uses up.

I generally use SQL in Java to query the database, even though it's that nasty JetSQL Access uses. Oracle PL/SQL gives me a nice TO_NUMBER function I can fall back on, but I don't know if JetSQL offers anything similar. If not, or if their version is too time-intensive, we might well be better off having a note field or something similar to store non-numeric data, and keep our value field pure.



Indeed. MUCH more fun than actually working.

Access has a function called IsNumeric (VBA) that can be used like TO_NUMBER. I have used it before it did not seem to slow down anything. Plus with the amount of data we need to use I can't see it bogging anything down.
If in doubt you can add a boolean field to the table. True for numeric. (I don't suggest this one)

Also, if you want to convert the string to an interger use the CInt function.

 

[Silveras]

Access has a function called IsNumeric (VBA) that can be used like TO_NUMBER. I have used it before it did not seem to slow down anything. Plus with the amount of data we need to use I can't see it bogging anything down.
If in doubt you can add a boolean field to the table. True for numeric. (I don't suggest this one)

Also, if you want to convert the string to an interger use the CInt function.

Actually, IsNumeric() is a boolean that says a value IS or IS NOT numeric data. CInt() converts to 16-bit Integers; CSng() converts to single-precision (16-bit IIRC) floating-point numbers; CDbl() converts to double-precision (32-bit IIRC) floating point numbers; CStr() converts numeric data to Strings.

However, I do not know if they will work for external queries. They are VBA functions that are accessible to the Jet engine in Access; they may not work from Java.

Something like "SELECT CSng([column1]) AS [newname], [column2] FROM

WHERE IsNumeric([column1])" might work.

 

[javapadawan]

Thanks for the tips, Cyberknight and Silveras. I'll keep those functions in mind when I'm finally finished formatting and importing the 3.5 spells. If they don't work from Java, I'll probably go with Pielorinho's suggestion and keep the value field numeric while adding an additional text note field.