Creating Databases

Over the years, I have always maintained databases for spells, monsters, equipment, treasure, magic items, etc. In the past, this meant a lot of typing. Today, it is possible to simply grab the text in electronic form and start massaging it into whatever format you need.
Why Would I Want A Database?
There are a number of reasons for maintaining databases:
- Provide access to information when books aren’t available.
- Provide search capabilities that books can’t provide.
- Document changes to spells, magic items, etc.
- Use in conjunction with other tools to automate tasks.
There are probably a number of other reasons that don’t come to mind at the moment, but those are the primary ones.
As you know, my rule system bears little resemblance to any other game system. That makes some resources (certain spells, monsters, magical items, etc) taken from other games incompatible with mine. In order to use them, I need to make a lot of modifications. Sticky notes just don’t cut it. So I keep databases of everything. I put my databases online (on a private website) so I can access them from anywhere, plus I have written tools to manipulate the data in various ways. In addition, my player’s have limited access to the databases in order to view the rules, as they pertain to my world, online.
Even if you don’t make any modifications to the rules, these databases are handy to have around. But if you do intend to make changes, starting with a database makes the whole process far easier.
What Is A Database?
I throw the term “database” around a lot and use it very loosely to mean any of a number of things. To many people, a database is exclusively a set of data laid out in DBase, FoxBASE, Access or similar database format. Typically, when I refer to a database I am referring collectively to both an Excel spreadsheet (for stats) and a Word document (for text). When I put this “database” online, I combine the two into a number of tables within a MySQL database. Although purists will disagree, to me a database is simply any collection of data, regardless of format, that is conveniently laid out for me to work with.
How Do I Create A Database?
As I said earlier, my first databases were the result of an inordinate amount of typing. You are fortunate in that you now have alternatives to entering everything manually. WotC provides the 3.5e SRD for download in electronic form, and nicely formatted for your use. Alternately, Paizo provides their entire rule system online, including all the databases you will need and in various formats, at their PF SRD site. If you run a more traditional game, Labyrinth Lord is a d20 retro-clone that comes pretty close to the original rules and has free downloads (sans art) of their rule system. If you run a 4e game, you can get a DDI subscription and do a lot of cutting and pasting.
Creating, maintaining, manipulating, and otherwise working with a database, regardless of format, requires technical knowledge of the tools you choose to work with. Because such work requires a degree of expertise, I can’t simply write out instructions on how to create your databases. But if you have the skills, I strongly recommend maintaining your own databases, especially if you like to tinker with the rules.
If you don’t have the skills, I still recommend downloading the appropriate rule set and doing what you can.
Getting Started
The Pathfinder databases are available in various formats. I recommend downloading them in pure text format and dumping each into an Excel spreadsheet. No conversion is necessary. The 3.5e documents are in RTF format so you can just convert each (save as) to native Word format. Labyrinth Lord, if memory serves, comes in PDF format which means some copying and pasting. Some PDFs copy and paste better than others. You may be able to grab the whole thing and dump it into a Word document in one shot or you may have to cut and paste smaller portions at a time.
A Word document is great for text but it is a terrible format for editing stats, especially if stats are separated by entry. What I do is make a copy of the Word document and then delete all the text, leaving only the entry names and stat blocks. With a little massaging, you can standardize the stat blocks and then cut and paste each one into a separate column in excel. Once you have all the data in columns, you can copy and paste special | transpose, to put the data into rows instead (I find data much easier to work with in this orientation). When that’s done, delete the Word copy you were working with and go back to the original to delete the stat blocks, leaving only the text.
Once I have all the stats in an Excel spreadsheet and the text in a Word document, I leave them in that format. For wholesale changes, I find it faster and easier to do my editing in these programs. Once I have things more or less finished, I convert to a MySQL database. Minor changes and additions are then handled directly within the database. If I need to make large-scale changes (that can’t easily be accomplished with SQL statements) I export the SQL database as comma-delimited text, import it into Excel to make my changes, and then reverse the process to update the database.
What About Art?
Some people prefer to incorporate images directly into the database. I’m not one of those people. I prefer to dump all my images into a single directory and have only the name of the image listed in the database entry. This reduces the size of the database significantly which makes backups and downloads smaller but also allows direct access to the images without having to go through the database.
Since I use a fairly insane mix of 3rd and 4th edition GURPS with lots of nerdly house rules, I really need to use a database.
I used to use mySQL, but right now, since I lost all of my old dbs in a crash anyway, I’m trying postgreSQL. My main reason for using postgre, though, is because I’m interested in the postGIS geographical extensions. I got a shiny new degree in geography and a newfound love for GIS as a mapping tool so… there it is.
All of my current house rules, most of my 4th ed rules and about half of the 3rd ed rules I use are in my rules db so far. I also have separate dbs for setting information(this is where the GIS extensions come in handy: who, what, when, why and, WHERE) and scenario info. I spent a lot of time working on the structure of my rules db, I think I followed Codd’s rules. I’m still working on the organization of my setting db and a skeleton scenario template.
I deal with too many poorly structured dbs at work and spent too much time trying to clean up mistakes and mismatches that shouldn’t have been possible in properly designed databases. Somebody got paid to make those lazy things… Currently, my rules db is the biggest thing I’ve ever put together myself and it’s pretty tiny compared to the big enterprise things, but it should be a lot solider. >:-(
I agree with you on the art. Just have the db link to it. Seeing as I’m using GURPS, I didn’t have any use for the rulebook art, but I plan to incorporate imagery into my setting data and probably scenarios. Another thing to worry about…