db/setup.sql

   1 -- A user may or may not play games
   2 create table Users(
   3   id integer primary key,
   4   name varchar(32) unique not null
   5 );
   6
   7 create table Games(
   8   id integer primary key,
   9   created date
  10 );
  11
  12 -- A player (ref. uid) is involved into a game (ref. gid)
  13 create table Players(
  14   uid integer,
  15   gid integer,
  16   points integer not null default 0,
  17   foreign key(uid) references Users(id),
  18   foreign key(gid) references Games(id)
  19 );
  20
  21 create table Moves(
  22   uid integer,
  23   gid integer,
  24   choice character(1) not null, --'r', 'p', 's', 'l' or 'k'
  25   mnum integer not null,
  26   foreign key(uid) references Users(id),
  27   foreign key(gid) references Games(id)
  28 );