viric@0: -- use prova_pis;
viric@0: drop table if exists moviments ;
viric@0: drop table if exists ingressos ;
viric@0: drop table if exists butxaques ;
viric@0: 
viric@0: CREATE TABLE butxaques (
viric@0: 	id	MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
viric@0: 	nom	VARCHAR(50) NOT NULL,
viric@0: 	principal	BOOL,
viric@0: 
viric@0: 	PRIMARY KEY (id)
viric@0: 	) TYPE = InnoDB;
viric@0: 
viric@0: -- ingressos i despeses
viric@0: CREATE TABLE ingressos (
viric@0: 	data	TIMESTAMP NOT NULL,
viric@0: 	data_modif	TIMESTAMP NOT NULL,
viric@0: 	rao	TINYTEXT NULL,
viric@0: 	quantitat	DECIMAL(18,10) SIGNED NOT NULL,
viric@0: 	butxaca		MEDIUMINT UNSIGNED NOT NULL,
viric@0: 	comentari	TEXT,
viric@0: 
viric@0: 	PRIMARY KEY (data),
viric@0: 	INDEX (butxaca),
viric@0: 	FOREIGN KEY (butxaca) REFERENCES butxaques (id)
viric@0: 	) TYPE = InnoDB;
viric@0: 
viric@0: -- moviments de diners entre butxaques
viric@0: CREATE TABLE moviments (
viric@0: 	data	TIMESTAMP NOT NULL,
viric@0: 	data_modif	TIMESTAMP NOT NULL,
viric@0: 	rao	TINYTEXT NOT NULL,
viric@0: 	quantitat	DECIMAL(18,10) SIGNED NOT NULL,
viric@0: 	comentari	TEXT,
viric@0: 	butxaca_origen		MEDIUMINT UNSIGNED NOT NULL,
viric@0: 	butxaca_desti		MEDIUMINT UNSIGNED NOT NULL,
viric@0: 
viric@0: 	PRIMARY KEY (data),
viric@0: 	INDEX (butxaca_origen),
viric@0: 	INDEX (butxaca_desti),
viric@0: 	FOREIGN KEY (butxaca_origen) REFERENCES butxaques (id),
viric@0: 	FOREIGN KEY (butxaca_desti) REFERENCES butxaques (id)
viric@0: 	) TYPE = InnoDB;
viric@0: