create_table.sql

/* Table utilisateur */
CREATE TABLE IF NOT EXISTS user_account
(
    id_user INT NOT NULL AUTO_INCREMENT,
    name_user VARCHAR(20) NOT NULL,
    firstname VARCHAR(20) NOT NULL,
    CONSTRAINT pk_user_account PRIMARY KEY (id_user)
)ENGINE = InnoDB;

/* Table compte banquaire */
CREATE TABLE IF NOT EXISTS bank_account
(
    id_account INT NOT NULL AUTO_INCREMENT,
    type_account VARCHAR(20) NOT NULL, /* (Courant, Épargne, etc...) */
    real_balance FLOAT NOT NULL DEFAULT 0.0,
    anticipate_balance FLOAT NOT NULL DEFAULT 0.0,
    id_user INT NOT NULL,
    CONSTRAINT p_bank_account PRIMARY KEY (id_account),
    CONSTRAINT fk_bank_account_id_user FOREIGN KEY (id_user) REFERENCES user_account(id_user)
)ENGINE = InnoDB;

/* Table chèque */
CREATE TABLE IF NOT EXISTS cheque_account
(
    id_cheque INT NOT NULL AUTO_INCREMENT,
    first_number_cheque VARCHAR(7),
    prev_number_cheque VARCHAR(7),
    last_number_cheque VARCHAR(7),
    CONSTRAINT pk_cheque_account PRIMARY KEY (id_cheque),
    CONSTRAINT chk_cheque_account_first_number_cheque CHECK(first_number_cheque < last_number_cheque),
    CONSTRAINT chk_cheque_account_prev_number_cheque CHECK(prev_number_cheque BETWEEN first_number_cheque AND last_number_cheque),
    CONSTRAINT chk_cheque_account_last_number_cheque CHECK(last_number_cheque > first_number_cheque)
)ENGINE = InnoDB;

/* Table paiement */
CREATE TABLE IF NOT EXISTS payment_account
(
    id_payment INT NOT NULL AUTO_INCREMENT,
    name_payment VARCHAR(20) NOT NULL,
    id_cheque INT NOT NULL,
    CONSTRAINT pk_payment_account PRIMARY KEY (id_payment),
    CONSTRAINT fk_payment_account_id_cheque FOREIGN KEY (id_cheque) REFERENCES cheque_account(id_cheque)
)ENGINE = InnoDB;

/* Table type de dépense */
CREATE TABLE IF NOT EXISTS type_expend_account
(
    id_type_expend INT NOT NULL AUTO_INCREMENT,
    name_type_expend VARCHAR(20) NOT NULL,
    CONSTRAINT pk_type_expend_account PRIMARY KEY (id_type_expend)
)ENGINE = InnoDB;

/* Table pour calculer le solde réel et le solde anticipé */
/* Table dépense */
CREATE TABLE IF NOT EXISTS expend_account
(
    id_expend INT NOT NULL AUTO_INCREMENT,
    amount FLOAT NOT NULL,
    desc_expend VARCHAR(20),
    date_expend DATE NOT NULL,
    id_payment INT NOT NULL,
    id_account INT NOT NULL,
    id_type_expend INT NOT NULL,
    anticipate BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_expend_account PRIMARY KEY (id_expend),
    CONSTRAINT fk_expend_account_id_account FOREIGN KEY (id_account) REFERENCES bank_account(id_account),
    CONSTRAINT fk_expend_account_id_type_expend FOREIGN KEY (id_type_expend) REFERENCES type_expend_account(id_type_expend)
)ENGINE = InnoDB;

/* Table revenu */
CREATE TABLE IF NOT EXISTS income_account
(
    id_income INT NOT NULL AUTO_INCREMENT,
    amount FLOAT NOT NULL,
    desc_income VARCHAR(20),
    date_income DATE NOT NULL,
    id_payment INT NOT NULL,
    id_account INT NOT NULL,
    anticipate BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_income_account PRIMARY KEY (id_income),
    CONSTRAINT fk_income_account_id_account FOREIGN KEY (id_account) REFERENCES bank_account(id_account)
)ENGINE = InnoDB;