Quintilien - Structure de la base de données (2026)

Note : les tables désormais sont liées au moyen de foreign keys basées sur les identifiants (id INTEGER NOT NULL UNIQUE) et non plus sur les références alphanumériques uniques, comme c'était le cas dans les précédentes versions.

Cette nouvelle structure permet de modifier, si on le souhaite, les références alphanumériques puisqu'elles n'interviennent plus dans les liens entre les enregistrements.

CREATE TABLE "t_who" (
    "who_id" INTEGER NOT NULL UNIQUE,
    "who_ref" TEXT NOT NULL UNIQUE,
    "who_name" TEXT DEFAULT '',
    "who_lang" INTEGER DEFAULT 1,
    "who_ext" TEXT DEFAULT '',
    "who_price" REAL DEFAULT 0.0,
    "who_pswd" TEXT DEFAULT '',
    "who_admin" INTEGER DEFAULT 0,
    "who_sleep" INTEGER DEFAULT 0,
    "who_mail" TEXT DEFAULT '',
    "who_2fa" INTEGER DEFAULT 0,
    "who_note" TEXT DEFAULT '',
    "who_2fa_secret" TEXT DEFAULT NULL,
    PRIMARY KEY("who_id" AUTOINCREMENT)
);

INSERT INTO t_who (who_ref,who_name,who_admin,who_pswd)
VALUES('ADMIN', 'Admin', 1, '');

CREATE TABLE "t_customer" (
    "cust_id" INTEGER NOT NULL UNIQUE,
    "cust_name1" TEXT DEFAULT '',
    "cust_name2" TEXT DEFAULT '',
    "cust_short" TEXT DEFAULT '',
    "cust_cat" TEXT DEFAULT '',
    "cust_lang" TEXT DEFAULT '',
    "cust_vatnum" TEXT DEFAULT '',
    "cust_date" TEXT DEFAULT '',
    "cust_sleep" INTEGER DEFAULT 0,
    "cust_adr1" TEXT DEFAULT '',
    "cust_adr2" TEXT DEFAULT '',
    "cust_country" TEXT DEFAULT '',
    "cust_postcode" TEXT DEFAULT '',
    "cust_city" TEXT DEFAULT '',
    "cust_adrmail" TEXT DEFAULT '',
    "cust_phone1" TEXT DEFAULT '',
    "cust_phone2" TEXT DEFAULT '',
    "cust_notes" TEXT DEFAULT '',
    PRIMARY KEY("cust_id" AUTOINCREMENT)
);

CREATE TABLE "t_why" (
    "why_id" INTEGER NOT NULL UNIQUE,
    "why_ref" TEXT NOT NULL UNIQUE,
    "why_date" TEXT DEFAULT '',
    "why_fk_cust_id" INTEGER,
    "why_descr" TEXT DEFAULT '',
    "why_sleep" INTEGER DEFAULT 0,
    "why_notes" TEXT DEFAULT '',
    PRIMARY KEY("why_id" AUTOINCREMENT),
    FOREIGN KEY("why_fk_cust_id") REFERENCES "t_customer"("cust_id")
);

CREATE TABLE "t_unit" (
    "unit_id" INTEGER NOT NULL UNIQUE,
    "unit_ref" TEXT NOT NULL UNIQUE,
    "unit_descr" TEXT DEFAULT '',
    "unit_sleep" INTEGER DEFAULT 0,
    PRIMARY KEY("unit_id" AUTOINCREMENT)
);

CREATE TABLE "t_what" (
    "what_id" INTEGER NOT NULL UNIQUE,
    "what_short" TEXT DEFAULT '',
    "what_descr" TEXT DEFAULT '',
    "what_fk_unit_id" INTEGER,
    "what_price" REAL DEFAULT 0.0,
    "what_notes" TEXT DEFAULT '',
    "what_sleep" INTEGER DEFAULT 0,
    PRIMARY KEY("what_id" AUTOINCREMENT),
    FOREIGN KEY("what_fk_unit_id") REFERENCES "t_unit"("unit_id")
);

