COM_BRANDS

Tabella anagrafica marchi.

Schema

CREATE TABLE `COM_BRANDS` (
  `ID_BRAND` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ID_COMPANY` INT UNSIGNED NOT NULL DEFAULT '0',
  `C_BRAND` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `D_BRAND` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `ORDERING` TINYINT NOT NULL DEFAULT '0',
  `CREATED_BY` INT UNSIGNED NULL,
  `CREATED_AT` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `STATUS` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `DELETED_BY` INT UNSIGNED NULL,
  `DELETED_AT` DATETIME NULL,
  `RESTORED_BY` INT UNSIGNED NULL,
  `RESTORED_AT` DATETIME NULL,
  PRIMARY KEY (`ID_BRAND`),
  KEY `K_C_BRAND` (`C_BRAND`),
  KEY `K_COMP_BRAND` (`ID_COMPANY`, `C_BRAND`),
  KEY `K_D_BRAND` (`D_BRAND`),
  KEY `IDX_STATUS` (`STATUS`),
  KEY `IDX_COMPANY_STATUS` (`ID_COMPANY`, `STATUS`),
  KEY `IDX_DELETED_AT` (`DELETED_AT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Campi

Campo	Tipo	Nullable	Default	Descrizione
ID_BRAND	INT UNSIGNED	No	AUTO_INCREMENT	Chiave primaria
ID_COMPANY	INT UNSIGNED	No	0	ID azienda (multi-tenancy)
C_BRAND	VARCHAR(50)	Si	NULL	Codice marchio
D_BRAND	VARCHAR(255)	Si	NULL	Descrizione marchio
ORDERING	TINYINT	No	0	Deprecato - Non utilizzato
CREATED_BY	INT UNSIGNED	Si	NULL	ID utente creatore
CREATED_AT	DATETIME	Si	CURRENT_TIMESTAMP	Data creazione
STATUS	TINYINT UNSIGNED	No	1	1=ACTIVE, 0=DELETED
DELETED_BY	INT UNSIGNED	Si	NULL	ID utente eliminazione
DELETED_AT	DATETIME	Si	NULL	Data eliminazione
RESTORED_BY	INT UNSIGNED	Si	NULL	ID utente ripristino
RESTORED_AT	DATETIME	Si	NULL	Data ripristino

Indici

Nome	Campi	Scopo
PRIMARY	ID_BRAND	Chiave primaria
K_C_BRAND	C_BRAND	Ricerca per codice
K_COMP_BRAND	ID_COMPANY, C_BRAND	Unicita codice per azienda
K_D_BRAND	D_BRAND	Ricerca per descrizione
IDX_STATUS	STATUS	Filtro stato
IDX_COMPANY_STATUS	ID_COMPANY, STATUS	Lista marchi attivi per azienda
IDX_DELETED_AT	DELETED_AT	Query su eliminati

Valori STATUS

Valore	Costante	Descrizione
1	ACTIVE	Marchio attivo e visibile
0	DELETED	Marchio eliminato (soft delete)

Relazioni

Foreign Keys Logiche

Tabella	Campo	Relazione
COM_COMPANIES	ID_COMPANY	Azienda proprietaria
AUTH_USER	CREATED_BY	Utente creatore
AUTH_USER	DELETED_BY	Utente eliminazione
AUTH_USER	RESTORED_BY	Utente ripristino

Tabelle Dipendenti

Tabella	Campo	Descrizione
COM_ARTICLES	ID_BRAND	Articoli del marchio
COM_CONDITIONS	ID_BRAND	Condizioni commerciali
PRO_CONDITIONS	ID_BRAND	Condizioni promozionali
TRA_BRANDS	C_BRAND	Transcodifica marchi
EXP_FILTER_BRANDS	ID_BRAND	Filtri export
ECO_EXP_FILTER_BRANDS	ID_BRAND	Filtri export e-commerce

Query Comuni

Lista marchi attivi

SELECT ID_BRAND, C_BRAND, D_BRAND
FROM COM_BRANDS
WHERE ID_COMPANY = :company_id
  AND STATUS = 1
ORDER BY D_BRAND;

Verifica unicita codice

SELECT ID_BRAND
FROM COM_BRANDS
WHERE ID_COMPANY = :company_id
  AND C_BRAND = :code
  AND STATUS = 1
  AND ID_BRAND != :exclude_id;

Join con articoli

SELECT a.*, b.D_BRAND AS brand_name
FROM COM_ARTICLES a
JOIN COM_BRANDS b ON a.ID_BRAND = b.ID_BRAND AND b.STATUS = 1
WHERE a.ID_COMPANY = :company_id;