spoon-discuss: db schema

["David E. Smith" <dave@xxxxxxxxxxxxxxx>]

# I added some comments of my own after each table, so things will make a
# bit more sense. Whee!


CREATE TABLE cache (
  cacheid varchar(16) NOT NULL default '',
  content blob NOT NULL,
  updatesrc varchar(250) NOT NULL default '',
  utime timestamp(14) NOT NULL,
  staletime int(11) NOT NULL default '0',
  PRIMARY KEY  (cacheid),
  UNIQUE KEY cacheid (cacheid)
) TYPE=MyISAM;
# --------------------------------------------------------
# This is for the much-lamented caching system. utime is its last
# update, staletime is how many seconds a page should be considered
# "fresh".


CREATE TABLE dims (
  dimid smallint(6) NOT NULL auto_increment,
  dimname varchar(42) NOT NULL default '',
  PRIMARY KEY  (dimid),
  UNIQUE KEY dim-id (dimid)
) TYPE=MyISAM;
# --------------------------------------------------------
# Just a list of dimensions (score, charm, etc.) Works with "players" and
# "playerdim". My life would be so much easier if MySQL had any sense
# of referential integrity...

CREATE TABLE grid2 (
  xc tinyint(4) NOT NULL default '0',
  yc tinyint(4) NOT NULL default '0',
  contents text NOT NULL,
  PRIMARY KEY  (xc,yc)
) TYPE=MyISAM;
# --------------------------------------------------------
# There was a "grid" but I don't use it no more.
# It's the ultra-simple system - x and y coords, and a text blob.
# (I keep track of shinies and scaries on a Post-It note, because that
# way I don't have to worry about keeping track of which objects are
# visible/invisible to players.)

CREATE TABLE keywords (
  rulenum mediumint(9) NOT NULL default '0',
  keyword tinytext NOT NULL
) TYPE=MyISAM;
# --------------------------------------------------------
# Again, really really obvious. Any DBA that can't figure out how this
# table works is not worthy of the title. :-)


CREATE TABLE nomicrules (
  rulekey int(11) NOT NULL auto_increment,
  rulenum int(11) NOT NULL default '0',
  rulerev int(11) NOT NULL default '0',
  ruletext text NOT NULL,
  ruletitle varchar(250) NOT NULL default '',
  itemtype enum('rule','motion','cfj','nada') NOT NULL default 'rule',
  nweek mediumint(9) NOT NULL default '0',
  owner mediumint(9) NOT NULL default '0',
  PRIMARY KEY  (rulekey),
  UNIQUE KEY rulekey (rulekey),
  KEY rulenum (rulenum)
) TYPE=MyISAM;
# --------------------------------------------------------
# I've been thinking about adding a "status" column for stuff (i.e.
# 'unrecognized','open','passed','failed','notjudged','judged' etc.)
# owner refers to a player, defaulting to 0 (The Administrator).

CREATE TABLE playerdim (
  player tinyint(4) NOT NULL default '0',
  dim tinyint(4) NOT NULL default '0',
  val float NOT NULL default '0',
  PRIMARY KEY  (player,dim)
) TYPE=MyISAM;
# --------------------------------------------------------
# player/dim cross-references


CREATE TABLE players (
  pid int(11) NOT NULL auto_increment,
  name varchar(250) NOT NULL default '',
  start datetime default NULL,
  email varchar(250) NOT NULL default '',
  inventory text NOT NULL,
  human enum('y','n') NOT NULL default 'y',
  alive enum('y','n') NOT NULL default 'y',
  PRIMARY KEY  (pid)
) TYPE=MyISAM;

# Again, mostly obvious. I don't use 'start' for anything - it's just a
# record of when an entity was entered. I added it in case someone tried
# some wacky seniority notions (please don't consider this an invitation
# to do so). "inventory" is just a general comments field. "human" is
# obvious, but "alive" isn't -- I had to add that for players that aren't
# on the roster, but who still own stuff (players that have forfeited,
# but still `own' proposals etc.