[SQL]Chiavi Primarie ed Esterne

[RaouL_BennetH]

Ciao a tutti

Considerando queste entità:

Persona
(id, nome, cognome)


Dipendente : Persona
(ruolo, personaId, indirizzoId)

Indirizzi
(id_indirizzo, domicilio, comune, provincia, cap)


Ho definito le due tabelle 'principali'

CREATE TABLE Persone(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Nome varchar(25),
Cognome varchar(25));

CREATE TABLE Indirizzi(id_indirizzo INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
domicilio varchar(100),
comune varchar(80),
provincia char(2),
cap varchar(5));

Vorrei capire:

1) Nella tabella dipendente, è necessaria una chiave primaria?
2) Nella tabella dipendente, le chiavi esterne sono relative alle chiavi primarie di Indirizzi e Persone, posso mettere due chiavi esterne sulla stessa tabella?
3) C'è modo per impedire anche per il più incredibile dei casi, che vengano inseriti nella tabella dipendente degli id relativi a persone o indirizzi che non esistono nelle due tabelle?

Grazie mille per i chiarimenti

RaouL.

[Trigger2009]

Originariamente inviato da RaouL_BennetH

Ciao a tutti

Considerando queste entità:

Persona
(id, nome, cognome)


Dipendente : Persona
(ruolo, personaId, indirizzoId)

Indirizzi
(id_indirizzo, domicilio, comune, provincia, cap)


Ho definito le due tabelle 'principali'

CREATE TABLE Persone(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Nome varchar(25),
Cognome varchar(25));

CREATE TABLE Indirizzi(id_indirizzo INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
domicilio varchar(100),
comune varchar(80),
provincia char(2),
cap varchar(5));

Vorrei capire:

1) Nella tabella dipendente, è necessaria una chiave primaria?
2) Nella tabella dipendente, le chiavi esterne sono relative alle chiavi primarie di Indirizzi e Persone, posso mettere due chiavi esterne sulla stessa tabella?
3) C'è modo per impedire anche per il più incredibile dei casi, che vengano inseriti nella tabella dipendente degli id relativi a persone o indirizzi che non esistono nelle due tabelle?

Grazie mille per i chiarimenti

RaouL.

mmm credo proprio che in MySql sia impossibile creare una tabella senza chiave primaria...per poter accedere ai record...anche se non vorrei sbagliarmi...

Comunque fatto stà che a livello logico devi sempre mettere una chiave primaria univoca, nella tabella Dipendenti metterei personaId che identifica univocamente un dipendente.
Mentre in indirizzi metterei Id_Indirizzo che identifica univocamente un indirizzo.

Considera che se hai una tabella che logicamente non contiene dati per forza univoci si mette un campo auto_increment per avere una chiave primaria o si usano più campi della tabella che messi insieme sono univoci...

2 e 3) Si se crei le tabelle Dipendente imponendo il vincolo di integrità referenziale ai campi delle altre due tabelle...devi usare i references nella creazione della tabella Dipendenti che il campo IdDipendente fa riferimento al campo ID di persona ed il campo IdIndirizzo fa riferimento al campo IdIndirizzo di indirizzo

Così se ad esempio provi ad inserire un dipendente con un indirizzo che non esiste dentro la tabella indirizzi ti da un errore e non te lo fa inserire preservando i vincoli di integrità referenziale dei tuoi dati.

Questo se usi tabelle InnoDB, con MyISAM non dovresti poterlo fare e te lo dovresti fare a mano con i trigger ma sarebbe una caciara...quindi usa InnoDB.

Ciao
Andrea

[RaouL_BennetH]

Originariamente inviato da Trigger2009

mmm credo proprio che in MySql sia impossibile creare una tabella senza chiave primaria...per poter accedere ai record...anche se non vorrei sbagliarmi...

