6
« on: August 20, 2012, 06:25:14 pm »
Having realized that the problem I'd been having before was basically just that I'd been trying to make an ad-hoc implementation of the relational calculus on my UFO data structures, I shifted gears and started sticking parse results into a proper relational database. The bad news from all this is that, predictably, I decided to use this as an opportunity to refamiliarize myself with a bunch of tangentially related stuff like database design and normalization, so the whole business is horribly overengineered and slow going. Also, Real Life™ got busy. Still, database population is almost done, which means that the DB schema is firming up; I'm going to toss it (and the rest of my code) up in a proper public repository once DB population is finalized, but for the moment here's the SQL for DB setup. Note that I use some quantity of Postgres-specific features which may or may not be easily removed for more general RDB compatibility.
CREATE TYPE team AS ENUM ('phalanx', 'alien', 'civilian');
CREATE TYPE range AS (min int, max int);
CREATE TYPE randval AS (center int, swing int);
CREATE TYPE usestyle AS ENUM ('holdtwohand','firetwohand','onehand');
DROP TABLE IF EXISTS "item";
CREATE TABLE "item" (
item_id text PRIMARY KEY,
item_name text NOT NULL,
type text NOT NULL,
price int NOT NULL,
size int CHECK (size >= 0) NOT NULL
);
DROP TABLE IF EXISTS "armour";
CREATE TABLE "armour" (
item_id text PRIMARY KEY REFERENCES item ON DELETE CASCADE,
useable team NOT NULL,
weight int CHECK (weight >= 0) NOT NULL
);
DROP TABLE IF EXISTS "protection";
CREATE TABLE "protection" (
item_id text REFERENCES armour ON DELETE CASCADE,
damageweight text,
resistvalue int NOT NULL,
PRIMARY KEY (item_id,damageweight)
);
DROP TABLE IF EXISTS "template";
CREATE TABLE "template" (
template_id text PRIMARY KEY,
strength range NOT NULL,
speed range NOT NULL,
accuracy range NOT NULL,
mind range NOT NULL,
close range NOT NULL,
heavy range NOT NULL,
assault range NOT NULL,
sniper range NOT NULL,
explosive range NOT NULL,
piloting range NOT NULL,
targeting range NOT NULL,
evading range NOT NULL,
health range CHECK (health > 0) NOT NULL
);
DROP TABLE IF EXISTS "unit";
CREATE TABLE "unit" (
unit_id text PRIMARY KEY,
unit_name text NOT NULL,
side team NOT NULL,
wearsarmour bool NOT NULL DEFAULT TRUE,
carriesweapons bool NOT NULL DEFAULT TRUE,
size int NOT NULL DEFAULT 1
);
DROP TABLE IF EXISTS "unit_templates";
CREATE TABLE "unit_templates" (
unit_id text NOT NULL REFERENCES unit ON DELETE CASCADE,
template_id text NOT NULL REFERENCES template ON DELETE CASCADE,
PRIMARY KEY (unit_id,template_id)
);
DROP TABLE IF EXISTS "resistance";
CREATE TABLE "resistance" (
unit_id text NOT NULL REFERENCES unit ON DELETE CASCADE,
damageweight text NOT NULL,
resistvalue int NOT NULL,
PRIMARY KEY (unit_id,damageweight)
);
DROP TABLE IF EXISTS "weapon";
CREATE TABLE "weapon" (
item_id text PRIMARY KEY REFERENCES item ON DELETE CASCADE,
use usestyle NOT NULL,
ammocap int CHECK (ammocap > 0) NOT NULL,
reload int CHECK (reload > 0) NOT NULL
);
DROP TABLE IF EXISTS "firedef";
CREATE TABLE "firedef" (
ammo_id text NOT NULL REFERENCES item (item_id) ON DELETE CASCADE,
weapon_id text NOT NULL REFERENCES weapon (item_id) ON DELETE CASCADE,
name text NOT NULL,
skill text NOT NULL,
vspread real CHECK (vspread >= 0) NOT NULL,
hspread real CHECK (hspread >= 0) NOT NULL,
crouch real CHECK (crouch >= 0) NOT NULL,
range numeric(10,1) CHECK (range >= 0) NOT NULL,
shots int CHECK (shots >= 0) NOT NULL,
ammoconsump int NOT NULL,
tus int NOT NULL,
damage randval NOT NULL,
damageweight text NOT NULL,
reaction bool NOT NULL DEFAULT TRUE,
PRIMARY KEY (ammo_id,weapon_id,name)
);
My biggest qualm with this design is that it requires me to interpret melee weapons as having 1 ammo and all of their attacks as consuming 0 ammo (except for throwing attacks, which consume 1), and with a 0 reload time, which all seems semantically unsound to me, but I think I'm confident enough in not actually getting into trouble as a result to just go with it.
~J, going all architecture astronaut
Show Posts