Schlechtes BAD-databasedesign reparieren, sobald sich data im System befinden

Ich weiß, dass das keine Frage ist … eh jedenfalls HIER ist die Frage.

Ich habe eine database geerbt, die 1 (eine) Tabelle hat, die in etwa so aussieht. Sein Ziel ist es, zu registrieren, welche Arten in den verschiedenen (200 ungeraden) Ländern gefunden werden.

ID Species Afghanistan Albania Algeria American Samoa Andorra Angola .... Western Sahara Yemen Zambia Zimbabwe 

Eine Stichprobe der data wäre etwa so

 id Species Afghanistan Albania American Samoa 1 SP1 null null null 2 SP2 1 1 null 3 SP3 null null 1 

Es scheint mir dies ist eine typische viele zu viele Situation und ich möchte 3 Tische. Art, Land und ArtFoundInCountry

Die Verknüpfungstabelle (SpeciesFoundInCountry) würde sowohl in den Arten als auch in den Landtabellen Fremdschlüssel enthalten.

(Es ist schwer, das Diagramm zu zeichnen!)

 Species SpeciesID SpeciesName Country CountryID CountryName SpeciesFoundInCountry CountryID SpeciesID 

Gibt es einen magischen path, wie ich eine Insert-statement generieren kann, die die CountryID aus der neuen Country-Tabelle basierend auf dem Spaltennamen und der SpeciesID erhält, wo es eine 1 in der ursprünglichen Mega-Tabelle gibt?

Ich kann es für ein Land machen (das ist eine Auswahl um zu zeigen was ich will)

 SELECT Species.ID, Country.CountryID FROM Country, Species WHERE (((Species.Afghanistan)=1)) AND (((Country.Country)="Afghanistan")); 

(der Mega-Tisch heißt Spezies)

Aber mit dieser Strategie müsste ich die Abfrage für jede Spalte in der ursprünglichen Tabelle durchführen.

Gibt es eine Möglichkeit, dies in SQL zu tun?

Ich denke, ich kann eine Menge meiner where-Klauseln zusammenstellen und ein Skript schreiben, um das SQL zu machen, scheint aber unelegant!

Irgendwelche Gedanken (oder Klärung erforderlich)?

Solutions Collecting From Web of "Schlechtes BAD-databasedesign reparieren, sobald sich data im System befinden"

Ich würde ein Skript verwenden, um alle einzelnen Abfragen zu generieren, da dies ein einmaliger Importvorgang ist.

Einige Programme wie Excel eignen sich gut zum Mischen verschiedener Dimensionen von data (Vergleichen von Spaltennamen mit data in Zeilen), relationale databaseen sind jedoch selten.

Möglicherweise stellen Sie jedoch fest, dass einige Systeme (wie z. B. Microsoft Access) über komfortable Tools verfügen, mit denen Sie die data normalisieren können. Persönlich würde ich es schneller finden, das Skript zu schreiben, aber Ihre relativen Fähigkeiten mit Access und Scripting könnten sich von meinen unterscheiden.

Warum willst du es in SQL machen? Schreiben Sie einfach ein kleines Skript, das die Konvertierung durchführt.

Wenn ich auf diese stoße, schreibe ich ein Skript, um die Konvertierung zu machen, anstatt es in SQL zu tun. Es ist normalerweise viel schneller und einfacher für mich. Wählen Sie eine beliebige Sprache, mit der Sie sich auskennen.

Wenn dies SQL server wäre, würden Sie die Unpivot-Befehle verwenden, aber wenn Sie das Tag betrachten, das Sie zugewiesen haben, ist es für den Zugriff – habe ich recht?

Obwohl beim Zugriff ein Pivoting-Befehl vorhanden ist, gibt es keine Reverse-statement.

Sieht so aus, als könnte es mit einem komplexen Join gemacht werden. Sehen Sie sich diesen interessanten Artikel an, um zu erfahren, wie Sie in einem Select-Befehl die function deaktivieren können.

