The Joys of RPG Database Design – Part 1

When I was a young gamer, I enjoyed the various systems that abounded for creating characters, planets, vehicles, creatures and other things used to populate game worlds. But I quickly got tired of the dice, pencils, and graph paper needed for tasks that obviously could be automated. So I developed tools using my computer to help me out – BASIC programs and spreadsheets. That fascination with the merging of tabletop games and digital tools never went away, and I’ve always been fond of different kinds of generators, mapping programs, and even productivity tools to assist me in my gaming.

About 8 years ago I took a crack at a project to create a vehicle design application for the SilCore Vehicle Construction System. I didn’t have a lot of experience, but it was a moderate success and I learned a lot. After I grew away from the project, I decided to tackle a bigger challenge – the Mekton Zeta Plus construction system.  Every time I’ve made an attempt, I’ve been stymied by the inconsistencies and sheer variety that the system allows. The database itself seemed like an impossible design – but I knew it was simply because I hadn’t found the right design pattern.

It appears, after about a week of renewed interest in the project and a virtual lifetime of additional experience, I’ve done it. I’ve gotten further in loading data in and having it be consistent than I ever have – up through Servos and Armor to having Beam Weapons fully input. The database design is complex, for sure, but my much greater experience  with database architecture has shown through.

The purpose of these series of posts is going to be to outline the design, and the decisions that went into that design, and what I’ve found works and doesn’t. I want to complete the project both to have something of a portfolio item and so I can start actually designing an application to go along with it.

The first thing that I’ve learned is that some kind of Entity-Attribute-Value design is essential to be able to model MZ+. What has not worked was a straight EAV design. At some point I would wind up with some kind of situation that isn’t modeled correctly or some kind of extremely complex data model. This time, what seems to have worked is a modified EAV model that allows for the consistencies and inconsistencies in the design system to exist side-by-side.

One thing that I had previously done was treat “Cost” as an attribute. That was a large stumbling block – until I noticed that cost is a constant. Everything has a cost in MZ+ (even if the cost is zero, which is rare). So with the new design I yanked Cost out of the attributes and created a separate set of tables to model the cost of various items. The next stumbling block was attributes that are linked to one another – for example, Cost, Space and Kills for a Servo. That I handled in a much different manner that I will get to in a later blog entry. The resulting schema has a respectable number of tables (18, but some may be added) that are all fairly narrow. Some of them contain nothing but integer values. Even then, querying against the data has not been nearly as painful as it was with the straight EAV architecture.

In the past I have toyed with a combination of different tables for the “types” of systems in MZ+, ranging from having separate tables for Type and Subtype to having everything in a Type table with a parent-child hierarchy. Most of those attempts resulted in difficulties in putting things into the correct table or being arranged in the hierarchy correctly. After re-examining the way that the systems are actually laid out in the MZ+ book, I came up with a solution that works. There is a SystemType table and a Systems table. SystemType contains all of the high level types (Additive, Multiplier, Servo, Armor, Weapon, etc.) with a parent-child hierarchy. The levels never go more than 3 deep (and currently, only one SystemType is a child of a child: Shields, which are a child of Weapons). The Systems table contains the actual systems (Arm, Beam Weapon, Alpha Armor, etc.) tied to a SystemType. There are no parent-child relationships in Systems. Instead, a separate table links Systems together. For example, Armor is really a linked system to the various Servos and the Shield System.

Next comes the SystemAttribute table. These are all of the things that define a system – Damage, Kills, SP, Range, various options, all except for Cost. A junction table relates the SystemAttribute to Systems in groups (this is more of a sanity check to insure that a SystemAttribute is properly related).  The next table of interest is SystemAttributeValue, which defines the possible values for the various attributes. One thing that I learned from my prior attempts was that it is nearly impossible to have only one value column when mixing numeric and alphanumeric values. To this end, the SystemAttributeValue table has two columns – numericvalue and stringvalue. This has allowed much greater flexibility in assigning the values, and actually helps out greatly in the data relationships further down the road.

So for now, that’s all regarding my exciting foray into designing a MZ+ database. Next up I’ll explain how I implemented actually using the attribute values to define one of the MZ+ systems.





Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s