Comunque fatto stà che a livello logico devi sempre mettere una chiave primaria univoca, nella tabella Dipendenti metterei personaId che identifica univocamente un dipendente.
Mentre in indirizzi metterei Id_Indirizzo che identifica univocamente un indirizzo.

Considera che se hai una tabella che logicamente non contiene dati per forza univoci si mette un campo auto_increment per avere una chiave primaria o si usano più campi della tabella che messi insieme sono univoci...

2 e 3) Si se crei le tabelle Dipendente imponendo il vincolo di integrità referenziale ai campi delle altre due tabelle...devi usare i references nella creazione della tabella Dipendenti che il campo IdDipendente fa riferimento al campo ID di persona ed il campo IdIndirizzo fa riferimento al campo IdIndirizzo di indirizzo

Così se ad esempio provi ad inserire un dipendente con un indirizzo che non esiste dentro la tabella indirizzi ti da un errore e non te lo fa inserire preservando i vincoli di integrità referenziale dei tuoi dati.

Questo se usi tabelle InnoDB, con MyISAM non dovresti poterlo fare e te lo dovresti fare a mano con i trigger ma sarebbe una caciara...quindi usa InnoDB.

Ciao
Andrea

Ciao e grazie per la risposta

Vorrei solo precisare che non utilizzo MySql come base dati.

I 'motori' che ho a disposizione sono PostgreSql e, forse (ci spero vivamente), Sql Server (non nella versione express).

Ho messo [SQL] nel titolo perchè mi interessa capire come funziona il tutto in qualsiasi engine che sia standard.

Grazie

RaouL.

[Trigger2009]

Originariamente inviato da RaouL_BennetH

Ciao e grazie per la risposta

Vorrei solo precisare che non utilizzo MySql come base dati.

I 'motori' che ho a disposizione sono PostgreSql e, forse (ci spero vivamente), Sql Server (non nella versione express).

Ho messo [SQL] nel titolo perchè mi interessa capire come funziona il tutto in qualsiasi engine che sia standard.

Grazie

RaouL.

Aspetta stai facendo confusione !!!
PostgreSQL e SQL Server NON SONO MOTORI, sono DBMS (ovvero i software che servono a creare e gestire le basi di dati) così come MySql.

Io ti parlo di tipi di tabelle: con questi DBMS tu puoi gestire tabelle realizzate con motori diversi che sono appunto InnoDB, MyIsam, Falcon e tantissimi altri...

In pratica ogni motore ti rappresenta in memoria le tabelle in modo diverso ed ognuno di essi ha dei pro e dei contro (apparte alcuni vecchissimi che non si usano più)...in fin dei conti quelli usati frequentemente sono InnoDB e MyIsam...vedila così: puoi avere due tipi di tabelle diverse:

Le tabelle InnoDB ti garantiscono la possibilità di avare vincoli di integrità referenziale come ti ho appena spiegato

Le tabelle MyIsam sono più veloci a fare certe operazioni e puoi creare indici fulltext per ricerche testuali molto potenti ma hanno il grosso handicap di non garantire l'integrità referenziale e se la vuoi devi fartela a mano scrivendo dei trigger (un bel casino).

Per scopi generici viene usato InnoDB che è abbastanza performante e ti garantisce l'integrità referenziale.

Però questi tipi di tabelle sono valide per praticamente tutti i DBMS e non sono i DBMS stessi come tu hai capito.

Spero di averti tolto un po' di confusione che avevi in testa

[vizzz]

1) non necessariamente, ma di solito io lo faccio
2) si certo puoi avere anche infinite chiavi esterne in una tabella
3) lo fa automaticamente il database quando crei la chiave esterna (parlo per postgresql, ma dovrebbe essere una cosa comune quando si parla di chiavi esterne)

[RaouL_BennetH]

Originariamente inviato da Trigger2009

Aspetta stai facendo confusione !!!
PostgreSQL e SQL Server NON SONO MOTORI, sono DBMS (ovvero i software che servono a creare e gestire le basi di dati) così come MySql.

