A second pair of eyes

Could someone who has more of a clue about Shadowrun 4 (especially the magicky parts) take a look at this DDL for a character database?  Please let me know if I got something wrong (especially text vs. numeric datatypes for magicky stuff).

CREATE DATABASE CHADOWRUN4;
CREATE TABLE PLAYER(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	NAME			VARCHAR(63)  NOT NULL,
	USERNAME		VARCHAR(31)  NOT NULL,
	PASS_HASH		CHAR(40),
	AVATAR			TINYBLOB,
	EMAIL			VARCHAR(255),
	WEBSITE			VARCHAR(255),
	PRIMARY KEY (ID),
		KEY (NAME),
		KEY (USERNAME)
) ENGINE=InnoDB;

CREATE TABLE `CHARACTER`(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	NAME			VARCHAR(31),
	ALIAS			VARCHAR(31)  NOT NULL,
	PLAYER			INT UNSIGNED,
	METATYPE		VARCHAR(31)  NOT NULL,
	AGE			TINYINT UNSIGNED,
	SEX			CHAR(1),
	NUYEN			INT UNSIGNED,
	LIFESTYLE		VARCHAR(23),
	TOTAL_KARMA		TINYINT UNSIGNED,
	CURRENT_KARMA		TINYINT UNSIGNED,
	STREET_CRED		TINYINT UNSIGNED,
	NOTORIETY		TINYINT UNSIGNED,
	PUBLIC_AWARENESS	TINYINT UNSIGNED,
	BODY			TINYINT UNSIGNED,
	AGILITY			TINYINT UNSIGNED,
	REACTION		TINYINT UNSIGNED,
	STRENGTH		TINYINT UNSIGNED,
	CHARISMA		TINYINT UNSIGNED,
	INTUITION		TINYINT UNSIGNED,
	LOGIC			TINYINT UNSIGNED,
	WILLPOWER		TINYINT UNSIGNED,
	EDGE			TINYINT UNSIGNED,
	ESSENCE			FLOAT   UNSIGNED,
	INITIATIVE		TINYINT UNSIGNED,
	MAGIC_RESONANCE		TINYINT UNSIGNED,
	CURRENT_EDGE		TINYINT UNSIGNED,
	ASTRAL_INIT		TINYINT UNSIGNED,
	MATRIX_INIT		TINYINT UNSIGNED,
	INIT_PASSES		TINYINT UNSIGNED,
	DATAPIC			TINYBLOB,
	PHYSICAL_DAMAGE		TINYINT UNSIGNED,
	STUN_DAMAGE		TINYINT UNSIGNED,
	COMMLINK		VARCHAR(31),
	RESPONSE		VARCHAR(31),
	SYSTEM			VARCHAR(31),
	FIREWALL		VARCHAR(31),
	SIGNAL			VARCHAR(31),
	PROGRAMS		VARCHAR(255),
	PRIMARY KEY (ID),
	        KEY (ALIAS, NAME),
	FOREIGN	KEY (PLAYER) REFERENCES PLAYER (ID),
		KEY (METATYPE)
) ENGINE=InnoDB;

CREATE TABLE SKILL(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	SKILL_NAME		VARCHAR(31)  NOT NULL,
	`TYPE`			VARCHAR(9),
	ATTRIBUTE		VARCHAR(31)  NOT NULL,
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID),
	        KEY (SKILL_NAME)
) ENGINE=InnoDB;

CREATE TABLE CHARACTER_SKILL(
	`CHARACTER`		INT UNSIGNED NOT NULL,
	SKILL			INT UNSIGNED NOT NULL,
	RATING			INT UNSIGNED NOT NULL,
	PRIMARY KEY (`CHARACTER`, SKILL),
	FOREIGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID),
	FOREIGN KEY (SKILL)       REFERENCES SKILL(ID)
) ENGINE=InnoDB;

CREATE TABLE QUALITY(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	QUALITY_NAME		VARCHAR(31)  NOT NULL,
	`TYPE`			VARCHAR(15),
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE CONTACT(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	CONTACT_NAME		VARCHAR(31)  NOT NULL,
	`CHARACTER`		INT UNSIGNED NOT NULL,
	LOYALTY_RTG		TINYINT UNSIGNED,
	CONN_RTG		TINYINT UNSIGNED,
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID),
		KEY (CONTACT_NAME),
	FOREIGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID)
) ENGINE=InnoDB;

