schema.sql 2.5 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556
  1. -- Schéma POC suivi facturation / échéancier / paiement (EMGI)
  2. -- MariaDB/MySQL
  3. -- Factures : type_produit (alevins, larves), cycle (24-25, 25-26), qte_livree
  4. SET NAMES utf8mb4;
  5. CREATE TABLE IF NOT EXISTS clients (
  6. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  7. nom VARCHAR(255) NOT NULL,
  8. created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  9. );
  10. CREATE TABLE IF NOT EXISTS factures (
  11. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  12. client_id INT UNSIGNED NOT NULL,
  13. n_facture VARCHAR(64) NOT NULL,
  14. n_vente VARCHAR(64) DEFAULT NULL,
  15. date_facture DATE NOT NULL,
  16. type_produit VARCHAR(32) DEFAULT NULL COMMENT 'alevins, larves',
  17. cycle VARCHAR(16) DEFAULT NULL COMMENT 'ex: 24-25, 25-26',
  18. quantites DECIMAL(12,2) DEFAULT NULL,
  19. qte_livree DECIMAL(12,2) DEFAULT NULL,
  20. montant_ht DECIMAL(12,2) NOT NULL,
  21. created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  22. CONSTRAINT fk_factures_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
  23. );
  24. CREATE TABLE IF NOT EXISTS paiements (
  25. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  26. facture_id INT UNSIGNED NOT NULL,
  27. montant DECIMAL(12,2) NOT NULL,
  28. date_paiement DATE NOT NULL,
  29. created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  30. CONSTRAINT fk_paiements_facture FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE,
  31. CONSTRAINT chk_paiements_montant CHECK (montant > 0)
  32. );
  33. CREATE TABLE IF NOT EXISTS echeanciers (
  34. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  35. facture_id INT UNSIGNED NOT NULL,
  36. nb_echeances INT UNSIGNED NOT NULL,
  37. date_debut_echeancier DATE NOT NULL,
  38. montant_par_echeance DECIMAL(12,2) NOT NULL COMMENT 'Montant calculé au moment de la création (encours / nb_echeances)',
  39. created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  40. CONSTRAINT fk_echeanciers_facture FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE,
  41. CONSTRAINT chk_echeanciers_nb_echeances CHECK (nb_echeances > 0),
  42. CONSTRAINT chk_echeanciers_montant CHECK (montant_par_echeance > 0),
  43. UNIQUE KEY uk_echeanciers_facture_id (facture_id) COMMENT 'Une facture = un écheancier maximum'
  44. );
  45. CREATE INDEX idx_factures_client_id ON factures(client_id);
  46. CREATE INDEX idx_factures_type_produit ON factures(type_produit);
  47. CREATE INDEX idx_factures_cycle ON factures(cycle);
  48. CREATE INDEX idx_paiements_facture_id ON paiements(facture_id);
  49. CREATE INDEX idx_paiements_date ON paiements(date_paiement);
  50. CREATE INDEX idx_echeanciers_facture_id ON echeanciers(facture_id);