RE: spoon-discuss: db schema

["Jonathan Van Matre" <JVanMatre@xxxxxxxx>]

MySQL actually does have a modicum of referential integrity if you use InnoDB (version 3.23.44 and up), but let's assume that's not available to you for the time being.  I haven't used it myself, anyway, so I'm not sure how well it works.

So, a few early suggestions...

1)  Convert all your int and mediumint columns to smallint.  The realistic chances of our exceeding 32,767 players or nweeks are practically nil.  32,767 rule/CFJ objects and revisions might happen eventually, but not for a long while.  If you really want to be safe, I think mediumint for the rulekey and smallint for the other integers in the nomicrules table should provide sufficient headroom.  That allows 32,767 rules/CFJs with 32,767 revisions each, and 8388607 total unique object/revision iterations.

2)  Use the same name and data type for columns you are joining.  E.g.  playerdim.dim and dims.dimid should share the same data type and column name.  Likewise for playerdim.player and players.pid.  You should get better join performance this way.

3)  Split players into two tables, one fixed-record-length, one dynamic.  Require players with player names longer than 20 characters to declare a short name to be used to represent them on all pages other than the roster.  This one may require a proposal to modify the player name rule, but maybe not, since it does still permit the player's name to actually be up to 255 characters.  

Then you have:

CREATE TABLE players (
   pid smallint(6) NOT NULL auto_increment,
   short_name char(20) NOT NULL default '',
   start datetime default NULL,
   human enum('y','n') NOT NULL default 'y',
   alive enum('y','n') NOT NULL default 'y',
   PRIMARY KEY  (pid)
 ) TYPE=MyISAM;

# This smaller fixed-length table should provide better performance
# at a minimal cost of space for the fixed-length char(20) field.

CREATE TABLE players_roster (
   pid smallint(6) NOT NULL auto_increment,
   name varchar(250) NOT NULL default '',
   email varchar(250) NOT NULL default '',
   inventory text NOT NULL,
   PRIMARY KEY  (pid)
 ) TYPE=MyISAM;

# This dynamic data is really only needed for the roster page.

Yes, it's not 3rd normal form to do this, but that's a rule that's made to be broken when performance demands it.  Besides, 3NF is mostly cosmetic when you don't have relational integrity to enforce it.

That's all I've got for now.  Still banging on some other ideas which I'll post later.

--Scoff!