CREATE TABLE RANGED_WEAPON(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	WEAPON			VARCHAR(63)  NOT NULL,
	DAMAGE			VARCHAR(31),
	AP			VARCHAR(31),
	`MODE`			VARCHAR(15),
	RC			VARCHAR(15),
	AMMO			VARCHAR(15),
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID),
		KEY (WEAPON)
) ENGINE=InnoDB;

CREATE TABLE CHARACTER_RANGED_WEAPON(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`CHARACTER`		INT UNSIGNED NOT NULL,
	WEAPON			INT UNSIGNED NOT NULL,
	AMMO			INT UNSIGNED,
	PRIMARY KEY (ID),
		KEY (`CHARACTER`, WEAPON),
	FOREIGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID),
	FOREIGN KEY (WEAPON)      REFERENCES RANGED_WEAPON(ID)
) ENGINE=InnoDB;

CREATE TABLE CYBERWARE(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	IMPLANT			VARCHAR(63)  NOT NULL,
	RATING			VARCHAR(15),
	ESSENCE			FLOAT,
	NOTES			VARCHAR(255),
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID),
		KEY (IMPLANT)
) ENGINE=InnoDB;

CREATE TABLE CHARACTER_CYBERWARE(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`CHARACTER`		INT UNSIGNED NOT NULL,
	IMPLANT			INT UNSIGNED NOT NULL,
	NOTES			VARCHAR(127),
	PRIMARY KEY (ID),
		KEY (`CHARACTER`, IMPLANT),
	FORIEGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID),
	FOREIGN KEY (IMPLANT)     REFERENCES CYBERWARE(ID)
) ENGINE=InnoDB;

CREATE TABLE MELEE_WEAPON(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	WEAPON			VARCHAR(63)  NOT NULL,
	REACH			VARCHAR(15),
	DAMAGE			VARCHAR(15),
	AP			VARCHAR(15),
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID),
		KEY (WEAPON)
) ENGINE=InnoDB;

CREATE TABLE CHARACTER_MELEE_WEAPON(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`CHARACTER`		INT UNSIGNED NOT NULL,
	WEAPON			INT UNSIGNED NOT NULL,
	PRIMARY KEY (ID),
		KEY (`CHARACTER`, WEAPON),
	FOREIGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID),
	FOREIGN KEY (WEAPON)	  REFERENCES MELEE_WEAPON(ID)
) ENGINE=InnoDB;

CREATE TABLE VEHICLE(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	VEHICLE			VARCHAR(63)  NOT NULL,
	HANDLING		TINYINT UNSIGNED,
	ACCEL			TINYINT UNSIGNED,
	SPEED			TINYINT UNSIGNED,
	PILOT			TINYINT UNSIGNED,
	BODY			TINYINT UNSIGNED,
	ARMOR			TINYINT UNSIGNED,
	SENSOR			TINYINT UNSIGNED,
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID),
		KEY (VEHICLE)
) ENGINE=InnoDB;

CREATE TABLE CHARACTER_VEHICLE(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`CHARACTER`		INT UNSIGNED NOT NULL,
	VEHICLE			INT UNSIGNED NOT NULL,
	PRIMARY KEY (ID),
		KEY (`CHARACTER`, VEHICLE),
	FOREIGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID),
	FOREIGN KEY (VEHICLE)     REFERENCES VEHICLE(ID)
) ENGINE=InnoDB;

CREATE TABLE ARMOR(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	ARMOR			VARCHAR(63)  NOT NULL,
	BALLISTIC		TINYINT UNSIGNED,
	IMPACT			TINYINT UNSIGNED,
	NOTES			VARCHAR(255),
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID),
		KEY (ARMOR)
) ENGINE=InnoDB;

CREATE TABLE CHARACTER_ARMOR(
	`CHARACTER`		INT UNSIGNED NOT NULL,
	ARMOR			INT UNSIGNED NOT NULL,
	NOTES			VARCHAR(127),
	PRIMARY KEY (`CHARACTER`, ARMOR),
	FOREIGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID),
	FOREIGN KEY (ARMOR)       REFERENCES ARMOR(ID)
) ENGINE=InnoDB;

CREATE TABLE SPELL(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	SPELL			VARCHAR(63)  NOT NULL,
	`TYPE`			VARCHAR(63),
	RANGE			VARCHAR(31),
	DURATION		VARCHAR(63),
	DRAIN_VALUE		TINYINT,
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID),
		KEY (SPELL)
) ENGINE=InnoDB;

CREATE TABLE CHARACTER_SPELL(
	`CHARACTER`		INT UNSIGNED NOT NULL,
	SPELL			INT UNSIGNED NOT NULL,
	PRIMARY KEY (`CHARACTER`, SPELL),
	FOREIGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID),
	FOREIGN KEY (SPELL)       REFERENCES SPELL(ID)
) ENGINE=InnoDB;