Io ti parlo di tipi di tabelle: con questi DBMS tu puoi gestire tabelle realizzate con motori diversi che sono appunto InnoDB, MyIsam, Falcon e tantissimi altri...

In pratica ogni motore ti rappresenta in memoria le tabelle in modo diverso ed ognuno di essi ha dei pro e dei contro (apparte alcuni vecchissimi che non si usano più)...in fin dei conti quelli usati frequentemente sono InnoDB e MyIsam...vedila così: puoi avere due tipi di tabelle diverse:

Le tabelle InnoDB ti garantiscono la possibilità di avare vincoli di integrità referenziale come ti ho appena spiegato

Le tabelle MyIsam sono più veloci a fare certe operazioni e puoi creare indici fulltext per ricerche testuali molto potenti ma hanno il grosso handicap di non garantire l'integrità referenziale e se la vuoi devi fartela a mano scrivendo dei trigger (un bel casino).

Per scopi generici viene usato InnoDB che è abbastanza performante e ti garantisce l'integrità referenziale.

Però questi tipi di tabelle sono valide per praticamente tutti i DBMS e non sono i DBMS stessi come tu hai capito.

Spero di averti tolto un po' di confusione che avevi in testa

Uh.. forse me ne hai fatta venire di più

Cioè, in postgresql ci sono tabelle innodb e myisam


@vizzz
Chiarissimo grazie

[CwNd]

Ciao, vorrei farti osservare che potresti anche "inglobare" il dipendente nella tabella "persona". Questo tipo di soluzione, parlando di ristrutturazione di gerarchie, si chiama "mantenimento dell'entità genitore" e ha senso se la maggior parte delle persone è anche dipendente.

La tabella persona sarebbe quindi

CREATE TABLE Persone(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Nome varchar(25),
Cognome varchar(25),
Ruolo TIPO DEFAULT NULL,
indirizzo_id TIPO DEFAULT NULL)

Le persone dai dipendenti li riconosceresti dal fatto che hanno ruolo = null.

Tra l'altro non capisco perchè l'indirizzo ha un suo id, in realtà dovrebbe avere la chiave esterna della persona.O Sbaglio?

[Trigger2009]

Originariamente inviato da RaouL_BennetH

Uh.. forse me ne hai fatta venire di più

Cioè, in postgresql ci sono tabelle innodb e myisam


@vizzz
Chiarissimo grazie

In MySql ci sono tantissimi tipi di tabelle e le più usate sono InnoDB e MyIsam, credo che anche postgresql sia simile e probabilmente InnoDB e MyIsam saranno implementate...

Però forse dovresti leggerti qualcosa perchè mi pare che le tue basi siano un pochino debolucce...senza offesa...

[Trigger2009]

Originariamente inviato da CwNd

Ciao, vorrei farti osservare che potresti anche "inglobare" il dipendente nella tabella "persona". Questo tipo di soluzione, parlando di ristrutturazione di gerarchie, si chiama "mantenimento dell'entità genitore" e ha senso se la maggior parte delle persone è anche dipendente.

La tabella persona sarebbe quindi

CREATE TABLE Persone(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Nome varchar(25),
Cognome varchar(25),
Ruolo TIPO DEFAULT NULL,
indirizzo_id TIPO DEFAULT NULL)

Le persone dai dipendenti li riconosceresti dal fatto che hanno ruolo = null.

Tra l'altro non capisco perchè l'indirizzo ha un suo id, in realtà dovrebbe avere la chiave esterna della persona.O Sbaglio?

Per curiosità si tratta di una denormalizzazione giusto?

[CwNd]

Originariamente inviato da Trigger2009

Per curiosità si tratta di una denormalizzazione giusto?

Non lo so, io l'ho sempre chiamato "eliminazione di gerarchie di generalizzazione".

[Trigger2009]

Originariamente inviato da CwNd