CREATE TABLE "t_who_when_what" (
    "w_id" INTEGER NOT NULL UNIQUE,
    "w_fk_who_id" INTEGER,
    "w_when" TEXT DEFAULT '',
    "w_fk_why_id" INTEGER,
    "w_fk_what_id" INTEGER,
    "w_descr" TEXT DEFAULT '',
    "w_qty" REAL DEFAULT 0.0,
    "w_sleep" INTEGER DEFAULT 0,
    PRIMARY KEY("w_id" AUTOINCREMENT),
    FOREIGN KEY("w_fk_what_id") REFERENCES "t_what"("what_id"),
    FOREIGN KEY("w_fk_who_id") REFERENCES "t_who"("who_id"),
    FOREIGN KEY("w_fk_why_id") REFERENCES "t_why"("why_id")
);



/*
          V I E W S
*/



CREATE VIEW v_why AS
SELECT 
  t_why.why_id,
  t_why.why_ref,
  t_why.why_date,
  t_why.why_fk_cust_id,
  CASE
    WHEN t_customer.cust_short = '' 
      THEN t_customer.cust_name1
    ELSE t_customer.cust_short || '  |  ' || t_customer.cust_name1
  END AS cust_short,
  t_why.why_descr,
  t_why.why_sleep,
  t_why.why_notes
FROM t_why
INNER JOIN t_customer ON t_why.why_fk_cust_id = t_customer.cust_id
ORDER BY t_why.why_ref;

;

CREATE VIEW v_what AS SELECT
	"what_id" , 
	"what_short" ,
	"what_descr" ,
	"what_fk_unit_id" ,
	"unit_ref" , 
	"unit_descr" , 
	"what_price" ,
	"what_notes"  , 
	"what_sleep" 
FROM t_what
INNER JOIN t_unit ON unit_id = 	"what_fk_unit_id"

;
CREATE VIEW v_who_when_what AS
SELECT
    -- clé de la prestation
    t_who_when_what.w_id,
    t_who_when_what.w_when,

    -- client affiché court
    CASE
        WHEN t_customer.cust_short = ''
            THEN t_customer.cust_name1
        ELSE t_customer.cust_short || '  |  ' || t_customer.cust_name1
    END AS cust_short,

    -- affichage long dossier (client + dossier)
    CASE
        WHEN t_customer.cust_short = ''
            THEN t_customer.cust_name1 || '  |  ' || t_why.why_ref || '  |  ' || IFNULL(t_why.why_descr, '')
        ELSE t_customer.cust_short || '  |  ' || t_customer.cust_name1 || '  |  ' || t_why.why_ref || '  |  ' || IFNULL(t_why.why_descr, '')
    END AS why_short,

    -- infos dossier
    t_why.why_id,
    t_why.why_ref,
    t_why.why_descr,

    -- infos prestation
    t_what.what_id,
    CASE
        WHEN t_what.what_short = ''
            THEN t_what.what_descr
        ELSE t_what.what_short || '  |  ' || t_what.what_descr || ' (' || t_unit.unit_descr || ')'
    END AS what_short,
    t_what.what_short AS what_short_raw,
    t_what.what_descr AS what_descr,

    -- FK prestataire
    t_who_when_what.w_fk_who_id,

    -- infos prestataire
    t_who.who_id,
    CASE
        WHEN t_who.who_ref = ''
            THEN t_who.who_name
        ELSE t_who.who_ref || '  |  ' || t_who.who_name
    END AS who_short,
    t_who.who_ref,
    t_who.who_name,

    -- quantité formatée
    printf('%10.2f', ROUND(t_who_when_what.w_qty, 2)) AS qty,

    -- unité
    t_unit.unit_ref,
    t_unit.unit_descr,

    -- remarque
    t_who_when_what.w_descr

FROM t_customer
JOIN t_why
    ON t_why.why_fk_cust_id = t_customer.cust_id
JOIN t_who_when_what
    ON t_who_when_what.w_fk_why_id = t_why.why_id
JOIN t_what
    ON t_who_when_what.w_fk_what_id = t_what.what_id
JOIN t_unit
    ON t_what.what_fk_unit_id = t_unit.unit_id
JOIN t_who
    ON t_who_when_what.w_fk_who_id = t_who.who_id

ORDER BY
    t_who_when_what.w_when DESC,
    t_customer.cust_short || '  |  ' || t_customer.cust_name1 || '  |  ' || t_why.why_ref || '  |  ' || IFNULL(t_why.why_descr, '');