CREATE TABLE SPIRIT(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	SPIRIT			VARCHAR(31)  NOT NULL,
	`CHARACTER`		INT UNSIGNED NOT NULL,
	FORCE			VARCHAR(31),
	SERVICES		VARCHAR(127),
	BOUND			TINYINT UNSIGNED,
	PRIMARY KEY (ID),
		KEY (SPIRIT, `CHARACTER`),
	FOREIGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID)
) ENGINE=InnoDB;

CREATE TABLE ADEPT_POWER(
	ID			INT UNSIGNED NOT NULL AUTO_INCREMENT,
	NAME			VARCHAR(31)  NOT NULL,
	RATING			TINYINT UNSIGNED,
	DESCRIPTION		VARCHAR(1023),
	PRIMARY KEY (ID),
		KEY (NAME)
) ENGINE=InnoDB;

CREATE TABLE CHARACTER_ADEPT_POWERS(
	`CHARACTER`		INT UNSIGNED NOT NULL,
	ADEPT_POWER		INT UNSIGNED NOT NULL,
	RATING			TINYINT UNSIGNED,
	PRIMARY KEY (`CHARACTER`, ADEPT_POWER),
	FOREIGN KEY (`CHARACTER`) REFERENCES `CHARACTER`(ID),
	FOREIGN KEY (ADEPT_POWER) REFERENCES ADEPT_POWER(ID)
) ENGINE=InnoDB;
Advertisements
  1. #1 by Joshua on November 25, 2009 - 7:26 AM

    The Chad, do the datatypes for the magicky stuff like Adept Powers and Spirit seem okay to you? Like INT vs VARCHAR vs FLOAT? Thanks for the input. I can get started building Enterprise Beans around this then.

    • #2 by Chadwick on November 25, 2009 - 7:42 AM

      So for things like Adept Power, why did you choose int instead of varchar? (honest question, I’m not sure about potential advantages there)

      • #3 by Joshua on November 25, 2009 - 7:47 AM

        Mainly because I wasn’t sure if it was a number (INT) or word (VARCHAR). I figured it was a number.

        It’s possible to store numbers as character data but then you lose the ability to do basic arithmetic on them without very dumb code.

    • #4 by Chadwick on November 25, 2009 - 8:56 AM

      Well on your Table CHARACTER_ADEPT_POWERS, for example, ADEPT_POWER, I assume is the name of a given power. But then, if you can store a string as INT, it may not be a problem. What are you coding in here?

      • #5 by Joshua on November 25, 2009 - 9:07 AM

        Well, this is DDL for MySQL 5.0 but the actual program will be Java (J2EE).

        You can’t store a string as an INT – but you can store an integer as a VARCHAR. That particular field is a link back to the ADEPT_POWER.ID field so it’s an int.

        I didn’t really mean for you to get bogged down in the technical stuff, just to confirm that I had the magicky numbers stored as numbers and the magicky stuff text data stored as VARCHARs. Thanks for your help, BTW. You helped me catch a few problems without even knowing it. 🙂

        • #6 by Chadwick on November 25, 2009 - 9:34 AM

          Well it’s always helpful making you explain yourself.

          • #7 by Joshua on November 25, 2009 - 9:38 AM

            I find that, too. If I explain to my mom (who last programmed in 1987, in BASIC) my dilemma, she forces me to slow my brain down and explain it in simpler terms. In that process, I, myself, better understand the problem and can usually figure out the solution much easier. Even if she never actually understands anything I’m saying. 🙂

  2. #8 by Joshua on November 25, 2009 - 7:36 AM

    I should probably change CHARACTER.NUYEN from INT UNSIGNED to INT since it is possible to be overdrawn…

  3. #9 by Chadwick on December 7, 2010 - 10:37 AM

    So I’ll just comment here since this was a Shadowrun post, and I don’t feel up to a full post. I’m just rather ill is all—hence the cancellation. Bathroom trips are becoming more frequent as I go through the day sort of thing. General abdominal discomfort, etc. I’m sure you don’t need the messy details.

    • #10 by Joshua on December 7, 2010 - 10:40 AM

      We hope you feeling better the Chad! I’m sorry I couldn’t hear you on the phone. My cell phone is t3h sux0r.

    • #11 by Joshua on December 7, 2010 - 7:37 PM

      Is teh Chad feeling better now?

  1. Chadowrun Online Dev Update « Around Teh Table

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: