progettare un database in mysql

[pippuzzo80]

devo progettare il database di una biblioteca che contenga queste tabelle Libro, Autore, Casa editrice e Libri in prestito. Tra le varia tabelle ci devono essere delle corrispondenze, per esempio id della casa editrice deve avere una corrispondenza con l'id nella tabella libri, oppure isbn della tabella libri deve avere una corrispondenza con isbn della tabella libri fuori.

Secondo voi come mi conviene organizzare il tutto, utilizzare ad esempio chiavi esterne tra le varie tabelle?? oppure i trigger

fatemi sapere
grazie dell'aiuto

[LeleFT]

Sicuramente, quando è possibile, è meglio utilizzare i vincoli di chiave esterna. Il Trigger è una procedura che deve essere avviata quando si hanno a che faer con operazioni più gravose da far eseguire al DBMS (come la salvaguardia di vincoli aziendali, ecc).
Un problema che si riscontra con MySQL è che non supporta, però, i vincoli di chiave esterna (chissà perchè questa mancanza), sebbene sia supportata la clausola FOREIGN KEY.

Comunque, per ciò che devi faer, le chiavi esterne sono più che sufficienti e, di solito, i DBMS sono ottimizzati per il loro utilizzo.


Ciao.

[internet]

Originariamente inviato da pippuzzo80
devo progettare il database di una biblioteca che contenga queste tabelle Libro, Autore, Casa editrice e Libri in prestito. Tra le varia tabelle ci devono essere delle corrispondenze, per esempio id della casa editrice deve avere una corrispondenza con l'id nella tabella libri, oppure isbn della tabella libri deve avere una corrispondenza con isbn della tabella libri fuori.

Secondo voi come mi conviene organizzare il tutto, utilizzare ad esempio chiavi esterne tra le varie tabelle?? oppure i trigger

fatemi sapere
grazie dell'aiuto

MySQL utilizza di default l'engine MyISAM
http://dev.mysql.com/doc/mysql/en/my...ge-engine.html

che non supporta i vincoli di integrità referenziale (FOREIGN KEY), il parser SQL non segnala alcun errore (forse un warning) ma verrà ignorato il vincolo quando questo verrà violato.

Una soluzione consiste nell'utilizzare l'engine InnoDB
http://dev.mysql.com/doc/mysql/en/innodb.html

Infatti è pure suggerito nella guida
http://dev.mysql.com/doc/mysql/en/in...nstraints.html
Both tables must be InnoDB type.

All'atto pratico questo si traduce nell'aggiungere " TYPE=InnoDB;" quando viene creata una tabella
http://dev.mysql.com/doc/mysql/en/us...db-tables.html

Considerando questo situazione:

Ogni categoria è costituita da più prodotti.
Un prodotto appartiene ad una'unica categoria.

------------           ----------- 
| category |-1-------*-| product |
------------           -----------

CREATE TABLE category (
  catID int(11),
  name varchar(255) NOT NULL,
  PRIMARY KEY  (catID)
) TYPE=InnoDB;

CREATE TABLE product (
  productID int(11),
  name varchar(255) NOT NULL,
  cost decimal(10,2) NOT NULL,
  catID int(11) NOT NULL default '0',
  PRIMARY KEY  (productID),
  KEY catID (catID),
  FOREIGN KEY (`catID`) REFERENCES `category` (`catID`)
) TYPE=InnoDB;

Per decidere cosa deve fare il DBMS una volta che è stato violato un vincolo di integrità referenziale, hai a disposizione i seguenti costrutti:

per la cancellazione della chiave riferita
ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

per l'aggiornamento della chiave riferita
ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

tutto spiegato qui
http://dev.mysql.com/doc/mysql/en/in...nstraints.html

[pippuzzo80]

grazie della risposta, ma purtroppo non ci riesco:

creao una tabella casaeditrice con id e nome (id primary key), successivamente creo una tabella di prova con id referenziato con id di casaeditrice e setto type=innodb(fatto anche in casaeditrice), ma quando premo invio mi dà questo errore: error 1005 can't create table
poi il percorso del mio table ed infine (errno 150).

