root/websites/jwatch.jp/trunk/misc/jwatch_create.sql @ 11302

Revision 11302, 1.9 kB (checked in by lopnor, 7 years ago)

websites/jwatch.jp: start to make htmls.

Line 
1-- vim: ft=mysql
2DROP DATABASE IF EXISTS jwatch;
3CREATE DATABASE jwatch default character set utf8;
4
5use jwatch;
6create table `club` (
7    id int not null default 0 primary key,
8    shortname tinytext,
9    name tinytext,
10    name_i18n tinytext,
11    category tinyint
12);
13
14create table `player` (
15    `clubid` int not null default 0,
16    `year` int not null default 0,
17    `number` int not null default 0,
18    `serialno` int not null default 0,
19    `name` tinytext,
20    `name_i18n` tinytext,
21    `joindate` date,
22    `leavedate` date,
23    primary key (`clubid`, `year`, `number`, `serialno`)
24);
25
26create table `official` (
27    id tinyint not null auto_increment primary key,
28    name tinytext,
29    name_i18n tinytext
30);
31
32create table `stadium` (
33    id int not null primary key,
34    name tinytext,
35    name_i18n tinytext
36);
37
38create table `game` (
39    id char(20) not null default '' primary key,
40    category tinyint,
41    gameno tinyint,
42    kickoff datetime,
43    clubid_home int,
44    clubid_away int,
45    stadiumid int,
46    score_home tinyint default null,
47    score_away tinyint default null
48);
49
50create table `game_player` (
51    `gameid` char(20) not null default '',
52    `clubid` int not null default 0,
53    `number` tinyint not null default 0,
54    `position` enum('GK','DF','MF','FW'),
55    `intime` tinyint,
56    `outtime` tinyint,
57    `type` enum('starter', 'reserve'),
58    primary key (gameid, clubid, number)
59);
60
61create table `game_official` (
62    gameid char(20) not null default '',
63    officialid tinyint not null default 0,
64    `type` enum('referee', 'assistant', 'fourth'),
65    primary key (gameid, officialid)
66);
67
68create table event (
69    `id` int not null auto_increment primary key,
70    `gameid` char(20) not null default '',
71    `gametime` tinyint not null default 0,
72    `datetime` datetime,
73    `type` enum('goal', 'yellow', 'red', 'change', 'other', 'member', 'end'),
74    `clubid` int,
75    `player1` tinyint,
76    `player2` tinyint,
77    `description` tinytext
78);
Note: See TracBrowser for help on using the browser.