My character db design PART I!!
So.
Since I Forgot both Koihime’s sheet, my books, and what I was going to do for HQ, I started working on my database design. I got about 4 tables in and then.. stopped. (Half because soon, I will be cleaning up the place with a fury, mostly the windows, but.. ) and half because the design made no sense.
So let’s call this design b. It’s not even alpha. (It’s also a bad way to do it, as in Part III I will probably take the time to write out a formal design document and formally write up code instead of the pseudo SQL code used within these posts)
It’s pretty simple, but… a character has some certain features. However, how they are measured differs wildly. For example, a M&M character has no alignment, a 3.5e D&d has different from 4e and Exalted uses INtimacies instead. And while I am only doing this as needed, I should not design things so I need to alter what is in: merely can be added.
To resolve this, I needed to figure out how the sheets differ and how they are unique. As the testing sheet is a d20 M&M, I will focus on that and 3.5e/4e slightly.
However, we can at least start psuedocoding the main table
CREATE TABLE charBase{
charID INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT,
charName VARCHAR(100) NOT NULL,
charDesc TEXT,
charAlign TEXT,
charBkgd TEXT,
charAge INT(6);
}
It has the base requirements of an ID, the char name, the description, the background, and the addtional field of age. We may return to it later, but this is a good starting point. (also alignment, although in M&M It may be blank or just who the person/hero works for. ETc.)
d20 Games
First off, most of the values + generation are the same for the first part. So much so that d20stats will have these common:
STR, DEX, CON, INT, CHA, WIS, Init, Reflex, Will, Fort.
Also a common function: getStatMod() which uses the equation: floor((stat-10)/2);
We also need a charID field to tie in with the main field.
However, movement changes when we consider 3.5e, M&M and 4e. The first two use the same movement, but 4e does. 3e and 4e have levels, but M&M as a power system has pp and a power level. So now we have this
CREATE TABLE d20_stats{
charID INT(4) FOREIGN KEY AUTO_INCREMENT,
charSTR SMALLINT(3) NOT NULL DEFAULT 10,
charDEX SMALLINT(3) NOT NULL DEFAULT 10,
charCON SMALLINT(3) NOT NULL DEFAULT 10,
charINT SMALLINT(3) NOT NULL DEFAULT 10,
charWIS SMALLINT(3) NOT NULL DEFAULT 10,
charCHA SMALLINT(3) NOT NULL DEFAULT 10,
charInit SMALLINT(3),
charReflex SMALLINT(3),
charWill SMALLINT(3),
charMovement SMALLINT(3),
charMovement4e SMALLINT(3),
charLevel SMALLINT(3),
charPP INT(4),
charPL SMALLINT(3),
}
A sharp eye might note something wrong off the bat: What kind of movement. So we need to change it:
- charMovement SMALLINT(3),
+ charMovementAir SMALLINT(3),
+ charMovementLand SMALLINT(3),
+ charMovementWater SMALLINT(3),- charMovement4e SMALLINT(3),
+ charMovement4eAir SMALLINT(3),
+ charMovement4eLand SMALLINT(3),
+ charMovement4eWater SMALLINT(3),
Moving on, we have a few more tables to finish this article, and we should do so, first completing the d20stat table, then moving to feats and skills.
CREATE TABLE d20_stats{
charID INT(4) FOREIGN KEY,
charSTR SMALLINT(3) NOT NULL DEFAULT 10,
charDEX SMALLINT(3) NOT NULL DEFAULT 10,
charCON SMALLINT(3) NOT NULL DEFAULT 10,
charINT SMALLINT(3) NOT NULL DEFAULT 10,
charWIS SMALLINT(3) NOT NULL DEFAULT 10,
charCHA SMALLINT(3) NOT NULL DEFAULT 10,
charInit SMALLINT(3),
charReflex SMALLINT(3),
charWill SMALLINT(3),
charMovementAir SMALLINT(3),
charMovementLand SMALLINT(3),
charMovementWater SMALLINT(3),
charMovement4eAir SMALLINT(3),
charMovement4eLand SMALLINT(3),
charMovement4eWater SMALLINT(3),
charLevel SMALLINT(3),
charGrapple SMALLINT(3),
charPP INT(4),
charPL SMALLINT(3),
}
While feat names and effects vary, what is needed stays mostly the same, with just a point/acquire variance. Including the needed foreign key..
CREATE TABLE d20feats{
charID SMALLINT(3) FOREIGN KEY NOT NULL,
featName VARCHAR(255) NOT NULL,
featCost SMALLINT(4),
featAquire VARCHAR(100),
featDESC TEXT}
Before we move on to skills, we should add a field to the base table telling a viewer what sheet type it belongs to. Currently, we have freeform, mutants and masterminds 2nd, dungeons and dragons 3.5 and 4E. Also Exalted (and horror of horrors BESM 2nd). (ANd then some miscellanous systems). As such, I’m going to probably collapse the miscellanous into nothing for this, and address them later.
+ chrSystem ENUM('mm2nd','besm2nd','exalted2nd','dungon3.5e','dungeon4e','hero');
Skills. A /good/ system would maintain a skill id and …
… well, you know. BE good and all.
… yeah. I don’t have my books here, and I am not writing up variant skills.
…. anyway.
It needs a userID. And then a name, rank, associated stat, and misc mods. However. Associated S…tat. It is quite likely that this is best placed as an enum. So, our prototype looks like this
CREATE TABLE d20Skills{
charID INT(4) NOT NULL FOREIGN KEY,
skillName VARCHAR(255),
skillRank SMALLINT(3) ,
skillMisc SMALLINT(3) ,
skillSTAT ENUM('STR','DEX','CON','INT','WIS','CHA')}
And that is it for part 1. Part 2 covers Powers, COntainers, Arrays, Devices, Equipment, and HQ. (as you can tell – almost entirely M&M based). Later we will return to D&D 3.5, 4e, and Exalted.
Leave a Reply