Wahrscheinlich werden Sie Ersatztabellen erstellen. Die Art des Skripts hängt von der Skriptsprache ab, die Ihnen zur Verfügung steht. Sie sollten jedoch in der Lage sein, die Länder-ID-Tabelle zu erstellen, indem Sie einfach die Spalten der Tabelle auflisten, die Sie jetzt haben. Sobald Sie dies getan haben, können Sie einige String-Ersetzungen durchführen, um alle eindeutigen Ländernamen zu durchlaufen und in die Tabelle speciesFoundInCountry einzufügen, in der die angegebene Länderspalte nicht null ist.

Sie könnten wahrscheinlich clever werden und die Systemtabellen nach den Spaltennamen abfragen und dann eine dynamische Abfrage-characterfolge zur Ausführung erstellen, aber ehrlich gesagt wird das wahrscheinlich hässlicher sein als ein schnelles Skript, um die SQL-statementen für Sie zu generieren.

Hoffentlich haben Sie nicht zu viel dynamischen SQL-Code, der auf die alten Tabellen zugreift, die in Ihrer Codebasis vergraben sind. Das könnte der wirklich harte Teil sein.

In SQL server generiert dies Ihre benutzerdefinierte Auswahl, die Sie demonstrieren. Sie können auf einen deployment extrapolieren

 select 'SELECT Species.ID, Country.CountryID FROM Country, Species WHERE (((Species.' + c.name + ')=1)) AND (((Country.Country)="' + c.name + '"))' from syscolumns c inner join sysobjects o on o.id = c.id where o.name = 'old_table_name' 

Wie bei den anderen würde ich es wahrscheinlich als eine einmalige schnelle Lösung in irgendeiner Weise tun, die für Sie arbeitet.

Bei diesen Arten von Conversions handelt es sich um Einzelstücke, schnelle Korrekturen und der Code muss nicht elegant sein, er muss einfach funktionieren. Für diese Art von Dingen habe ich es auf viele Arten gemacht.

Wenn das SQL server ist, können Sie die Tabelle sys.columns verwenden, um alle Spalten der ursprünglichen Tabelle zu finden. Dann können Sie dynamisches SQL und den Pivot-Befehl verwenden, um das zu tun, was Sie wollen. search sie online nach Syntax.

Ich würde definitiv Ihrem Vorschlag zustimmen, ein kleines Skript zu schreiben, um Ihr SQL mit einer Abfrage für jede Spalte zu erstellen.

In der Tat könnte dein Skript bereits in der time fertig sein, in der du über diese magische Frage nachgedacht hast (die du nur einmal nutzen würdest und dann wegwirfst, also was nutzt es, um alles magisch und perfekt zu machen)

Entschuldigung, aber der blutige Posting-Parser hat das Leerzeichen und die Formatierung meines Posts entfernt. Es macht es schwerer zu lesen.

@stampfen:

Über der Box, in die Sie die Antwort eingeben, befinden sich mehrere Schaltflächen. Der Code 101010 ist ein Codebeispiel. Sie wählen alle Ihren Text, der Code ist, und klicken Sie dann auf diese Schaltfläche. Dann wird es nicht viel durcheinander gebracht.

 cout>>"I don't know C" cout>>"Hello World" 

