1NswxYht

1DROP TABLE IF EXISTS event_interrested;
2DROP TABLE IF EXISTS event_participate;
3DROP TABLE IF EXISTS events;
4DROP TABLE IF EXISTS subscriptions;
5DROP TABLE IF EXISTS training_days;
6DROP TABLE IF EXISTS users;
7DROP TABLE IF EXISTS trainings;
8DROP TABLE IF EXISTS years;
9
10CREATE TABLE years (
11  `year` int(11) NOT NULL PRIMARY KEY,
12  `current` tinyint(1) NOT NULL DEFAULT 0
13);
14
15CREATE TABLE trainings (
16  training_id tinyint(4) PRIMARY KEY AUTO_INCREMENT,
17  training_name VARCHAR(50) NOT NULL,
18  descri text NOT NULL
19);
20
21CREATE TABLE training_days (
22  training_id tinyint(4),
23  step_date date NOT NULL,
24  descri text NOT NULL
25);
26
27CREATE TABLE users (
28  mail varchar(50) NOT NULL PRIMARY KEY,
29  firstname varchar(15) NOT NULL,
30  lastname varchar(15) NOT NULL,
31  pwd char(40) not null,
32  role char(5) NOT NULL DEFAULT 'user',
33  tel varchar(12),
34  address varchar(35) NOT NULL,
35  bank_num varchar(16),
36  balance int(11) NOT NULL DEFAULT 0,
37  pic tinytext,
38  training tinyint(4) NOT NULL DEFAULT 0,
39  verified tinyint(1) NOT NULL DEFAULT 0
40);
41
42CREATE TABLE events (
43  event_id int(11) PRIMARY KEY AUTO_INCREMENT,
44  `name` varchar(15) NOT NULL,
45  descri text NOT NULL,
46  start_date date NOT NULL,
47  end_date date NOT NULL,
48  lon varchar(20) NOT NULL,
49  lat varchar(20) NOT NULL,
50  url tinytext NOT NULL,
51  price int(11) NOT NULL,
52  `year` int(11) NOT NULL
53);
54
55CREATE TABLE event_interrested (
56  mail varchar(50) NOT NULL,
57  event_id int(11) NOT NULL
58);
59
60CREATE TABLE event_participate (
61  mail varchar(50) NOT NULL,
62  event_id int(11) NOT NULL,
63  payed int(1) DEFAULT 0
64);
65
66CREATE TABLE subscriptions (
67  mail varchar(50) NOT NULL,
68  `year` int(11) NOT NULL,
69  payed tinyint(1) NOT NULL DEFAULT 0
70);
71
72ALTER TABLE training_days
73  ADD PRIMARY KEY (training_id, step_date),
74  ADD KEY FK_training_id (training_id); 
75
76ALTER TABLE events
77  ADD KEY FK_year (year);
78
79ALTER TABLE event_interrested
80  ADD PRIMARY KEY (mail, event_id),
81  ADD KEY FK_mail (mail),
82  ADD KEY FK_event_id (event_id);
83  
84ALTER TABLE event_participate
85  ADD PRIMARY KEY (mail, event_id),
86  ADD KEY FK_mail (mail),
87  ADD KEY FK_event_id (event_id);
88 
89 ALTER TABLE subscriptions
90  ADD PRIMARY KEY (mail, year),
91  ADD KEY FK_mail (mail),
92  ADD KEY FK_year (year);
93
94ALTER TABLE training_days
95  ADD CONSTRAINT FK_td_training_id FOREIGN KEY (training_id) REFERENCES trainings (training_id);
96
97ALTER TABLE users
98  ADD CONSTRAINT FK_us_training FOREIGN KEY (training) REFERENCES trainings (training_id);
99  
100ALTER TABLE events
101  ADD CONSTRAINT FK_ev_year FOREIGN KEY (year) REFERENCES years(year);
102
103ALTER TABLE event_interrested
104  ADD CONSTRAINT FK_in_mail FOREIGN KEY (mail) REFERENCES users (mail),
105  ADD CONSTRAINT FK_in_event_id FOREIGN KEY (event_id) REFERENCES events(event_id);
106
107ALTER TABLE event_participate
108  ADD CONSTRAINT FK_pa_mail FOREIGN KEY (mail) REFERENCES users (mail),
109  ADD CONSTRAINT FK_pa_event_id FOREIGN KEY (event_id) REFERENCES events(event_id);  
110
111ALTER TABLE subscriptions
112  ADD CONSTRAINT FK_sub_mail FOREIGN KEY (mail) REFERENCES users(mail),
113  ADD CONSTRAINT FK_sub_year FOREIGN KEY (year) REFERENCES years(year);