[rpsls-web.git] / db / setup.sql

-- A user may or may not play games
create table Users(
  id integer primary key,
  name varchar(32) unique not null
);

create table Games(
  id integer primary key,
  created date
);

-- A player (ref. uid) is involved into a game (ref. gid)
create table Players(
  uid integer,
  gid integer,
  points integer not null default 0,
  foreign key(uid) references Users(id),
  foreign key(gid) references Games(id)
);

create table Moves(
  uid integer,
  gid integer,
  choice character(1) not null, --'r', 'p', 's', 'l' or 'k'
  mnum integer not null,
  foreign key(uid) references Users(id),
  foreign key(gid) references Games(id)
);
Commit	Line	Data
	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	);