82 lines
No EOL
3.5 KiB
SQL
82 lines
No EOL
3.5 KiB
SQL
-- WARNING: This schema is for context only and is not meant to be run.
|
|
-- Table order and constraints may not be valid for execution.
|
|
|
|
CREATE TABLE public.clienti (
|
|
id_cliente integer NOT NULL DEFAULT nextval('clienti_id_cliente_seq'::regclass),
|
|
nome_cliente character varying NOT NULL UNIQUE,
|
|
CONSTRAINT clienti_pkey PRIMARY KEY (id_cliente)
|
|
);
|
|
CREATE TABLE public.compilatori (
|
|
id_compilatore integer NOT NULL DEFAULT nextval('compilatori_id_compilatore_seq'::regclass),
|
|
nome_compilatore character varying NOT NULL UNIQUE,
|
|
CONSTRAINT compilatori_pkey PRIMARY KEY (id_compilatore)
|
|
);
|
|
CREATE TABLE public.inci (
|
|
id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
inci text NOT NULL UNIQUE,
|
|
cas text NOT NULL,
|
|
CONSTRAINT inci_pkey PRIMARY KEY (id),
|
|
CONSTRAINT inci_cas_fkey FOREIGN KEY (cas) REFERENCES public.ingredienti(cas)
|
|
);
|
|
CREATE TABLE public.ingredienti (
|
|
id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
cas text NOT NULL UNIQUE,
|
|
mongo_id text,
|
|
dap boolean DEFAULT false,
|
|
cosing boolean DEFAULT false,
|
|
tox boolean DEFAULT false,
|
|
CONSTRAINT ingredienti_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.ingredients_lineage (
|
|
id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
id_progetto integer,
|
|
id_ingrediente bigint,
|
|
CONSTRAINT ingredients_lineage_pkey PRIMARY KEY (id),
|
|
CONSTRAINT ingredients_lineage_id_ingrediente_fkey FOREIGN KEY (id_ingrediente) REFERENCES public.ingredienti(id),
|
|
CONSTRAINT ingredients_lineage_id_progetto_fkey FOREIGN KEY (id_progetto) REFERENCES public.progetti(id)
|
|
);
|
|
CREATE TABLE public.ordini (
|
|
id_ordine integer NOT NULL DEFAULT nextval('ordini_id_ordine_seq'::regclass),
|
|
id_cliente integer,
|
|
id_compilatore integer,
|
|
uuid_ordine character varying NOT NULL,
|
|
uuid_progetto character varying UNIQUE,
|
|
data_ordine timestamp without time zone NOT NULL,
|
|
stato_ordine integer DEFAULT 1,
|
|
note text,
|
|
CONSTRAINT ordini_pkey PRIMARY KEY (id_ordine),
|
|
CONSTRAINT ordini_id_cliente_fkey FOREIGN KEY (id_cliente) REFERENCES public.clienti(id_cliente),
|
|
CONSTRAINT ordini_id_compilatore_fkey FOREIGN KEY (id_compilatore) REFERENCES public.compilatori(id_compilatore),
|
|
CONSTRAINT ordini_stato_ordine_fkey FOREIGN KEY (stato_ordine) REFERENCES public.stati_ordini(id_stato)
|
|
);
|
|
CREATE TABLE public.progetti (
|
|
id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
mongo_id character varying,
|
|
preset_tipo_prodotto integer,
|
|
CONSTRAINT progetti_pkey PRIMARY KEY (id),
|
|
CONSTRAINT progetti_mongo_id_fkey FOREIGN KEY (mongo_id) REFERENCES public.ordini(uuid_progetto),
|
|
CONSTRAINT progetti_preset_tipo_prodotto_fkey FOREIGN KEY (preset_tipo_prodotto) REFERENCES public.tipi_prodotti(id_preset)
|
|
);
|
|
CREATE TABLE public.stati_ordini (
|
|
id_stato integer NOT NULL DEFAULT nextval('stati_ordini_id_stato_seq'::regclass),
|
|
nome_stato character varying NOT NULL,
|
|
CONSTRAINT stati_ordini_pkey PRIMARY KEY (id_stato)
|
|
);
|
|
CREATE TABLE public.tipi_prodotti (
|
|
id_preset integer NOT NULL DEFAULT nextval('tipi_prodotti_id_tipo_seq'::regclass),
|
|
preset_name text NOT NULL UNIQUE,
|
|
tipo_prodotto text,
|
|
luogo_applicazione text,
|
|
esp_normali text,
|
|
esp_secondarie text,
|
|
esp_nano text NOT NULL,
|
|
sup_esposta integer,
|
|
freq_applicazione integer,
|
|
qta_giornaliera double precision,
|
|
ritenzione double precision,
|
|
CONSTRAINT tipi_prodotti_pkey PRIMARY KEY (id_preset)
|
|
); |