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