| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556 |
- -- Schéma dirCops — suivi facturation / échéancier / paiement
- -- MariaDB/MySQL
- -- Factures : type_produit (alevins, larves), cycle (24-25, 25-26), qte_livree
- SET NAMES utf8mb4;
- CREATE TABLE IF NOT EXISTS clients (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- nom VARCHAR(255) NOT NULL,
- created_at DATETIME DEFAULT CURRENT_TIMESTAMP
- );
- CREATE TABLE IF NOT EXISTS factures (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- client_id INT UNSIGNED NOT NULL,
- n_facture VARCHAR(64) NOT NULL,
- n_vente VARCHAR(64) DEFAULT NULL,
- date_facture DATE NOT NULL,
- type_produit VARCHAR(32) DEFAULT NULL COMMENT 'alevins, larves',
- cycle VARCHAR(16) DEFAULT NULL COMMENT 'ex: 24-25, 25-26',
- quantites DECIMAL(12,2) DEFAULT NULL,
- qte_livree DECIMAL(12,2) DEFAULT NULL,
- montant_ht DECIMAL(12,2) NOT NULL,
- created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
- CONSTRAINT fk_factures_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
- );
- CREATE TABLE IF NOT EXISTS paiements (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- facture_id INT UNSIGNED NOT NULL,
- montant DECIMAL(12,2) NOT NULL,
- date_paiement DATE NOT NULL,
- created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
- CONSTRAINT fk_paiements_facture FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE,
- CONSTRAINT chk_paiements_montant CHECK (montant > 0)
- );
- CREATE TABLE IF NOT EXISTS echeanciers (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- facture_id INT UNSIGNED NOT NULL,
- nb_echeances INT UNSIGNED NOT NULL,
- date_debut_echeancier DATE NOT NULL,
- montant_par_echeance DECIMAL(12,2) NOT NULL COMMENT 'Montant calculé au moment de la création (encours / nb_echeances)',
- created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
- CONSTRAINT fk_echeanciers_facture FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE,
- CONSTRAINT chk_echeanciers_nb_echeances CHECK (nb_echeances > 0),
- CONSTRAINT chk_echeanciers_montant CHECK (montant_par_echeance > 0),
- UNIQUE KEY uk_echeanciers_facture_id (facture_id) COMMENT 'Une facture = un écheancier maximum'
- );
- CREATE INDEX idx_factures_client_id ON factures(client_id);
- CREATE INDEX idx_factures_type_produit ON factures(type_produit);
- CREATE INDEX idx_factures_cycle ON factures(cycle);
- CREATE INDEX idx_paiements_facture_id ON paiements(facture_id);
- CREATE INDEX idx_paiements_date ON paiements(date_paiement);
- CREATE INDEX idx_echeanciers_facture_id ON echeanciers(facture_id);
|