|
1 -- use prova_pis; |
|
2 drop table if exists moviments ; |
|
3 drop table if exists ingressos ; |
|
4 drop table if exists butxaques ; |
|
5 |
|
6 CREATE TABLE butxaques ( |
|
7 id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, |
|
8 nom VARCHAR(50) NOT NULL, |
|
9 principal BOOL, |
|
10 |
|
11 PRIMARY KEY (id) |
|
12 ) TYPE = InnoDB; |
|
13 |
|
14 -- ingressos i despeses |
|
15 CREATE TABLE ingressos ( |
|
16 data TIMESTAMP NOT NULL, |
|
17 data_modif TIMESTAMP NOT NULL, |
|
18 rao TINYTEXT NULL, |
|
19 quantitat DECIMAL(18,10) SIGNED NOT NULL, |
|
20 butxaca MEDIUMINT UNSIGNED NOT NULL, |
|
21 comentari TEXT, |
|
22 |
|
23 PRIMARY KEY (data), |
|
24 INDEX (butxaca), |
|
25 FOREIGN KEY (butxaca) REFERENCES butxaques (id) |
|
26 ) TYPE = InnoDB; |
|
27 |
|
28 -- moviments de diners entre butxaques |
|
29 CREATE TABLE moviments ( |
|
30 data TIMESTAMP NOT NULL, |
|
31 data_modif TIMESTAMP NOT NULL, |
|
32 rao TINYTEXT NOT NULL, |
|
33 quantitat DECIMAL(18,10) SIGNED NOT NULL, |
|
34 comentari TEXT, |
|
35 butxaca_origen MEDIUMINT UNSIGNED NOT NULL, |
|
36 butxaca_desti MEDIUMINT UNSIGNED NOT NULL, |
|
37 |
|
38 PRIMARY KEY (data), |
|
39 INDEX (butxaca_origen), |
|
40 INDEX (butxaca_desti), |
|
41 FOREIGN KEY (butxaca_origen) REFERENCES butxaques (id), |
|
42 FOREIGN KEY (butxaca_desti) REFERENCES butxaques (id) |
|
43 ) TYPE = InnoDB; |
|
44 |