[SQL] References su un sottoinsieme

[Donbabbeo]

Sto creando un database di un magazzino e sono all'ennesima revisione.
Sto in questo momento testando la funzionalità dei vincoli ma ho un dubbio su come aggiungere più vincoli sullo stesso campo.
Mi spiego meglio:

Sto facendo le prove su una versione semplificata del database solo per vedere se funzionano. Attualmente ho 3 tabelle:
una dei dipendenti(ID, Nome, Tipo [Fattorino/Magazziniere]),
una degli ordini(ID, Nome [Non ha senso lo so, ma non sapevo che metterci]) ,
una che mette in connessione le due tabelle qui sopra (id_fattorino, id_ordine)

Ovviamente SOLO i fattorini potranno far parte del possibile gruppo di dipendenti da inserire in consegna.

Il codice di creazione della tabella fatto finora è questo:

CREATE TABLE consegna (
	id_ordine INT(3) NOT NULL,
	id_fattorino INT(3) DEFAULT '0',
PRIMARY KEY (id_ordine),
CONSTRAINT OrdCon FOREIGN KEY (id_ordine) REFERENCES ordine(id_ordine),
CONSTRAINT FatCon FOREIGN KEY (id_fattorino) REFERENCES dipendente(id_dipendente)
) ENGINE=InnoDB;

Dovrei in pratica modificare il secondo CONSTRAINT in modo che l'id_fattorino sia preso non tra tutto l'elenco degli id_dipendente, ma solo tra l'elenco degli id_dipendente il cui campo Tipo sia 'Fattorino'.

Come posso modificare la tabella per inserire questo controllo

[Donbabbeo]

E' un paio d'ore che ci provo, al principio ho pensato di provare tramite un trigger (se l'id dipendente inserita non è di un fattorino segnala un errore), per poi scoprire che mysql non permette trigger legati a foreign keys...

Ho quindi deciso di associare la tabella dipendenti a 2 tabelle FATTORINI e MAGAZZINIERI in cui vengono immagazzinati gli id dei dipendenti di quel tipo.

A questo punto le REFERENCES della tabella consegna invece di indirizzarle all'id_dipendente di dipendenti, le indirizzo all'id_fattorino/magazziniere di FATTORINI/MAGAZZINIERI.

A questo punto: per aggiungere automaticamente i nuovi dipendenti in uno dei 2 gruppi a seconda del tipo di mansione serve necessariamente un trigger?

[gugoXX]

Nono, niente trigger, lascia perdere per favore.
Se vuoi modellare questa relazione sul database, ti consiglio di utilizzare il pattern is-a dell'SQL. (Ovvero quando si deve mappare la inheritance dei linguaggi ad oggetti in rispettive tabelle SQL)

Nel tuo caso la superclasse delle persone avrebbe tutto cio' che tutte le persone hanno
persone(ID, Nome)

poi una classe sottotipo dei fattorini, con l'identificativo e tutte le eventuali caratteristiche che i fattorini hanno (se ce ne sono)

fattorini(ID, LuogoDiPartenza, NumeroDiCartaDelSindacatoFattorini)

e poi una classe sottotipo per i magazzinieri, ciascuno con le caratteristiche che i soli magazzinieri hanno (se ce ne sono)

magazzinieri(ID, Magazzino)

tutte e 3 queste tabelle hanno solo ID come chiave primaria.
sia fattorini che magazzinieri hanno la loro ID in ForeignKey verso Persone

a questo punto ti risultera' facile forzare gli ORDINI ad avere come ID un ID solo di un fattorino, facendo puntare la foreign key verso la tabella sottotipo "magazzinieri", e non verso quella globale "persone"

Spero sia chiaro. Con un solo disegno sarebbe oltremodo banale...

Il tipo di persone non dovrebbe servirti mapparlo sul database. E' intrinseco dalla presenza della persona in una o nell'altra tabella (o entrambe?)

[Donbabbeo]

Originariamente inviato da gugoXX

Nono, niente trigger, lascia perdere per favore.
Se vuoi modellare questa relazione sul database, ti consiglio di utilizzare il pattern is-a dell'SQL. (Ovvero quando si deve mappare la inheritance dei linguaggi ad oggetti in rispettive tabelle SQL)

Nel tuo caso la superclasse delle persone avrebbe tutto cio' che tutte le persone hanno
persone(ID, Nome)

poi una classe sottotipo dei fattorini, con l'identificativo e tutte le eventuali caratteristiche che i fattorini hanno (se ce ne sono)

fattorini(ID, LuogoDiPartenza, NumeroDiCartaDelSindacatoFattorini)

e poi una classe sottotipo per i magazzinieri, ciascuno con le caratteristiche che i soli magazzinieri hanno (se ce ne sono)

magazzinieri(ID, Magazzino)

tutte e 3 queste tabelle hanno solo ID come chiave primaria.
sia fattorini che magazzinieri hanno la loro ID in ForeignKey verso Persone

a questo punto ti risultera' facile forzare gli ORDINI ad avere come ID un ID solo di un fattorino, facendo puntare la foreign key verso la tabella sottotipo "magazzinieri", e non verso quella globale "persone"

Spero sia chiaro. Con un solo disegno sarebbe oltremodo banale...

Il tipo di persone non dovrebbe servirti mapparlo sul database. E' intrinseco dalla presenza della persona in una o nell'altra tabella (o entrambe?)

Esattamente quello che ho fatto. Il discorso era rendere automatico l'aggiunta del dipendente alle tabelle MAGAZZINIERE e FATTORINO.
Cioè io aggiungo un nuovo dipendente (INSERT INTO dipendenti blah blah blah) a questo punto a seconda del tipo devo aggiungerlo manualmente alla tabella magazziniere o fattorino dico bene?

Perchè mi sconsigli l'uso dei trigger? E' in questo caso particolare che sono superflui o non devo proprio usarli? In alcuni punti potrei averne bisogno mi sa...

[gugoXX]

Originariamente inviato da Donbabbeo

Esattamente quello che ho fatto. Il discorso era rendere automatico l'aggiunta del dipendente alle tabelle MAGAZZINIERE e FATTORINO.
Cioè io aggiungo un nuovo dipendente (INSERT INTO dipendenti blah blah blah) a questo punto a seconda del tipo devo aggiungerlo manualmente alla tabella magazziniere o fattorino dico bene?

Perchè mi sconsigli l'uso dei trigger? E' in questo caso particolare che sono superflui o non devo proprio usarli? In alcuni punti potrei averne bisogno mi sa...

I trigger sono sconsigliati in genere solo per questioni di controllo durante le fasi di manutenzione (ma talvolta fanno casino anche durante le fasi di sviluppo)
La maggior parte delle volte che sono stati usati se ne poteva fare a meno con un buon disegno o usando altre features (check constraint etc.)

Nel tuo caso specifico, devi chiederti chi e cosa eseguira' la insert e a fronte di cosa.
Se fosse un linguaggio ad alto livello come penso, e' possibile ovviamente eseguire le 2 insert ora necessarie, per ciascun tipo di dipendente, in modo custom.
Addirittura e' possibile che il linguaggio ad alto livello abbia 2 diverse pagine per le anagrafiche di fattorini oppure di magazzinieri (tieni conto che potrebbero facilmente avrere campi diversi fra loro)
Oppure alla peggio, se proprio volessi avere un unico modo centralizzato per creare una persona indipendentemente dal suo tipo (ma quindi sarebbe proprio nell'improbabile caso che fattorini e magazzinieri avessero proprio gli stessi identici campi), al posto di strani meccanismi con trigger userei una semplice unica stored procedure.