Homebrew spell database request

[Sir Valdon]

I'm doing a lot of homebrew spell stuff, and it'd be really handy to have a nice central database so that I can manage things more easily. I've made an Excel spreadsheet for the spells before now, but it's annoying having to go through umpteen separate sheets to change the wording on the Friends spell or whatever. So what I'm thinking of is a database with a nice, handy-dandy A4 form for printouts and such that will make editing spells and such much easier.

Spoiler: Details within

Show

Spells
The plan is to have a database that you can add every spell in D&D into with their full details, along with a couple of extra features for homebrew purposes. Each spell must have the following options:

• Name (text)
• Level (cantrips then 1st to 9th level)
• Concentration (check box)
• Ritual spell (check box)
• Components (verbal, somatic, material, thought)
• Material Components (appears only if material components selected ideally)
• Duration (Text)
• Casting Time (Special, Reaction, Bonus Action, Action, 1 Minute, 10 Minutes, 1 Hour, 8 Hours, 12 Hours, 24 Hours)
• Description (text)
• Magic Type (homebrew, see below)
• Available Classes (check boxes, see below)

By using the form, you can bring up any spell and print it out on an A4 (297 x 210 mm) sheet. It will include all data except “Available Classes”, which is hidden, but is used to let users create a list of all spells a certain class can use, and then print the whole list out if they want.

Actually filling in the form can be done later – I have the spell descriptions etc and can input them later but lack the database.

Magic Type & Available Classes
These need to refer to a table, and it should be possible to have multiple options for both. In addition, the list of available classes should determine the magic type in some cases, such that if (for sake of argument) clerics and wizards share a spell, the cleric will see it on their spell list as using divine magic, and the wizard as arcane magic. You should be able to expand the list of magic types and classes.

• Sir Valdon's Overpowered Spell
• - Magic Type: Arcane, Divine, Super Custom Magic
• - Available Classes: Cleric, Paladin, Wizard
• - If Class = Cleric then set Magic Type = Divine on form
• - If Class = Paladin then set Magic Type = Divine on form
• - If Class = Sorcerer (Divine Soul) then set Magic Type = Divine on form
• - If Class = Sorcerer (Wild Magic) then set Magic Type = Arcane on form
• - If Class = Wizard (Abjurer) then set Magic Type = Arcane on form
• - If Class = OP Knight then set Magic Type = Super Custom Magic on form
• - If Class = None set then Magic Type = Arcane, Divine, Super Custom Magic on form

 
Psionics
Psionic disciplines & talents should be separated from spells, and require the following options:

• Name (text)
• Concentration (check box)
• Ritual (check box)
• Psi Points minimum (number, must be lower or equal to Psi Points maximum)
• Psi Points maximum (number, must be higher or equal to Psi Points maximum)
• Components (verbal, somatic, material, thought)
• Material Components (appears only if material components selected ideally)
• Duration (Text)
• Casting Time (Special, Reaction, Bonus Action, Action, 1 Minute, 10 Minutes, 1 Hour, 8 Hours, 12 Hours, 24 Hours)
• Description (text)
• Magic Type (homebrew, see below)
• Available Classes (check boxes, see below)

Lost & Epic Spells / Psionics
Finally there should be a clone of the Spells & Psionics forms/databases, such that players can create or discover custom spells during the campaign without them by default belonging to their class / archetype spell list. The “Epic Spells” sheet should also include the following:

• Multiple Spellcasters (yes/no)
• Available Classes (as normal, but visible on this form)

Index
There should be an index that lists spells down the left-hand side (1st by spell level, 2nd by alphabetical order), and along the top by all classes & archetypes, including custom ones. Players can thus quickly scan to see whether or not their class can learn a particular spell. Psionics and lost/epic spells/psionics should belong on their own index, or at the very least kept separate and not mixed up.

I'm thinking something like MS Access will be fine for this, but whilst I'm okay at Excel and such I don't really know my way around Access at all.

[Anxe]

This seems more like a database question than a roleplaying question. Did you post this on a database forum of some kind as well as here? You might get better answers somewhere else.

[Sir Valdon]

Trouble is I don't have the faintest idea where to begin for that . D&D seems to attract computer-literate people though, so I was figured it was worth a shot here.

[Anxe]

There's a subreddit for it. https://www.reddit.com/r/Database/
No idea how useful it would be, but you might find somewhere else to look from there as well.

And obviously no harm in looking here as well. I'm sorry I couldn't be more help!

[Firest Kathon]

I would view it more a programming question than a database question. The database itself is not very complicated (two tables, I would guess), most work would be the frontend (data entry/retrieval).

As to how to develop it, there are four approaches:

1. Integrated database, e.g. MS Access, Apple filemaker, etc., where you build the UI also in the database software.
   Advantages:
   
   • Simple, only one tool required
   Disadvantages:
   
   • Every user needs the (paid) DB software (unless you can somehow export it as a standalone application)
   • How to share with your users?
   • How to give updates to your users? Copy the new version (what about custom spells?), export/import function, ...
2. Local database with application, i.e. you have a "real" application using a (likely integrated) database.
   Advantages:
   Disadvantages:
   
   • Development knowledge needed
   • How to share with your users?
   • How to give updates to your users? See above.
3. Online database with local application, i.e. the database is stored on a server and the UI is run locally
   Advantages:
   
   • Everyone gets central updates immediately
   Disadvantages:
   
   • Development knowledge needed
   • Hosting is usually paid
   • Access control (who may change the contents?)
   • Risk of IP infringement if copyrighted spells are added to the database
4. Online database with web application
   Advantages:
   
   • Everyone gets central updates immediately
   • UI is accessible form any device (e.g. smartphone)
   Disadvantages:
   
   • Development knowledge needed
   • Hosting is usually paid
   • Access control (who may access/change the contents?)
   • Risk of IP infringement if copyrighted spells are added to the database

[Sir Valdon]

As to how to develop it, there are four approaches

I figured MS Access (or another DB software package) would be the easiest way to go about it, as you ought to have all the tools necessary for actually making it at hand, rather than having to code anything. My goal as far as players go is that I can print off anything necessary, which also neatly avoids any copyright issues too. I should perhaps mention that I'm thinking actual tabletop RPG gaming here, rather than via Discord, Roll20 or the like, so I don't need to worry about putting stuff online so much.

[Ventruenox]

I'm going to move this thread to the Mad Sciences and Grumpy Technology forum for their expertise.

Personally, I would keep it in Excel and just make judicious use of filters, functions, and the "Replace All" command. Inserting additional rows or columns is simple enough for the addition of new spells/psionics. If you want to get fancy, maybe throw in a few VBA buttons.

[Firest Kathon]

Personally, I would keep it in Excel and just make judicious use of filters, functions, and the "Replace All" command. Inserting additional rows or columns is simple enough for the addition of new spells/psionics. If you want to get fancy, maybe throw in a few VBA buttons.

In my experience, it is really hard to create a maintainable Excel spreasheed for anything even slightly complicated. Excel is not a database, and making it do things for which a database is designed just leads to a big mess. Use the tools which have been designed to solve your problems. But then again, maybe I'm to pedantic (is that the right word?), and you should just use the tools you have and can use, especially if it's just for your own use.

[Manga Shoggoth]

Speaking as a Database Administrator and sometime Applications Programmer - trying to use a spreadsheet such as Excel as a database is a very bad idea (my first DBA assignment was fixing the problems caused by the previous incumbent who thought it was a good idea). Although spreadsheets look like they are doing the same job, they really aren't.

Firest Kathon pretty much covers the bases here - I would advise you to keep things local as hosting costs have a habit of adding up over time.

If you are going to hold the database yourself, and print off materials for your players, then Access is probably the simplest (albiet costly, unless you already have it) way of doing it. Access is actutally quite good for lightweight applications like this. Last time I used it it was possible to build a runtime application to distribute to other people, but that was a while ago.

Open Office and Libre Office may give you simillar functionality to Access via Base (ther are rivals to Microsoft Office, after all...), but I am not familliar with them.

Failing that, you can do local installations of a database (mysql, postgres, mongodb) and run scripts to produce HTML pages from them. What you get depends on your level of skill and what tools you have available. It is worth asking your players if they are any good at this sort of thing (and requests on the forums here will occasionally net you a helpful programmer).

You will have to spend a bit of time learning how to use a database, but it isn't that hard and it will be educational. Access has a lot of wizards for building forms and reports (you would use the forms for data entry and the reports for stuff to print off) which will give you a starting point. To go much beyond the wizards you will need some programming ability though.

[Sir Valdon]

If you are going to hold the database yourself, and print off materials for your players, then Access is probably the simplest (albiet costly, unless you already have it) way of doing it.

Yeah, I've got Access 2016 here, so that'd be the easiest option for me. Well, except for the fact that I don't really have any experience with it .

[Manga Shoggoth]

Yeah, I've got Access 2016 here, so that'd be the easiest option for me. Well, except for the fact that I don't really have any experience with it .

OK - I'll have a think about it and drop you a PM with a basic database design. Once you have the database design, coding up the forms should be a little easier.

(I'd do the whole thing for you, but I don't have a high enough version of access at home)

EDIT: I've done it - it's written up as a word document. Either PM me your email or use the "send email" function to contact me and I'll send it to you.