SRD Spells Database 3.5
- Thread starter The_Dood
- Start date
Pielorinho
Iron Fist of Pelor
re: components in a separate table -- that could certainly work. I was thinking that, given the finite number of component types (less than 10), it'd probably be easier to put them in the spell table. However, if you want to make room for additional types (truenames etc.), another table would probably work better.
And I'd looooove to see this information in a database form, get a chance to play around with it; even if it's fairly rough, I might be able to make some useful suggestions, and maybe design a friendly form for its use (my main DB work has been to design idiot-proof systems for computer-illiterate coworkers). Thanks, dood, for all the work you've done with it; this sounds like a great resource!
Daniel
And I'd looooove to see this information in a database form, get a chance to play around with it; even if it's fairly rough, I might be able to make some useful suggestions, and maybe design a friendly form for its use (my main DB work has been to design idiot-proof systems for computer-illiterate coworkers). Thanks, dood, for all the work you've done with it; this sounds like a great resource!
Daniel
Ahrimon
Bourbon and Dice
It would be a lot of work to make one capable to handle multiple characters. But it's dooable by one person. I created mine to just create spell sheets so it would take some additional development to do more but the basics are the same. I broke my tables down like this:
Table Spells:
Autonumber (Primary Key)
Spell Name - Text
School - Relational to autonumber in Table Schools.
Sourcebook - Relational to autonumber in Table Sourcebook.
[Add unique fields here, like description]
[Add relational fields for common factors, like range etc.]
Table Schools:
Autonumber (Primary Key)
School - Text
Table Sourcebook:
Autonumber (Primary Key)
Book - Text
Abbreviation - Text
Table Classes:
Autonumber (Primary Key)
Class - Text
Table Spell List(Allows a custom list for each class):
Class - Relational to autonumber in Table Classes
Level - Number
Everything else is just querry's. I was able to build custom spell lists for each class. I could sort by sourcbook for those campaigns that restrict what sourcebooks are allowed. I could expand the list and sort by school if I wanted too. I could also remove spells from specific schools if I didn't want them on that list.
It shouldn't take too much to create a program that will create and track spells known for each character. Spells memorized might be a bit more, but it's still doable.
Ahrimon
Table Spells:
Autonumber (Primary Key)
Spell Name - Text
School - Relational to autonumber in Table Schools.
Sourcebook - Relational to autonumber in Table Sourcebook.
[Add unique fields here, like description]
[Add relational fields for common factors, like range etc.]
Table Schools:
Autonumber (Primary Key)
School - Text
Table Sourcebook:
Autonumber (Primary Key)
Book - Text
Abbreviation - Text
Table Classes:
Autonumber (Primary Key)
Class - Text
Table Spell List(Allows a custom list for each class):
Class - Relational to autonumber in Table Classes
Level - Number
Everything else is just querry's. I was able to build custom spell lists for each class. I could sort by sourcbook for those campaigns that restrict what sourcebooks are allowed. I could expand the list and sort by school if I wanted too. I could also remove spells from specific schools if I didn't want them on that list.
It shouldn't take too much to create a program that will create and track spells known for each character. Spells memorized might be a bit more, but it's still doable.
Ahrimon
Bozidar said:i know you uploaded those files to this site somewhere last night, is that file available somewhere? I'm at work, and i couldn't email the file to myself because our firewall here picked up something it thought was a curse, and rejected the email
I uploaded it to the Download area. It hasn't appeared yet, so I guess they need to approve it. I'll link to it in my sig as soon as it's up.
Andargor
DM's Familiar has all the SRD spells. There's an Advanced Search feature that let's you search by name, class, SR, saves, level (show me all 3rd level wizard spells without SR that have a Fire descriptor). It has Import/Export built in so you can easily add spells from other sources.
The character section can store spells for the character. It tracks number prepped, spells per day, DC modifiers for spells. You can have multiple spellbooks for multiclass character or for different setups you want.
The character section can store spells for the character. It tracks number prepped, spells per day, DC modifiers for spells. You can have multiple spellbooks for multiclass character or for different setups you want.
Cyberknight
First Post
The_Dood said:
My email address is troyashley at comcast dot net.
I'm probably going to be busy this weekend but I should be able to spend some time on Monday. First thing we are gonna have to do is normalize the db. It will make it easier to do some of the suggestions everybody is asking for.
Bozidar said:i know you uploaded those files to this site somewhere last night, is that file available somewhere? I'm at work, and i couldn't email the file to myself because our firewall here picked up something it thought was a curse, and rejected the email
Here it is! Thanks Morrus!
The_Dood, perhaps you could load up yours as well?
Andargor
javapadawan
First Post
Oooo, data modeling! Can I play?
I've been working on a new model for the data in my spell database, primarily because at first I had all the spell data denormalized into one table, with a setup much like The Dood's, but I now want to add a feature to my program that sorts by spell level, and also allows for adding custom descriptors, domains, classes, etc.
What I have thus far looks like this, and I'd love to hear any comments or suggestions. Additionally, if you like this design at all, please feel free to use or improve upon it for yourself:
spell
-----
spell_id (AutoNumber)
name (Text)
components (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)
*I separated effect type from effect hoping to make searching for spells which have a Target as opposed to Area effect spells easier.
attribute_type
--------------
attribute_type_id (Number)
attribute_type (Text)
*Examples of attribute types would be: School, Subschool, Descriptor, Class, Domain, Source. ID is not AutoNumber since these aren't created on the fly and can be used as constants for grouping attributes.
attribute
---------
attribute_id (AutoNumber)
attribute_type_id (Number, foreign key: attribute_type)
attribute (Text)
information (Memo, nullable)
abbrev (Text, nullable)
*So, for example, this is where I'd list all the spell Schools (Conjuration, Abjuration, etc.) with an attribute_type_id matching that of the School attribute_type. The information field is for holding miscellaneous data, like granted powers for the domains. The abbrev field is what is sounds like it is, so for example, the attribute Sorcerer/Wizard (attribute_type Class) would have Sor/Wiz in it's abbrev field.
spell_has_attribute
-------------------
spell_has_attribute_id (AutoNumber)
attribute_type_id (Number, foreign key: attribute_type)
attribute_id (Number, foreign key: attribute)
spell_id (Number, foreign key: spell)
value (Number)
*This table, then, is what ties the attribute to a particular spell, and each spell may have none, one, or many of any particular attribute type. The value field can hold the actual spell level.
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 still on the fence about whether or not component types, like material, verbal, XP, etc., should be attributes rather than plain text. Anyway, if you read this far, thanks, and I'd welcome any feedback, even if it questions my sanity.
I've been working on a new model for the data in my spell database, primarily because at first I had all the spell data denormalized into one table, with a setup much like The Dood's, but I now want to add a feature to my program that sorts by spell level, and also allows for adding custom descriptors, domains, classes, etc.
What I have thus far looks like this, and I'd love to hear any comments or suggestions. Additionally, if you like this design at all, please feel free to use or improve upon it for yourself:
spell
-----
spell_id (AutoNumber)
name (Text)
components (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)
*I separated effect type from effect hoping to make searching for spells which have a Target as opposed to Area effect spells easier.
attribute_type
--------------
attribute_type_id (Number)
attribute_type (Text)
*Examples of attribute types would be: School, Subschool, Descriptor, Class, Domain, Source. ID is not AutoNumber since these aren't created on the fly and can be used as constants for grouping attributes.
attribute
---------
attribute_id (AutoNumber)
attribute_type_id (Number, foreign key: attribute_type)
attribute (Text)
information (Memo, nullable)
abbrev (Text, nullable)
*So, for example, this is where I'd list all the spell Schools (Conjuration, Abjuration, etc.) with an attribute_type_id matching that of the School attribute_type. The information field is for holding miscellaneous data, like granted powers for the domains. The abbrev field is what is sounds like it is, so for example, the attribute Sorcerer/Wizard (attribute_type Class) would have Sor/Wiz in it's abbrev field.
spell_has_attribute
-------------------
spell_has_attribute_id (AutoNumber)
attribute_type_id (Number, foreign key: attribute_type)
attribute_id (Number, foreign key: attribute)
spell_id (Number, foreign key: spell)
value (Number)
*This table, then, is what ties the attribute to a particular spell, and each spell may have none, one, or many of any particular attribute type. The value field can hold the actual spell level.
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 still on the fence about whether or not component types, like material, verbal, XP, etc., should be attributes rather than plain text. Anyway, if you read this far, thanks, and I'd welcome any feedback, even if it questions my sanity.
Pielorinho
Iron Fist of Pelor
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!
I just have a couple questions:
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.
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).
Daniel
I just have a couple questions:
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.
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).
Daniel
Similar Threads
Recent & Upcoming Releases
-
June 16 2026 -
June 16 2026 -
September 16 2026 
Arcana Unleashed(Dungeons & Dragons)
Rulebook featuring "high magic" options, including a host of new spells.
Replies (52) -
September 16 2026 -
October 1 2026 -
October 6 2026 -
January 1 2027