Non lo so, io l'ho sempre chiamato "eliminazione di gerarchie di generalizzazione".

Capito, cmq credo che sia una denormalizazzione in quanto si stà racchiudendo un'informazione spezzabile in più tabelle in una sola tabella aumentandone i campi

[MEGA OT]
Ah cmq con quel triggerino ho risolto in maniera alternativa Praticamente c'erano 2 trigger diversi sulla stessa tabella ma non gli piace che ce ne siano 2 (anche se fanno operazioni diverse tra loro)...allora ho riunito tutto in uno e funziona
Ora però mi sono inchiodato su un altro trigger, poi posto ahaha...GIURO CHE è L'ULTIMO [/MEGA OT]

[CwNd]

Originariamente inviato da Trigger2009

Capito, cmq credo che sia una denormalizazzione in quanto si stà racchiudendo un'informazione spezzabile in più tabelle in una sola tabella aumentandone i campi

Con denormalizzazione probabilmente tu intendi il passaggio da uno schema in una certa forma normale a una meno raffinata che non è proprio quello che ho suggerito io a RaouL_BennetH.

[Trigger2009]

Originariamente inviato da CwNd

Con denormalizzazione probabilmente tu intendi il passaggio da uno schema in una certa forma normale a una meno raffinata che non è proprio quello che ho suggerito io a RaouL_BennetH.

mmm non per forza meno raffinata...da quello che ci hanno fatto vedere a lezione..a volte hai il tuo bel DB super normalizzato...hai lo schema fisico e per fare delle ottimizzazioni lo vai a denormalizzare perchè poi certe query sono più veloci in quanto anche se hai una certa ridondanza di dati magari denormalizzando vai ad aggiungere un campo in più ad una tabella e quando fai una query magari fai un join in meno

[RaouL_BennetH]

Originariamente inviato da Trigger2009

In MySql ci sono tantissimi tipi di tabelle e le più usate sono InnoDB e MyIsam, credo che anche postgresql sia simile e probabilmente InnoDB e MyIsam saranno implementate...

Però forse dovresti leggerti qualcosa perchè mi pare che le tue basi siano un pochino debolucce...senza offesa...

Nessuna offesa tranquillo

Comunque, documentandomi dal sito ufficiale di postgresql e anche su wikipedia, posso dire di essere sicuro di aver capito che postgresql NON usa ne InnoDb ne MyIsam.

Inoltre, a conferma anche di molti esperti e professionisti, MySql NON è sql standard.

[CwNd]

Originariamente inviato da RaouL_BennetH

Nessuna offesa tranquillo
Inoltre, a conferma anche di molti esperti e professionisti, MySql NON è sql standard.

Si è vero, da ne so non implementa l'operatore EXCEPT, i vincoli di CHECK, la CREATE DOMAIN e non so che altro.

Postgres invece di quanto ne so implementa molte più funzionalitù dello standard, se non tutte, oltre ad offrire, come un po' tutti i dbms, particolari funzionalità e particolari dialetti per quanto riguarda le funzioni pl/sql

[RaouL_BennetH]

Originariamente inviato da CwNd

Ciao, vorrei farti osservare che potresti anche "inglobare" il dipendente nella tabella "persona". Questo tipo di soluzione, parlando di ristrutturazione di gerarchie, si chiama "mantenimento dell'entità genitore" e ha senso se la maggior parte delle persone è anche dipendente.

La tabella persona sarebbe quindi

CREATE TABLE Persone(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Nome varchar(25),
Cognome varchar(25),
Ruolo TIPO DEFAULT NULL,
indirizzo_id TIPO DEFAULT NULL)

Le persone dai dipendenti li riconosceresti dal fatto che hanno ruolo = null.

Tra l'altro non capisco perchè l'indirizzo ha un suo id, in realtà dovrebbe avere la chiave esterna della persona.O Sbaglio?

Ciao