Ich würde eine Unionsfrage sehr grob verwenden:

 Dim db As Database Dim tdf As TableDef Set db = CurrentDb Set tdf = db.TableDefs("SO") strSQL = "SELECT ID, Species, """ & tdf.Fields(2).Name _ & """ AS Country, [" & tdf.Fields(2).Name & "] AS CountryValue FROM SO " For i = 3 To tdf.Fields.Count - 1 strSQL = strSQL & vbCrLf & "UNION SELECT ID, Species, """ & tdf.Fields(i).Name _ & """ AS Country, [" & tdf.Fields(i).Name & "] AS CountryValue FROM SO " Next db.CreateQueryDef "UnionSO", strSQL 

Sie hätten dann eine view, die an Ihr neues Design angehängt werden könnte.

Als ich den Titel "bad BAD database design" gelesen habe, war ich neugierig, wie schlimm das ist. Du hast mich nicht enttäuscht 🙂

Wie andere erwähnten, wäre ein Skript der einfachste path. Dies kann durch Schreiben von etwa 15 Codezeilen in PHP erreicht werden.

 SELECT * FROM ugly_table; while(row) foreach(row as field => value) if(value == 1) SELECT country_id from country_table WHERE country_name = field; if(field == 'Species') SELECT species_id from species_table WHERE species_name = value; INSERT INTO better_table (...) 

Offensichtlich ist das ein Pseudocode und wird nicht so funktionieren, wie er ist. Sie können auch die Länder- und Arten-Tabelle im laufenden Betrieb ausfüllen, indem Sie hier insert-statementen hinzufügen.

Entschuldigung, ich habe sehr wenig Access-Programmierung gemacht, aber ich kann eine Anleitung anbieten, die helfen sollte.

Lassen Sie uns zuerst das Problem durchgehen. Es wird angenommen, dass Sie in der Regel mehrere Zeilen in SpeciesFoundInCountry für jede Zeile in der ursprünglichen Tabelle generieren müssen. Mit anderen Worten: Arten leben in mehr als einem Land. Dies ist mit einem kartesischen Produkt, einem Join ohne Join-Kriterien, einfach möglich.

Um ein kartesisches Produkt zu erstellen, müssen Sie die Tabelle Land erstellen. Die Tabelle sollte die country_id von 1 bis N haben (N ist die Anzahl der eindeutigen Länder, 200 oder so) und den Ländernamen. Um das Leben einfacher zu machen, benutzen Sie einfach die Zahlen 1 bis N in Spaltenreihenfolge. Das würde Afghanistan 1 und Albanien 2 … Simbabwe N machen. Sie sollten dazu in der Lage sein, die Systemtabellen zu benutzen.

Als Nächstes erstellen Sie eine Tabelle oder view aus der ursprünglichen Tabelle, die die Spezies und einen Stich mit einer 0 oder 1 für jedes Land enthält. Sie müssen die Null, nicht Null in einen Text 0 oder 1 konvertieren und alle Werte in einer einzigen characterfolge verketten. Eine Beschreibung der Tabelle und ein Texteditor mit regulären Ausdrücken sollten dies erleichtern. Experimentieren Sie zuerst mit einer einzelnen Spalte und sobald das funktioniert, bearbeiten Sie die create view / insert mit allen Spalten.

Als Nächstes verbinden Sie die beiden Tabellen ohne Verbindungskriterien. Dies wird Ihnen einen Rekord für jede Art in jedem Land geben, Sie sind fast da.

Jetzt müssen Sie nur die datasätze ausfiltern, die nicht gültig sind. Sie haben eine Null an der entsprechenden Stelle in der characterfolge. Da die country_code -Spalte der Landtabelle die Position der Teilzeichenkette hat, müssen Sie nur die datasätze herausfiltern, in denen sie 0 ist.

 where substring(new_column,country_code) = '1' 

Sie müssen noch die Artentabelle erstellen und sich daran anschließen

 where a.species_name = b.species_name 

a und b sind Tabellenaliase.

Ich hoffe das hilft

OBTW,

Wenn Sie Abfragen haben, die bereits für die alte Tabelle ausgeführt werden, müssen Sie eine view erstellen, die die alten Tabellen mit den neuen Tabellen repliziert. Sie müssen eine Gruppe erstellen, um die Tabellen zu denormalisieren.

Teilen Sie Ihren Benutzern mit, dass die alte Tabelle / view in Zukunft nicht mehr unterstützt wird und alle neuen Abfragen oder Aktualisierungen für ältere Abfragen die neuen Tabellen verwenden müssen.

Wenn ich jemals eine Menge ähnlicher SQL-statementen erstellen und alle ausführen muss, finde ich Excel oft sehr praktisch. Nimm deine ursprüngliche Anfrage. Wenn Sie eine Länderliste in Spalte A und Ihre SQL-statement in Spalte B haben, formatiert als Text (in Anführungszeichen) mit Zellenreferenzen eingefügt, wo das Land in der SQL erscheint

zB = "INSERT INTO new_table SELECT … (species." & A1 & ") = …));"

Kopieren Sie dann die Formel, um 200 verschiedene SQL-statementen zu erstellen, kopieren Sie die Spalte in Ihren Editor und drücken Sie F5. Sie können dies natürlich mit so vielen variables tun, wie Sie möchten.

Als ich mit ähnlichen Problemen konfrontiert wurde, fand ich es praktisch, ein Skript zu generieren, das SQL-Skripte generiert. Hier ist die Stichprobe, die Sie angegeben haben, die abstrahiert wurde,% PAR1% anstelle von Afghanistan zu verwenden.

 SELECT Species.ID, Country.CountryID FROM Country, Species WHERE (((Species.%PAR1%)=1)) AND (((Country.Country)="%PAR1%")) UNION 

Außerdem wurde die Stichwort-Union hinzugefügt, um alle Selektionen zu kombinieren.

Als Nächstes benötigen Sie eine list der Länder, die aus Ihren vorhandenen data generiert werden:

Afghanistan Albanien. .

Als nächstes benötigen Sie ein Skript, das die Länderliste durchläuft und für jede Iteration eine Ausgabe erzeugt, die Afghanistan für% PAR1% in der ersten Iteration, Albanien für die zweite Iteration und so weiter ersetzt. Der Algorithmus ist wie ein Mail-Merge in einem Textverarbeitungsprogramm. Es ist ein wenig Arbeit, dieses Skript zu schreiben. Aber sobald Sie es haben, können Sie es in Dutzenden von einmaligen Projekten wie diesem verwenden.

Schließlich müssen Sie die letzte "UNION" manuell wieder in ein Semikolon ändern.

Wenn Sie Access erhalten können, um diese riesige Verbindung auszuführen, können Sie die gewünschten data in der von Ihnen gewünschten Form abrufen und in Ihre neue Tabelle insert.

Ich würde es einen dreistufigen process mit einer leichten vorübergehenden Änderung Ihrer SpeciesFoundInCountry-Tabelle machen. Ich würde eine Spalte zu dieser Tabelle hinzufügen, um den Country-Namen zu speichern. Dann wären die Schritte wie folgt.

1) Erstellen / Ausführen eines Skripts, das Spalten in der Quelltabelle durchläuft und einen datasatz in SpeciesFoundInCountry für jede Spalte mit einem wahren Wert erstellt. Dieser datasatz enthält den Namen des Landes. 2) Führen Sie eine SQL-statement aus, die das SpeciesFoundInCountry.CountryID-Feld aktualisiert, indem Sie der Country-Tabelle für den Country Name join. 3) Bereinigen Sie die Tabelle SpeciesFoundInCountry, indem Sie die Spalte CountryName entfernen.

Hier ist ein kleiner MS Access VB / VBA Pseudocode, um Ihnen den core zu geben

 Public Sub CreateRelationshipRecords() Dim rstSource as DAO.Recordset Dim rstDestination as DAO.Recordset Dim fld as DAO.Field dim strSQL as String Dim lngSpeciesID as Long strSQL = "SELECT * FROM [ORIGINALTABLE]" Set rstSource = CurrentDB.OpenRecordset(strSQL) set rstDestination = CurrentDB.OpenRecordset("SpeciesFoundInCountry") rstSource.MoveFirst ' Step through each record in the original table Do Until rstSource.EOF lngSpeciesID = rstSource.ID ' Now step through the fields(columns). If the field ' value is one (1), then create a relationship record ' using the field name as the Country Name For Each fld in rstSource.Fields If fld.Value = 1 then with rstDestination .AddNew .Fields("CountryID").Value = Null .Fields("CountryName").Value = fld.Name .Fields("SpeciesID").Value = lngSpeciesID .Update End With End IF Next fld rstSource.MoveNext Loop ' Clean up rstSource.Close Set rstSource = nothing .... End Sub 

Danach können Sie eine einfache SQL-statement ausführen, um die CountryID-Werte in der SpeciesFoundInCountry-Tabelle zu aktualisieren.

UPDATE SpeciesFoundInCountry INNER JOIN Land ON SpeciesFoundInCountry.CountryName = Land.CountryName SET SpeciesFoundInCountry.CountryID = Land.CountryID;

Schließlich müssen Sie lediglich die Tabelle SpeciesFoundInCountry bereinigen, indem Sie die Spalte CountryName entfernen.

**** SEITENHINWEIS: Ich finde es hilfreich, Länderlisten zu haben, die auch die ISO-Abkürzungen (Ländercodes) enthalten. Gelegentlich werden sie als Fremdschlüssel in anderen Tabellen verwendet, sodass ein Join zur Tabelle Country nicht in Abfragen eingeschlossen werden muss.

Für weitere Informationen: http://en.wikipedia.org/wiki/Iso_country_codes

Dies ist (hoffentlich) eine einmalige Übung, daher ist eine unelegante Lösung vielleicht nicht so schlimm, wie es sich anhört.

Das Problem (ich bin mir sicher, dass Sie das nur zu gut wissen!) Ist, dass Sie irgendwann in der Abfrage alle diese Spalten auflisten müssen. 🙁 Die Frage ist, was ist der eleganteste path, dies zu tun? Unten ist mein Versuch. Es sieht unhandlich aus, weil es so viele Spalten gibt, aber es könnte sein, was Sie suchen, oder zumindest könnte es Sie in der zeigen richtige Richtung.

Mögliche SQL-Lösung:

 /* if you have N countries */ CREATE TABLE Country (id int, name varchar(50)) INSERT Country SELECT 1, 'Afghanistan' UNION SELECT 2, 'Albania', UNION SELECT 3, 'Algeria' , UNION SELECT 4, 'American Samoa' , UNION SELECT 5, 'Andorra' , UNION SELECT 6, 'Angola' , ... UNION SELECT N-3, 'Western Sahara', UNION SELECT N-2, 'Yemen', UNION SELECT N-1, 'Zambia', UNION SELECT N, 'Zimbabwe', CREATE TABLE #tmp (key varchar(N), country_id int) /* "key" field needs to be as long as N */ INSERT #tmp SELECT '1________ ... _', 'Afghanistan' /* '1' followed by underscores to make the length = N */ UNION SELECT '_1_______ ... ___', 'Albania' UNION SELECT '__1______ ... ___', 'Algeria' ... UNION SELECT '________ ... _1_', 'Zambia' UNION SELECT '________ ... __1', 'Zimbabwe' CREATE TABLE new_table (country_id int, species_id int) INSERT new_table SELECT species.id, country_id FROM species s , #tmp t WHERE isnull( s.Afghanistan, ' ' ) + isnull( s.Albania, ' ' ) + ... + isnull( s.Zambia, ' ' ) + isnull( s.Zimbabwe, ' ' ) like t.key 

Mein Vorschlag

Persönlich würde ich das nicht tun. Ich würde eine schnelle und schmutzige Lösung machen, wie die, auf die du anspielst, außer dass ich die Länder-IDs fest codiere (weil du das nur einmal tun wirst, oder? Und du kannst es direkt nach der Erstellung des Landtabelle, damit Sie wissen, was alle IDs sind):

 INSERT new_table SELECT Species.ID, 1 FROM Species WHERE Species.Afghanistan = 1 INSERT new_table SELECT Species.ID, 2 FROM Species WHERE Species.Albania= 1 ... INSERT new_table SELECT Species.ID, 999 FROM Species WHERE Species.Zambia= 1 INSERT new_table SELECT Species.ID, 1000 FROM Species WHERE Species.Zimbabwe= 1