Umfragedatenmodell - Wie vermeidet man EAV und übermäßige Denormalisierung?
Meine Datenbankkenntnisse sind bestenfalls mittelmäßig und ich muss ein Datenmodell für Umfragedaten entwerfen. Ich habe einige Gedanken dazu gemacht und momentan fühle ich, dass ich zwischen einer Art EAV-Modell feststecke und ein Design mit Hunderten von Tabellen mit jeweils Hunderten von Spalten (und Tausenden von Datensätzen). Es muss einen besseren Weg geben, dies zu tun, und ich hoffe, dass die klugen Leute in diesem Forum mir helfen können.
Meine Frage ist: Wie sollte ich die Antworten auf Umfragefragen in einem RDBMS modellieren? Die Verwendung von SQL Server ist obligatorisch. Daher sollten alternative Datenspeichersysteme von dieser Diskussion ausgeschlossen werden. (Sicher, einige sollten und werden ausgewertet, aber bitte nicht hier.) Ich brauche keine Lösung für das gesamte Datenmodell, denn ich bin nur am Antworten-Teil interessiert.
Ich habe bereits verschiedene Foren durchsucht, aber ich konnte nicht wirklich eine Lösung finden. Wenn es bereits anderswo gegeben wurde, bitte entschuldigen Sie mich und geben Sie mir einen Link, damit ich es lesen kann.
Einige Annahmen über die Daten, mit denen ich mich befassen muss:
- Jede Umfrage besteht aus 1 bis n Fragebögen
- Jeder Fragebogen besteht aus 100-2.000 Fragen (bitte ignorieren Sie, dass 2.000 Fragen wirklich eine Menge zu beantworten scheinen ...)
- Fragen können verschiedener Art sein: Multiple-Choice, Freitext, eine Zahl (wie Alter, Einkommen, Prozentsätze, ...)
- Jede Umfrage umfasst 10-200 Länder (Dies sind nicht die Befragten. Die Befragten sind tatsächlich Menschen in den Ländern.)
- Je nach Art des Fragebogens wird jeder Fragebogen von 100-20.000 Befragten pro Land beantwortet.
- Ein Land kann die Fragebögen für eine Umfrage anpassen, d. h. Fragen hinzufügen, entfernen oder bearbeiten
- Die Daten für ein Land werden in einer separaten Datenbank in diesem Land gesammelt. Es gibt keine Möglichkeit für eine Online-Integration von Anfang an.
- Die Daten für alle Länder müssen später integriert werden. Das bedeutet zum Beispiel, wenn ein Land eine Frage gelöscht hat, müssen diese Daten irgendwie von dem, was sie gesendet haben, abgeleitet werden, um ein einheitliches Design für alle Länder zu erreichen
- Ich muss die Integrations- und Reinigungssoftware schreiben, die mit den Daten jedes Landes funktionieren muss
- Am Ende müssen die Daten in flache Dateien, ein rechteckiges Raster pro Land und einen Fragebogen exportiert werden.
Ich habe dieses Thema bereits mit Menschen unterschiedlicher Herkunft diskutiert und bin noch nicht zu einer guten Lösung gekommen. Ich habe hauptsächlich zwei Arten von Meinungen.
- Die Domänenexperten, die es gewohnt sind, mit flachen Dateien (Tabellenkalkulation) für die Datenverarbeitung und -analyse zu arbeiten, wählen eine denormalisierte Struktur mit vielen Tabellen und Spalten, wie ich oben beschrieben habe (1 Tabelle pro Land und Fragebogen). Das hört sich für mich schrecklich an, weil ich gelernt habe, dass große Tabellen vermieden werden müssen. Es ist ärgerlich zu bestimmen, welche Spalten in einer Tabelle liegen, wenn die Datenbank damit arbeitet. Die Datenbank wird mit Hunderten von Tabellen vollgestopft (oder muss sogar Richten Sie mehrere Datenbanken ein, jede mit einem ähnlichen, aber etwas anderen Design), etc.
- O-O-Programmierer wählen ein stark "normalisiertes" Design, das effektiv zu einer zentralen Tabelle führen würde, die alle Antworten aller Befragten auf alle Fragen enthält. Diese Tabelle müsste entweder eine Spalte vom Typ sql_variant type oder mehrere Antwortspalten mit unterschiedlichen Typen enthalten, um Antworten verschiedener Typen zu speichern (Multiple Choice, Freitext, ..). Ersteres wäre im Wesentlichen ein EAV-Modell. Ich neige dazu, Joe Celko hier zu folgen, der stark von seiner Verwendung abrät (er nennt es OTLT oder "One True Lookup Table"). Letzteres würde bedeuten, dass jede Zeile null Zellen für die nicht anwendbaren Typen enthalten würde.
Eine andere Alternative, die ich mir vorstellen könnte, wäre, eine Tabelle pro Antworttyp zu erstellen, dh eine für Multiple-Choice-Fragen, eine für Freitextfragen usw. Das ist nicht so generisch, es würde zu vielen Unionen führen Joins, denke ich und ich müsste eine Tabelle hinzufügen, wenn ein neuer Antworttyp erfunden wird.
Tut mir leid, dass ich Sie mit all dem Text langweilt und danke Ihnen für Ihre Eingabe!
Prost, Alex
PS: Ich habe die gleiche Frage hier gestellt: Ссылка
4 Antworten
Nun, ich bin imgur, also werde ich das Bild später veröffentlichen.
Ich denke, dies ist in einem relationalen Modell durchaus möglich. Ich habe ein CDM erstellt, um zu zeigen, wie ich das machen würde.
Ausgehend
Es braucht 4 Entitäten, um die Umfrage eines Landes zu definieren. Einige Elternumfrage, das Land und eine Liste von Fragen. Ihre Fragen haben eine interne Beziehung. Wenn also ein Land eine Frage "bearbeitet", können Sie sowohl die Frage des Landes als auch die Frage, aus der es stammt, verfolgen. Die andere Sache, die Sie brauchen, ist eine mögliche Antwort Entity / Tabelle. Zu jeder Frage kann eine Liste möglicher Antworten (Mehrfachauswahl oder Bereiche usw.) gehören. Diese 4 sollten die "OUTBOUND" Seite davon vollständig definieren.
Eingehend
Die Seite "INBOUND" besteht aus nur zwei neuen Entitäten, dem Beantworter und der Antwort. Der Befragte ist einfach, nur die Demographie dieser Person, wenn Sie sie kennen und hier können Sie eine Beziehung zurück in das Land aufnehmen. Jeder Befragte beantwortete die Umfrage in einem bestimmten Land. (Die Person kann 1: n mit dem Befragten sein, wenn die Person reist oder die doppelte Staatsbürgerschaft hat)
Die Antwort ist grundlegend; Entweder ist es eine der Optionen, die in der Liste der möglichen Antworten aufgeführt sind, oder es wird bereitgestellt. Lassen Sie sich nicht davon abhalten, dass die Antwort eine Nummer, ein Datum usw. sein könnte. Entweder ist es ein FK oder eine Zeichenfolge.
Berichterstellung
Ein Bericht ist eine Verbindung über all diese ... Sie wählen ein Land und eine Umfrage aus und erhalten die Liste mit Fragen und Antworten.
Antwortkomplexität
Hängt davon ab, wo Sie Ihre Berechnungen durchführen möchten. Wenn Sie eine Varchar2 (4000) -Spalte für Ihre vom Benutzer bereitgestellten Antworten verwendet haben, können Sie der Frage ein Attribut hinzufügen, um den Datentyp der Antwort zu beschreiben. F: Alter? DT: Ganzzahl zwischen (0 und 130). Dann kann Ihre Integrationsebene die Validierung durchführen und nicht die Datenbank, die sie erzwingt. Oder Sie können 4 Spalten haben, eine für Nummer, Datum, Zeichen und CLOB. Und Ihre Integrationsschicht bestimmt die zu verwendende Spalte. Wenn Sie diese Antworten melden, wählen Sie einfach alle vier Spalten mit Coalesce () aus.
Ist dies ein EAV, weil der Datentyp "Antwort" etwas unklar ist
Nein, ist es nicht.
Ein EAV-Modell zerlegt eine Entität in eine Liste von Attributen. so:
%Vor%Weil Sie sehen, dass die Antwort-Spalte des Survey-Schemas sowohl Wörter als auch Zahlen enthält, wie es die Value-Spalte hier tut, denken Sie, dass das EAV definiert. Es tut nicht. Genau so, als ob ich diesem Modell drei Datentyp-Spalten hinzugefügt hätte, würde es das nicht von einem EAV ändern.
Ich hasse es sooooooo
Ich habe Leute sagen lassen, dass die Abfrage, die ich abstimmen soll, "so schnell wie möglich" gehen soll. Ok, also gib mir eine Milliarde Dollar und 30 Jahre. "Warte, eine Milliarde was?" "So viel wie", "so schnell wie" sind keine Anforderungen. Du kannst alles, was du willst, in einer Datenbank validieren ... eine Ladung Vor-Trigger erstellen, voila! Validierung in Hülle und Fülle.
Was ist der Datentyp einer Alterspalte? Oder Geburtstagssäule? Hängt davon ab, was Ihre Datenquelle ist. Einige ältere Datensätze enthalten möglicherweise nur Monat und Jahr oder nur Jahr oder "um" oder "circa" ein Jahr. Sie könnten nicht nur eine Zahlenspalte haben und so viel Validierung wie möglich durchführen. und NUMMER (2) kann BESSERE Validierung sein als nur NUMMER. Jetzt haben Sie also NUMMER (1), NUMMER (2), NUMMER ... um "so viel wie" zu haben.
Wo ich denke, dass Sie sich stolpern
Stellen Sie sich dies als ein konzeptionelles Datenmodell vor, nicht als ein physisches. In diesen Begriffen ist Umfrage eine Entität. Ist Frage eine Entität oder nur ein Attribut von Survey. Wenn Sie Eine Tabelle PER erstellt haben, sagen Sie ganz klar, dass die Frage nur ein Attribut von Survey ist, und wenn Sie sie vertikal speichern, ist dies ein EAV. Was dieses Modell zeigt, ist, dass Frage tatsächlich eine andere Entität ist. Es gibt eine Beziehung zwischen Fragen, z.B. "Ein Land kann Fragen bearbeiten". Dort war die ursprüngliche Frage und eine bearbeitete. Jede Frage enthält eine Sammlung möglicher Antworten. Und das wichtigste ist, dass alle Fragen sind . In einem EAV rufe ich fname, lname, bdate, age, major, salary usw. auf, alles sehr unterschiedliche Dinge, nur Attribute. In diesem Fall geben wir nicht den Namen der Agentur an, die die Umfrage erstellt hat, und das Datum, an dem sie ausgestellt wurde, und das Datum ist zurück und die Fragen usw. als Fragen.
Lass mich das anders sagen. Du bist Fedex. Sie möchten Zeitstempel für bestimmte Ereignisse speichern. Jedes Mal, wenn ein Paket eine Einrichtung oder ein Fahrzeug betritt oder verlässt. Zeit auf dem Lastwagen, Zeit vom Lastwagen und in die erste Einrichtung, Zeit aus dieser Einrichtung und in ein Flugzeug usw. Lagern Sie sie horizontal? Woher kennen Sie die Anzahl der Hopfen im Voraus? Wenn Sie sie vertikal speichern, macht dies automatisch eine EAV? Und wenn ja, warum.
Du bist eine Wetterfirma, die von Stationen im ganzen Land temps bekommt.Nehmen wir an, die Sensoren sind so ausgelegt, dass sie einen Messwert senden, wenn sich die Temperatur um +/- einen Grad ändert. Wenn Sie einen Sensor_ID speichern | Zeitstempel | Temp ist ein Lesetisch ist das ein EAV? Jedes Lesen ist kein Attribut des Sensors, sie sind selbst Entitäten, die zu einer Sammlung / Serie gehören.
Eine Sache, die die vertikale Speicherung von Antworten mit einem EAV gemeinsam hat, ist die Schwierigkeit, analytische Abfragen durchzuführen. Wenn Sie eine Liste aller Personen wünschen, die auf Frage 5 und 10 mit WAHR beantwortet haben, aber FALSCH auf 6 und 11, wäre das vertikal sehr schwierig. Vielleicht siehst du das deshalb als EAV. Wenn Sie das tun möchten, benötigen Sie einen anderen Speicher. Die relationale Speicherung der Fragen und Antworten ist nicht die beste Berichtsdatenbank. Gehen wir zurück zum Fedex-Beispiel. Es ist nicht einfach, Zeitberichte für den "Transit" zu erstellen, wenn die Zeilen vertikal sind.
Das hört sich so an, als würden Sie mit einem häufigen Problem kämpfen: Wie benutzt man einen Hammer, um eine Schraube zu befestigen?
Beide Alternativen, die Sie aufgelistet haben, sind aus verschiedenen Gründen schlecht. Das liegt aber daran, dass Sie versuchen, Ihr spezielles Datenmodell in ein relationales Datenbanksystem zu stopfen. Ein guter Ansatz wäre es, über die relationale Datenbank in anderen Datenbank- / Speichersystemen zu schauen, ein paar auszuprobieren und zu finden die beste Lösung für Ihr Projekt.
Ich habe das EAV-Modell ausprobiert und aufgegeben, weil es viel zu komplex war, und ich habe Angst, das Multi-Tabellen-Modell mit einem relationalen Datenbanksystem auszuprobieren. Die einfachste Lösung, die ich mit einer relationalen Datenbank gefunden habe, ist: Speichere jede vollständige Antwort als einen einzelnen CLOB, serialisiert in JSON oder YAML (oder etwas Leichtgewichtiges), in einer Tabelle responses .
Wenn ich SQL Server verwendete, ist Express in Ordnung, dann würde ich das tun:
- Tabelle mit Fragenliste, Flaggen für Typ (Bit), falls erforderlich (Bit), die richtige Antwort, falls vorhanden, usw.
- Tabelle mit Länderliste
- Tabellenverknüpfung von Ländern und Fragen (einige Länder erhalten möglicherweise keine Fragen
- Tabelle für Antworten mit Spalten für die Frage (n) und ein xml Spalte für die optionalen Fragen einschließlich derjenigen, die hinzugefügt werden
Wenn Sie nicht mit dem Zerschneiden von XML vertraut sind, dann verwenden Sie Spalten mit spärlichen Spalten für alle optionalen Fragen. Ich erinnere mich nicht genau an die Grenze für die Anzahl der spärlichen Spalten in einer Tabelle, aber ich glaube, sie liegt über 30.000. SQL Server speichert spärliche Spalten intern als XML und zerlegt sie, wenn die Spalte ausgewählt und ja indiziert werden kann
Das folgende Diagramm zeigt ein Diagramm, das mit SQL Server erstellt wurde. die Spalte AL_A4 enthält die Antwort auf QL_Id = 4 und ist vom Typ sparse. Die QL_Id in der QuestionList-Tabelle wird nicht als erforderlich markiert, damit Sie wissen, dass die Spalte in AnswerList spärlich ist.
Da Länder Fragen hinzufügen werden, erstellen Sie QuestionListCustom-, QuestiontoCountryCustom- und AnswerListCustom-Tabellen und fügen Sie die Informationen aus den benutzerdefinierten Fragen hinzu.
Ich bin mir sicher, dass es andere Möglichkeiten gibt, den Speicher zu entwerfen, so würde ich die Hausaufgaben machen, wenn das keine Hausaufgaben sind, dann arbeiten Sie sicher für die UNO.
Tags und Links sql database-design survey