Per quanto riguarda questa applicazione, la maggior parte delle persone non è di tipo dipendente, anzi, diciamo pure che non c'è un tipo che ha 'maggioranza'

Per quanto riguarda l'indirizzo invece, ho pensato questo:

Dato che una marea di entità hanno un indirizzo (dipendenti, fornitori, sedi aggiuntive dei fornitori, clienti, sedi aggiuntive, consulenti, avvocati e blabla), ho pensato di creare un'unica entità indirizzo. Qualsiasi altra cosa che ha un'indirizzo, ne prende semplicemente l'id.

[cionci]

Originariamente inviato da CwNd

Le persone dai dipendenti li riconosceresti dal fatto che hanno ruolo = null.

Non mi sembra poi tanto bello...poi per carità, può avere senso per le prestazioni.

Sinceramente non mi tornano molto queste tabelle...

Persona
(id, nome, cognome)

E qui ci siamo...

Dipendente : Persona
(ruolo, personaId, indirizzoId)

Cosa vuoi rappresentare qui ? Una persona può avere diversi indirizzi ? Ma sono indirizzi di residenza delle persone (quindi ne esiste uno solo) o semplicemente indirizzi presso i quali una persona svolge un certo ruolo ?
Nel secondo caso va bene e la tabella è tutta chiave (cioè tutti gli attributi fanno parte della chiave primaria) se una persona può ricoprire più ruoli allo stesso indirizzo, altrimenti la chiave è (personaId, indirizzoId).
Nel primo caso devi fondere Persona e indirizzo, lasciando come chiave id.

[Guille]

Originariamente inviato da RaouL_BennetH

Per quanto riguarda l'indirizzo invece, ho pensato questo:

Dato che una marea di entità hanno un indirizzo (dipendenti, fornitori, sedi aggiuntive dei fornitori, clienti, sedi aggiuntive, consulenti, avvocati e blabla), ho pensato di creare un'unica entità indirizzo. Qualsiasi altra cosa che ha un'indirizzo, ne prende semplicemente l'id.

Nel tuo caso si possono avere indirizzi senza che ci siano entita' che li usino: non mi sembra molto logico.
Normalmente una entita' di quelle che tu hai citato ha almeno uno o piu' indirizzi (ad esempio un'azienda puo' avere un recapito fiscale, uno per la merce, ecc.) e quindi una soluzione piu' corretta dovrebbe prevedere la possibilita' di avere piu' indirizzi assegnati alla stessa entita' ad esempio:

CREATE TABLE Indirizzi(
persone_id,
tipo_indirizzo varchar(30),
domicilio varchar(100),
comune varchar(80),
provincia char(2),
cap varchar(5));

[cionci]

Il problema è che non sappiamo bene quale significato abbiano queste entità. Se non lo sappiamo non possiamo nemmeno determinare la forma migliore per rappresentarle.

Ad esempio, indirizzo potrebbe rappresentare l'indirizzo di una sede, oppure l'indirizzo di casa, o potrebbe rappresentare una rubrica indirizzi generica ed utilizzabile da relazioni diverse, in tal caso avrebbe senso per come è stata scritta.

[cionci]

Originariamente inviato da RaouL_BennetH

Dato che una marea di entità hanno un indirizzo (dipendenti, fornitori, sedi aggiuntive dei fornitori, clienti, sedi aggiuntive, consulenti, avvocati e blabla), ho pensato di creare un'unica entità indirizzo. Qualsiasi altra cosa che ha un'indirizzo, ne prende semplicemente l'id.

Ok, questo ha perfettamente senso.

Però tutto deve essere modificato.

sede (id, denominazione, descrizione, idindirizzo)
persona (id, nome, cognome, etc, idindirizzo)
ruolo (ruolo, iddipendente, idsede) con chiave (iddipendente, idsede)
dipendente (id, idpersona, tipo_contratto, scadenza, etc, etc)
indirizzo (id, indirizzo, comune, provincia, cap)