Ho provato ad aggirare l'ostacolo creando prima il table senza la modifica di type e poi utilizzando alter table per modificare il type e setterlo su innodb, in pratica non mi dà errore e me lo setta, ma facendo alcune prove non controlla i vincoli di integrità.

Da quando ho capito io i vincoli dovrebbero funzionare così: in casa editrice creo un record con id 1, in prova allora posso creare un record con id 1, ma non posso creare nessun record con id 2 perchè questo non esiste nella tabella casaeditrice. Inoltre se elimino il record con id 1 nella tabella casaeditrice dovrebbe effettuare delle modifiche nella tabella prova.... E' giusto il mio ragionamento??

grazie

[LeleFT]

Per quanto riguarda il problema con InnoDB dovresti controllare di avere una versione aggiornata di MySQL. Segui il ilnk che ti è stato proposto da internet precedentemente.

Per il ragionamento sul funzionamento della chiave esterna, direi che è corretto. Puoi decidere tu che comportamento deve assumere il DBMS quando viene modificato (aggiornato o cancellato) un record sulla tabella Master (quella che contiene l'ID primario): generalmente puoi decidere che il DBMS cancelli in cascata tutti i record corrispondenti nella tabella legata da vincolo (quando, ad esempio, viene cancellato il record corrispondente), oppure che vengano aggiornati in cascata (se c'è una modifica), oppure puoi decidere che vengano settati a NULL tutti i valori relativi, che non venga apportata alcuna modifica, ecc.
Trovi tutto sulla documentazione di MySQL, anche se le mie informazioni dovrebbero valere in generale per un qualsiasi DBMS (o almeno quelli che supportano un minimo di standard SQL).


Ciao.

[pippuzzo80]

grazie per l'aiuto, ma scaricando anche la versione aggiornata continuo ad avere sempre lo stesso problema:

create table prova(idprova integer(5) not null,nome varchar(20) not null, cognome varchar(20) not null, FOREIGN KEY (idprova) REFERENCES casaeditrice(id) ON DELETE NO ACTION) type=INNODB;

error 1005 (00000): can't create table '.\biblioteca\prova.frm' (errno: 150)

[floyd]

il motore di casaeditrice deve essere innodb

[pippuzzo80]

certo quello l'ho settato e non mi dà nessun errore

quando creo una tabella esiste un comando per vedere le caratteristiche della tabella compreso il motore utilizzato?

[LeleFT]

Devi esplicitamente creare un indice sul campo che intendi utilizzare come FOREIGN KEY. Ti faccio un esempio che ho appena provato e funziona:

CREATE TABLE Master (
   ID INT(6) NOT NULL,
   Dsc TEXT,
   PRIMARY KEY(ID)
) TYPE=INNODB;

CREATE TABLE Slave (
   ID INT(6) NOT NULL,
   FK INT(6),
   INDEX FK_IDX (FK),
   PRIMARY KEY(ID),
   FOREIGN KEY(FK) REFERENCES Master(ID) ON DELETE NO ACTION
) TYPE=INNODB;

Ciao.

[pippuzzo80]

grazie mille adesso và, ancora due domande:
1)se inserisco la clausola on delete set null ritorna sempre l'errore di prima

2)ho creato una tabella in cui con lo stesso indice ho creato due chiavi esterne una con il comando on delete e una con il comando on update è giusto in questo modo o ne esiste uno per farlo in un unico comando?? inoltre in entrambi ho inserito la clausola CASCADE(da quando ho capito dovrebbe aggiornare in cascata tutti i record che soddisfano l'attributo), però se cancello un record dalla chiave primaria allora tutto va bene (vengono cancellati i record dalla tabella referenziata) mentre se utilizzo il comando update per modificare id della tabella casa editrice allora mi comprare l'errore: cannot delete or update a parent row: a foreign key constraint fails!!perchè??

mi stai risolvendo tanti problemi!!
grazie