Share
Le app Web possono essere suddivise in due componenti principali: un front-end che visualizza e raccoglie informazioni e un back-end per la memorizzazione delle informazioni. In questo articolo, dimostrerò che cos'è un database relazionale e come progettare correttamente il database per memorizzare le informazioni della tua app.
Un database memorizza i dati in modo organizzato in modo che possa essere ricercato e recuperato in seguito. Dovrebbe contenere una o più tabelle. Una tabella è molto simile a un foglio di calcolo, in quanto è composta da righe e colonne. Tutte le righe hanno le stesse colonne e ogni colonna contiene i dati stessi. Se aiuta, pensa alle tue tabelle nello stesso modo in cui faresti una tabella in Excel.
Fig. 1
I dati possono essere inseriti, recuperati, aggiornati e cancellati da una tabella. La parola, creato, è generalmente usato al posto di inserito, così, collettivamente, queste quattro funzioni sono affettuosamente abbreviate come CRUD.
Un database relazionale è un tipo di database che organizza i dati in tabelle e li collega in base a relazioni definite. Queste relazioni consentono di recuperare e combinare dati da una o più tabelle con una singola query.
Ma quello era solo un mucchio di parole. Per comprendere veramente un database relazionale, è necessario crearne uno da solo. Iniziamo prendendo alcuni dati reali con cui possiamo lavorare.
Nello spirito degli articoli clone di Nettuts + Twitter (PHP, Ruby on Rails, Django), prendiamo alcuni dati di Twitter. Ho cercato Twitter per "#databases" e ho preso il seguente esempio di dieci tweet:
Tabella 1
| nome e cognome | nome utente | testo | created_at | following_username |
|---|---|---|---|---|
| "Boris Hadjur" | "_DreamLead" | "Cosa ne pensi di #emailing #campagne #traffic in #USA? È un buon mercato al giorno d'oggi? Hai # database?" | "Mar, 12 Feb 2013 08:43:09 +0000" | "Scootmedia", "MetiersInternet" |
| "Gunnar Svalander" | "GunnarSvalander" | "Bill Gates parla di database, software libero su Reddit http://t.co/ShX4hZlA #billgates #databases" | "Mar, 12 Feb 2013 07:31:06 +0000" | "klout", "zillow" |
| "Software GE" | "GEsoftware" | "RT @KirkDBorne: Letture in #Database: eccellente lettura, molte categorie: http://t.co/S6RBUNxq via @rxin Affascinante." | "Mar, 12 Feb 2013 07:30:24 +0000" | "DayJobDoc", "byosko" |
| "Adrian Burch" | "Adrianburch" | "RT @tisakovich: @NimbusData alla conferenza @Barclays Big Data a San Francisco oggi, parlando di #virtualizzazione, #database e # memoria flash". | "Mar, 12 Feb 2013 06:58:22 +0000" | "CindyCrawford", "Arjantim" |
| "Andy Ryder" | "AndyRyder5" | "http://t.co/D3KOJIvF articolo su Madden 2013 che usa l'intelligenza artificiale per produrre la super bowl #databases # bus311" | "Mar, 12 Feb 2013 05:29:41 +0000" | "MichaelDell", "Yahoo" |
| "Andy Ryder" | "AndyRyder5" | "http://t.co/rBhBXjma un articolo sulle impostazioni della privacy e facebook #databases # bus311" | "Mar, 12 Feb 2013 05:24:17 +0000" | "MichaelDell", "Yahoo" |
| "Brett Englebert" | "Brett_Englebert" | "# BUS311 NCFPD dell'Università del Minnesota sta creando # database per prevenire" frodi alimentari ". Http://t.co/0LsAbKqJ" | "Mar, 12 Feb 2013 01:49:19 +0000" | "RealSkipBayless", "stephenasmith" |
| Brett Englebert | "Brett_Englebert" | "# Le società BUS311 potrebbero proteggere i loro #database di produzione, ma i loro file di backup? Http://t.co/okJjV3Bm" | "Mar, 12 Feb 2013 01:31:52 +0000" | "RealSkipBayless", "stephenasmith" |
| "Nimbus Data Systems" | "NimbusData" | "@NimbusData CEO @tisakovich @BarclaysOnline Big Data conference a San Francisco oggi, parlando #virtualization, # databases, & #flash memory" | "Lun, 11 feb 2013 23:15:05 +0000" | "dellock6", "rohitkilam" |
| "SSWUG.ORG" | "SSWUGorg" | "Non dimenticarti di iscriverti alla nostra manifestazione GRATUITA questo venerdì: # Database, #BI e #Sharepoint: cosa devi sapere! Http://t.co/Ijrqrz29" | "Lun, 11 feb 2013 22:15:37 +0000" | "drsql", "steam_games" |
Ecco cosa significa ogni nome di colonna:
MySQL è utilizzato in quasi tutte le società Internet di cui hai sentito parlare.
Questi sono tutti dati reali; puoi cercare su Twitter e trovare effettivamente questi tweet.
Questo è buono. I dati sono tutti in un posto; quindi è facile da trovare, giusto? Non esattamente. Ci sono un paio di problemi con questo tavolo. Innanzitutto, vi sono dati ripetitivi su colonne. Le colonne "username" e "following_username" sono ripetitive, poiché entrambe contengono lo stesso tipo di dati: gli handle di Twitter. C'è un'altra forma di ripetizione all'interno della colonna "following_username". I campi dovrebbero contenere solo un valore, ma ciascuno dei campi "following_username" ne contiene due.
In secondo luogo, ci sono dati ripetitivi su righe.
@ AndyRyder5 e @Brett_Englebert hanno twittato due volte, quindi il resto delle loro informazioni è stato duplicato.
I duplicati sono problematici perché rendono le operazioni CRUD più impegnative. Ad esempio, sarebbe necessario più tempo per recuperare i dati perché il tempo sarebbe sprecato passando attraverso le righe duplicate. Inoltre, l'aggiornamento dei dati sarebbe un problema; se un utente cambia il suo handle di Twitter, dovremmo trovare ogni duplicato e aggiornarlo.
I dati ripetitivi sono un problema. Possiamo risolvere questo problema dividendo Tabella 1 in tabelle separate. Procediamo con la prima risoluzione della ripetizione tra le colonne.
Come notato sopra, le colonne "username" e "following_username" in Tabella 1 sono ripetitivi. Questa ripetizione è avvenuta perché stavo cercando di esprimere la relazione tra gli utenti. Miglioriamo Tabella 1La progettazione è suddivisa in due tabelle: una solo per le seguenti relazioni e una per il resto delle informazioni.
Fig. 2
Perché @Brett_Englebert segue @RealSkipBayless, il a seguire table esprimerà tale relazione memorizzando @Brett_Englebert come "from_user" e @RealSkipBayless come "to_user". Andiamo avanti e dividiamo Tabella 1 in queste due tabelle:
Tabella 2: The a seguire tavolo
| FROM_USER | to_user |
|---|---|
| _DreamLead | Scootmedia |
| _DreamLead | MetiersInternet |
| GunnarSvalander | Klout |
| GunnarSvalander | Zillow |
| GEsoftware | DayJobDoc |
| GEsoftware | byosko |
| adrianburch | Cindy Crawford |
| adrianburch | Arjantim |
| AndyRyder | MichaelDell |
| AndyRyder | Yahoo |
| Brett_Englebert | RealSkipBayless |
| Brett_Englebert | stephenasmith |
| NimbusData | dellock6 |
| NimbusData | rohitkilam |
| SSWUGorg | drsql |
| SSWUGorg | steam_games |
Tabella 3: The utenti tavolo
| nome e cognome | nome utente | testo | created_at |
|---|---|---|---|
| "Boris Hadjur" | "_DreamLead" | "Cosa ne pensi di #emailing #campagne #traffic in #USA? È un buon mercato al giorno d'oggi? Hai # database?" | "Mar, 12 Feb 2013 08:43:09 +0000" |
| "Gunnar Svalander" | "GunnarSvalander" | "Bill Gates parla di database, software libero su Reddit http://t.co/ShX4hZlA #billgates #databases" | "Mar, 12 Feb 2013 07:31:06 +0000" |
| "Software GE" | "GEsoftware" | "RT @KirkDBorne: Letture in #Database: eccellente lettura, molte categorie: http://t.co/S6RBUNxq via @rxin Affascinante." | "Mar, 12 Feb 2013 07:30:24 +0000" |
| "Adrian Burch" | "Adrianburch" | "RT @tisakovich: @NimbusData alla conferenza @Barclays Big Data a San Francisco oggi, parlando di #virtualizzazione, #database e # memoria flash". | "Mar, 12 Feb 2013 06:58:22 +0000" |
| "Andy Ryder" | "AndyRyder5" | "http://t.co/D3KOJIvF articolo su Madden 2013 che usa l'intelligenza artificiale per produrre la super bowl #databases # bus311" | "Mar, 12 Feb 2013 05:29:41 +0000" |
| "Andy Ryder" | "AndyRyder5" | "http://t.co/rBhBXjma un articolo sulle impostazioni della privacy e facebook #databases # bus311" | "Mar, 12 Feb 2013 05:24:17 +0000" |
| "Brett Englebert" | "Brett_Englebert" | "# BUS311 NCFPD dell'Università del Minnesota sta creando # database per prevenire" frodi alimentari ". Http://t.co/0LsAbKqJ" | "Mar, 12 Feb 2013 01:49:19 +0000" |
| Brett Englebert | "Brett_Englebert" | "# Le società BUS311 potrebbero proteggere i loro #database di produzione, ma i loro file di backup? Http://t.co/okJjV3Bm" | "Mar, 12 Feb 2013 01:31:52 +0000" |
| "Nimbus Data Systems" | "NimbusData" | "@NimbusData CEO @tisakovich @BarclaysOnline Big Data conference a San Francisco oggi, parlando #virtualization, # databases, & #flash memory" | "Lun, 11 feb 2013 23:15:05 +0000" |
| "SSWUG.ORG" | "SSWUGorg" | "Non dimenticarti di iscriverti alla nostra manifestazione GRATUITA questo venerdì: # Database, #BI e #Sharepoint: cosa devi sapere! Http://t.co/Ijrqrz29" | "Lun, 11 feb 2013 22:15:37 +0000" |
Questo sta guardando meglio. Ora nel utenti tavolo (Tabella 3), c'è solo una colonna con gli handle di Twitter. Nel a seguire tavolo (Tavolo 2), è solo un handle di Twitter per campo nella colonna "to_user".
Edgar F. Codd, lo scienziato informatico che ha definito le basi teoriche dei database relazionali, ha chiamato questa fase di rimuovere i dati ripetitivi su colonne nella prima forma normale (1NF).
Ora che abbiamo corretto le ripetizioni tra le colonne, dobbiamo correggere le ripetizioni su più righe. Poiché gli utenti @ AndyRyder5 e @Brett_Englebert hanno twittato due volte, le loro informazioni sono duplicate nel utenti tavolo (Tabella 3). Questo indica che dobbiamo estrarre i tweet e posizionarli nel loro tavolo.
Fig. 3
Come prima, "testo" memorizza il tweet stesso. Dato che la colonna "created_at" memorizza il timestamp del tweet, ha senso inserirlo in questo tavolo. Includo anche un riferimento alla colonna "username" in modo da sapere chi ha pubblicato il tweet. Ecco il risultato del posizionamento dei tweet nella propria tabella:
Tabella 4: The tweets tavolo
| testo | created_at | nome utente |
|---|---|---|
| "Cosa ne pensi di #emailing #campagne #traffic in #USA? È un buon mercato al giorno d'oggi? Hai # database?" | "Mar, 12 Feb 2013 08:43:09 +0000" | "_DreamLead" |
| "Bill Gates parla di database, software libero su Reddit http://t.co/ShX4hZlA #billgates #databases" | "Mar, 12 Feb 2013 07:31:06 +0000" | "GunnarSvalander" |
| "RT @KirkDBorne: Letture in #Database: eccellente lettura, molte categorie: http://t.co/S6RBUNxq via @rxin Affascinante." | "Mar, 12 Feb 2013 07:30:24 +0000" | "GEsoftware" |
| "RT @tisakovich: @NimbusData alla conferenza @Barclays Big Data a San Francisco oggi, parlando di #virtualizzazione, #database e # memoria flash". | "Mar, 12 Feb 2013 06:58:22 +0000" | "Adrianburch" |
| "http://t.co/D3KOJIvF articolo su Madden 2013 che usa l'intelligenza artificiale per produrre la super bowl #databases # bus311" | "Mar, 12 Feb 2013 05:29:41 +0000" | "AndyRyder5" |
| "http://t.co/rBhBXjma un articolo sulle impostazioni della privacy e facebook #databases # bus311" | "Mar, 12 Feb 2013 05:24:17 +0000" | "AndyRyder5" |
| "# BUS311 NCFPD dell'Università del Minnesota sta creando # database per prevenire" frodi alimentari ". Http://t.co/0LsAbKqJ" | "Mar, 12 Feb 2013 01:49:19 +0000" | "Brett_Englebert" |
| "# Le società BUS311 potrebbero proteggere i loro #database di produzione, ma i loro file di backup? Http://t.co/okJjV3Bm" | "Mar, 12 Feb 2013 01:31:52 +0000" | "Brett_Englebert" |
| "@NimbusData CEO @tisakovich @BarclaysOnline Big Data conference a San Francisco oggi, parlando #virtualization, # databases, & #flash memory" | "Lun, 11 feb 2013 23:15:05 +0000" | "NimbusData" |
| "Non dimenticarti di iscriverti alla nostra manifestazione GRATUITA questo venerdì: # Database, #BI e #Sharepoint: cosa devi sapere! Http://t.co/Ijrqrz29" | "Lun, 11 feb 2013 22:15:37 +0000" | "SSWUGorg" |
Tabella 5: The utenti tavolo
| nome e cognome | nome utente |
|---|---|
| "Boris Hadjur" | "_DreamLead" |
| "Gunnar Svalander" | "GunnarSvalander" |
| "Software GE" | "GEsoftware" |
| "Adrian Burch" | "Adrianburch" |
| "Andy Ryder" | "AndyRyder5" |
| "Brett Englebert" | "Brett_Englebert" |
| "Nimbus Data Systems" | "NimbusData" |
| "SSWUG.ORG" | "SSWUGorg" |
Dopo la divisione, il utenti tavolo (Tabella 5) ha righe univoche per gli utenti e i loro handle di Twitter.
Edgar F. Codd ha chiamato questa fase di rimuovere i dati ripetitivi su righe nella seconda forma normale (1NF).
I dati possono essere inseriti, recuperati, aggiornati e cancellati da una tabella.
Finora, Tabella 1 è stato diviso in tre nuove tabelle: a seguire (Tavolo 2), tweets (Tabella 4), e utenti (Tabella 5). Ma come è utile? I dati ripetitivi sono stati rimossi, ma ora i dati sono distribuiti su tre tabelle indipendenti. Per recuperare i dati, dobbiamo disegnare collegamenti significativi tra le tabelle. In questo modo possiamo esprimere query come "ciò che un utente ha twittato e chi sta seguendo un utente".
Il modo per disegnare i collegamenti tra le tabelle è innanzitutto assegnare a ciascuna riga di una tabella un identificatore univoco, definito come chiave primaria, quindi fare riferimento a quella chiave primaria nell'altra tabella a cui si desidera collegare.
In realtà l'abbiamo già fatto utenti (Tabella 5) e tweets (Tabella 4). Nel utenti, la chiave primaria è la colonna "username" perché nessun utente ha lo stesso Twitter. Nel tweets, facciamo riferimento a questa chiave nella colonna "username" in modo da sapere chi ha twittato cosa. Poiché è un riferimento, la colonna "nome utente" in tweets è chiamato una chiave straniera. In questo modo, il tasto "username" collega il utenti e tweets tavoli insieme.
La colonna "username" è la migliore idea per una chiave primaria per il utenti tavolo?
Da un lato, è una chiave naturale: ha senso cercare usando un handle di Twitter invece di assegnare a ciascun utente un ID numerico e cercare su di esso. D'altra parte, cosa succede se un utente vuole cambiare il suo handle di Twitter? Ciò potrebbe causare errori se la chiave primaria e tutte le chiavi esterne di riferimento non vengono aggiornate con precisione, errori che potrebbero essere evitati se fosse stato utilizzato un ID numerico costante. In definitiva, la scelta dipende dal tuo sistema. Se vuoi dare ai tuoi utenti la possibilità di cambiare il loro nome utente, è meglio aggiungere una colonna "id" a incremento automatico numerico a utenti e usalo come chiave primaria. Altrimenti, "username" dovrebbe fare bene. Continuerò a utilizzare "username" come chiave primaria per utenti
Passiamo a tweets (Tabella 4). Una chiave primaria dovrebbe identificare in modo univoco ogni riga, quindi quale dovrebbe essere la chiave primaria qui? Il campo "created_at" non funzionerà perché se due utenti eseguono tweet esattamente allo stesso tempo, i loro tweet avranno un timestamp identico. Il "testo" ha lo stesso problema in quanto se due utenti twittano entrambi "Ciao mondo", non siamo in grado di distinguere tra le righe. La colonna "username" è la chiave esterna che definisce il collegamento con il utenti quindi non scherziamo. Poiché le altre colonne non sono dei buoni candidati, è opportuno aggiungere una colonna "id" a incremento automatico numerico e utilizzarla come chiave primaria.
Tabella 6: The tweets tabella con una colonna "id"
| id | testo | created_at | nome utente |
|---|---|---|---|
| 1 | "Cosa ne pensi di #emailing #campagne #traffic in #USA? È un buon mercato al giorno d'oggi? Hai # database?" | "Mar, 12 Feb 2013 08:43:09 +0000" | "_DreamLead" |
| 2 | "Bill Gates parla di database, software libero su Reddit http://t.co/ShX4hZlA #billgates #databases" | "Mar, 12 Feb 2013 07:31:06 +0000" | "GunnarSvalander" |
| 3 | "RT @KirkDBorne: Letture in #Database: eccellente lettura, molte categorie: http://t.co/S6RBUNxq via @rxin Affascinante." | "Mar, 12 Feb 2013 07:30:24 +0000" | "GEsoftware" |
| 4 | "RT @tisakovich: @NimbusData alla conferenza @Barclays Big Data a San Francisco oggi, parlando di #virtualizzazione, #database e # memoria flash". | "Mar, 12 Feb 2013 06:58:22 +0000" | "Adrianburch" |
| 5 | "http://t.co/D3KOJIvF articolo su Madden 2013 che usa l'intelligenza artificiale per produrre la super bowl #databases # bus311" | "Mar, 12 Feb 2013 05:29:41 +0000" | "AndyRyder5" |
| 6 | "http://t.co/rBhBXjma un articolo sulle impostazioni della privacy e facebook #databases # bus311" | "Mar, 12 Feb 2013 05:24:17 +0000" | "AndyRyder5" |
| 7 | "# BUS311 NCFPD dell'Università del Minnesota sta creando # database per prevenire" frodi alimentari ". Http://t.co/0LsAbKqJ" | "Mar, 12 Feb 2013 01:49:19 +0000" | "Brett_Englebert" |
| 8 | "# Le società BUS311 potrebbero proteggere i loro #database di produzione, ma i loro file di backup? Http://t.co/okJjV3Bm" | "Mar, 12 Feb 2013 01:31:52 +0000" | "Brett_Englebert" |
| 9 | "@NimbusData CEO @tisakovich @BarclaysOnline Big Data conference a San Francisco oggi, parlando #virtualization, # databases, & #flash memory" | "Lun, 11 feb 2013 23:15:05 +0000" | "NimbusData" |
| 10 | "Non dimenticarti di iscriverti alla nostra manifestazione GRATUITA questo venerdì: # Database, #BI e #Sharepoint: cosa devi sapere! Http://t.co/Ijrqrz29" | "Lun, 11 feb 2013 22:15:37 +0000" | "SSWUGorg" |
Infine, aggiungiamo una chiave primaria al a seguire tavolo. In questa tabella, né la colonna "from_user" né la colonna "to_user" identificano in modo univoco ciascuna riga per conto proprio. Tuttavia "da_user" e "to_user" fanno insieme, dal momento che rappresentano una singola relazione. Una chiave primaria può essere definita su più di una colonna, quindi utilizzeremo entrambe queste colonne come chiave primaria per a seguire tavolo.
Per quanto riguarda la chiave esterna, "from_user" e "to_user" sono le chiavi esterne in quanto possono essere utilizzate per definire un collegamento con il utenti tavolo. Se chiediamo un handle di Twitter sulla colonna "from_user", otterremo tutti gli utenti che segue. Corrispondentemente, se chiediamo un handle di Twitter sulla colonna "to_user", otterremo tutti gli utenti che lo seguono.
Abbiamo fatto molto finora. Abbiamo rimosso le ripetizioni su colonne e righe separando i dati in tre diverse tabelle, quindi abbiamo scelto chiavi primarie significative per collegare le tabelle. Questo intero processo è chiamato normalizzazione e il suo output sono dati che sono organizzati in modo pulito secondo il modello relazionale. La conseguenza di questa organizzazione è che le righe appariranno nel database solo una volta che si spostano in avanti, il che a sua volta rende più semplici le operazioni CRUD.
Fig. 4 diagramma lo schema del database finalizzato. Le tre tabelle sono collegate insieme e le chiavi primarie sono evidenziate.
Fig. 4
Esistono piccole variazioni in SQL tra ciascun fornitore RDBMS, chiamato dialetti SQL.
Ora che sappiamo come progettare un database relazionale, come possiamo implementarne uno? I sistemi di gestione dei database relazionali (RDBMS) sono software che consentono di creare e utilizzare database relazionali. Ci sono diversi fornitori commerciali e open source tra cui scegliere. Sul lato commerciale, Oracle Database, IBM DB2 e Microsoft SQL Server sono tre soluzioni ben note. Sul lato libero e open source, MySQL, SQLite e PostgreSQL sono tre soluzioni ampiamente utilizzate.
MySQL è utilizzato in quasi tutte le società Internet di cui hai sentito parlare. Nel contesto di questo articolo, Twitter utilizza MySQL per memorizzare i tweet dei propri utenti.
SQLite è comune nei sistemi embedded. iOS e Android consentono agli sviluppatori di utilizzare SQLite per gestire il database privato della loro app. Google Chrome utilizza SQLite per archiviare la cronologia di navigazione, i cookie e le miniature nella pagina "Più visitata".
PostgreSQL è anche un RDBMS ampiamente utilizzato. La sua estensione PostGIS integra PostgreSQL con funzioni geospaziali che lo rendono utile per la mappatura delle applicazioni. Un utente notevole di PostgreSQL è OpenStreetMap.
Una volta scaricato e configurato un RDBMS sul sistema, il passo successivo è creare un database e delle tabelle al suo interno per inserire e gestire i dati relazionali. Il modo in cui lo fai è con Structured Query Language (SQL), che è la lingua standard per lavorare con RDBMS.
Ecco una breve panoramica delle istruzioni SQL comuni pertinenti ai dati di Twitter di esempio sopra. Vi consiglio di controllare il ricettario SQL per un elenco di query SQL più completo basato sull'applicazione.
CREARE lo sviluppo del DATABASE;
CREATE TABLE users (full_name VARCHAR (100), username VARCHAR (100));
Gli RDBMS richiedono che a ciascuna colonna di una tabella sia assegnato un tipo di dati. Qui ho assegnato le colonne "full_name" e "username" al tipo di dati VARCHAR che è una stringa che può variare in larghezza. Ho arbitrariamente impostato una lunghezza massima di 100. Un elenco completo di tipi di dati può essere trovato qui.
Accentsconagua
INSERISCI NEGLI UTENTI (full_name, username) VALORI ("Boris Hadjur", "_DreamLead"); SELEZIONA testo, created_at FROM tweets WHERE username = "_ DreamLead";
AGGIORNA gli utenti SET full_name = "Boris H" WHERE username = "_ DreamLead";
ELIMINA DAGLI utenti DOVE username = "_ DreamLead";
SQL è molto simile alle normali frasi inglesi. Esistono piccole variazioni in SQL tra ciascun fornitore RDBMS, chiamato dialetti SQL, ma le differenze non sono abbastanza drammatiche da non poter trasferire facilmente le tue conoscenze SQL dall'una all'altra.
In questo articolo, abbiamo imparato come progettare un database relazionale. Abbiamo preso una raccolta di dati e l'abbiamo organizzata in tabelle correlate. Abbiamo anche analizzato brevemente le soluzioni RDBMS e SQL. Quindi inizia scaricando un RDBMS e normalizzando alcuni dei tuoi dati in un database relazionale oggi.
Anteprima immagine sorgente: FindIcons.com/Barry Mieny
