Wie entwerfe ich eine Datenbank für benutzerdefinierte Felder?

Meine Anforderungen sind:

  • Müssen in der Lage sein, um dynamisch hinzufügen von benutzerdefinierten Feldern beliebigen Datentyps
  • Müssen in der Lage sein, um die Abfrage UDFs schnell
  • Müssen in der Lage sein zu tun Berechnungen auf UDFs basieren auf dem Datentyp
  • Müssen in der Lage sein, zu Sortieren UDFs basieren auf dem Datentyp

Weitere Informationen:

  • Ich bin auf der Suche nach Leistung sind vor allem
  • Es gibt ein paar Millionen Stammsätze, die UDF-Daten angehängt
  • Wenn ich der letzten überprüfung waren es über 50mil UDF Einträge in unserer aktuellen Datenbank
  • Die meisten der Zeit, eine UDF-Datei ist nur an ein paar tausend von den Stammsätzen, nicht alle von Ihnen
  • UDFs sind nicht verbunden oder als Schlüssel verwendet. Sie sind nur Daten, die für Abfragen oder Berichte

Optionen:

  1. Erstellen Sie eine große Tabelle mit StringValue1, StringValue2... IntValue1, IntValue2,... etc. Ich hasse diese Idee, aber betrachten es, wenn mir jemand sagen kann es ist besser als andere Vorstellungen und warum.

  2. Erstellen einer dynamischen Tabelle, die fügt eine neue Spalte auf Nachfrage, wie gebraucht. Ich weiß auch nicht wie diese Idee, da ich das Gefühl die Leistung wäre langsam, es sei denn, Sie indizierten jeder Spalte.

  3. Erstellen Sie eine einzelne Tabelle mit UDFName, UDFDataType, und Wert. Wenn eine neue UDF Hinzugefügt wird, erstellen Sie eine Ansicht, die zieht nur die Daten und analysiert Sie, in welcher Art auch immer angegeben ist. Gegenstände, die nicht der Analyse Kriterien, die den Wert NULL zurückgeben.

  4. Erstellen Sie mehrere UDF-Tabellen, eine pro Datentyp. So hätten wir Tabellen für UDFStrings, UDFDates, etc. Wahrscheinlich würde das gleiche tun wie #2 und das automatische generieren einer Ansicht jederzeit ein neues Feld Hinzugefügt wird,

  5. XML-Datentypen? Ich habe nicht gearbeitet, diese vor, aber gesehen haben Sie erwähnt. Nicht sicher, wenn Sie geben würde mir die Ergebnisse ich will vor allem mit Leistung.

  6. Etwas anderes?

InformationsquelleAutor der Frage Rachel | 2011-02-24

  1. 45

    Wenn die Leistung ist die primäre Sorge, ich gehe mit #6... eine Tabelle pro UDF (wirklich, das ist eine Variante von #2). Diese Antwort ist speziell auf diese situation und die Beschreibung der Verteilung der Daten und Zugriff Muster beschrieben.

    Vorteile:

    1. Weil Sie darauf hinweisen, dass einige UDFs
      die Werte haben für einen kleinen Teil der
      aus den gesamten Daten-set, eine separate
      Tabelle geben Sie die beste
      Leistung, da die Tabelle
      nur so groß wie es sein muss
      zur Unterstützung der UDF. Das gleiche gilt für die zugehörigen Indizes.

    2. Erhalten Sie auch einen Geschwindigkeitsschub durch die Begrenzung der Menge der Daten, die verarbeitet werden, die für Aggregationen oder anderen Transformationen. Die Aufteilung der Daten in mehrere Tabellen können Sie einige der Aggregation und andere statistische Auswertungen über die UDF-Daten, dann join das Ergebnis an den master-Tabelle über Fremdschlüssel zu bekommen, der nicht aggregierten Attribute.

    3. Können Sie mit Tabellen - /Spaltennamen
      das widerspiegeln, was die Daten tatsächlich ist.

    4. Sie haben vollständige Kontrolle, um die Verwendung von Datentypen,
      check-Einschränkungen, default-Werte, etc.
      zum definieren von Daten-Domänen. Unterschätzen Sie nicht die Leistungseinbußen aufgrund von on-the-fly-Konvertierung der Datentypen. Solche
      Einschränkungen auch helfen RDBMS query
      Optimierer entwickeln effektiver
      Pläne.

    5. Sollten Sie jemals brauchen werden, um die Nutzung ausländischer
      keys, built-in deklarativen
      Referentielle
      Integrität ist selten " out-performed by
      trigger-oder application-level
      Einschränkung der Vollstreckung.

    Nachteile:

    1. Dies könnte Sie eine Menge von Tabellen.
      Die Durchsetzung der schema-Trennung und/oder eine
      Namenskonvention lindern würde
      dies.

    2. Gibt es mehr Anwendungs-code
      benötigt werden zum Betrieb der UDF-definition
      und-management. Ich erwarte, dass dieser
      noch weniger code notwendig als für die
      original-Optionen 1, 3, & 4.

    Weitere Überlegungen:

    1. Wenn es etwas gibt, über das
      Art der Daten, die das machen würde
      Sinn für die UDFs gruppiert werden,
      das sollte gefördert werden. So,
      diese Daten können Elemente kombiniert werden
      in einer einzigen Tabelle. Zum Beispiel,
      lassen Sie uns sagen, Sie haben UDFs für Farbe,
      Größe und Kosten. Die Tendenz in der
      Daten ist, dass die meisten Instanzen dieses
      Daten, die sieht aus wie 

       'red', 'large', 45.03

      eher als 

       NULL, 'medium', NULL

      In einem solchen Fall, werden Sie nicht entstehen
      spürbaren Geschwindigkeitsnachteil durch
      die Kombination der 3 Spalten in Tabelle 1
      da einige Werte NULL und
      Sie verhindern, dass 2 weitere Tabellen,
      die 2 weniger joins benötigt, wenn
      Sie müssen Zugriff auf alle 3 Spalten.

    2. Wenn du eine Leistung von einer Wand
      UDF, die sehr stark bevölkert ist und
      Häufig verwendet, dann sollte das werden
      als für die Aufnahme in die
      master-Tabelle.

    3. Logische tabellendesign können Sie
      einem gewissen Punkt, aber wenn der Datensatz
      zählt sich wirklich Massiv, Sie auch
      sollte anfangen zu schauen, was die Tabelle
      Partitionierungs-Optionen, die Ihnen Ihr RDBMS der Wahl.

    InformationsquelleAutor der Antwort Phil Helmer

  2. 22

    Habe ich geschrieben über dieses problem viel. Die häufigste Lösung ist die Entity-Attribut-Wert-antipattern, was ist ähnlich, was Sie beschreiben, in Ihrer option #3. Vermeiden Sie diese design wie die Pest.

    Was ich für diese Lösung, wenn ich wirklich dynamische benutzerdefinierte Felder zum speichern von Ihnen in einem blob von XML, so kann ich neue Felder hinzuzufügen, zu jeder Zeit. Aber machen Sie schnell, auch das erstellen von zusätzlichen Tabellen für jedes Feld, das Sie brauchen, zu suchen oder zu Sortieren (Sie müssen nicht eine Tabelle pro Bereich--nur eine Tabelle pro durchsuchbare Feld). Dies wird manchmal als ein invertierter index-design.

    Können Sie Lesen einen interessanten Artikel von 2009 über diese Lösung hier: http://backchannel.org/blog/friendfeed-schemaless-mysql

    Oder Sie können eine Dokument-orientierte Datenbank, wo es erwartet wird, dass Sie benutzerdefinierte Felder pro Dokument. Ich würde wählen,Solr.

    InformationsquelleAutor der Antwort Bill Karwin

  3. 9

    Ich würde wahrscheinlich eine Tabelle erstellen von der folgenden Struktur:

    • varchar Name
    • Typ varchar
    • dezimal NumberValue
    • varchar StringValue
    • date DateValue

    Den genauen Typen, hängt natürlich von Ihren Bedürfnissen (und natürlich auch auf dem dbms, die Sie verwenden). Sie können auch die NumberValue (dezimal) Feld für int und Boolean. Sie müssen möglicherweise anderen Arten als gut.

    Müssen Sie einige link zu der Master-Aufzeichnungen, die eigenen Wert. Es ist wahrscheinlich am einfachsten und schnellsten erstellen Sie eine Benutzer-Felder-Tabelle für jede master-Tabelle und fügen Sie einen einfachen Fremdschlüssel. Auf diese Weise können Sie die filter-Stammsätze durch die user-Felder leicht und schnell.

    Möchten Sie vielleicht, um eine Art von meta-Daten. So dass Sie am Ende mit den folgenden:

    Tabelle UdfMetaData

    • int id
    • varchar Name
    • Typ varchar

    Tabelle MasterUdfValues

    • int Master_FK
    • int MetaData_FK
    • dezimal NumberValue
    • varchar StringValue
    • date DateValue

    Was auch immer Sie tun, ich würde nicht ändern Sie die Struktur der Tabelle dynamisch. Es ist ein Wartungs-Albtraum. Ich würde auch nicht Verwendung von XML-Strukturen, Sie sind viel zu langsam.

    InformationsquelleAutor der Antwort Stefan Steinegger

  4. 8

    Das klingt wie ein problem, das könnte besser gelöst werden, indem eine nicht-relationale Lösung, wie MongoDB oder CouchDB.

    Beide ermöglichen eine dynamische schema-Erweiterung und ermöglicht Ihnen, pflegen die Tupel Integrität, die Sie suchen.

    Ich Stimme mit Bill Karwin, das EAV-Modell ist nicht eine performante Ansatz für Sie ist. Verwendung von name-Wert-Paare in einem relationalen system ist nicht per se schlecht, sondern nur gut funktioniert, wenn die name-Wert-paar eine komplette Tupel von Informationen. Wenn es zwingt Sie dazu, sich dynamisch zu rekonstruieren, eine Tabelle zur Laufzeit, alle Arten von Dingen beginnen, die schwer zu bekommen. Abfragen zu einer übung in pivot-Wartung oder zwingt Sie, um die push-Tupel-Rekonstruktion bis in die Objekt-Ebene.

    Können Sie nicht bestimmen, ob eine null oder missing-Wert ist ein Gültiger Eintrag oder fehlt der Eintrag, ohne die Einbettung von schema-Regeln in die Objekt-Ebene.

    Verlieren Sie die Fähigkeit, effizient verwalten Sie Ihre schema. Ist eine 100-Zeichen-varchar-der richtige Typ für die "Wert" - Feld? 200-Zeichen? Sollte es nvarchar statt? Es kann sein a hard trade-off endet und eine mit Sie mit zu platzieren, die künstlichen Grenzen, die auf die dynamische Natur von Ihrem set. So etwas wie "du kannst nur x user-definierte Felder und kann jeweils nur y Zeichen lang ist.

    Mit einem Dokument-orientierte Lösung, wie MongoDB oder CouchDB, Sie pflegen alle Attribute eines Benutzers in einem einzigen Tupel. Da joins sind nicht ein Problem, das Leben ist glücklich, denn keiner der beiden hat auch mit joins, trotz des Hypes. Die Benutzer können definieren, wie viele Attribute, wie Sie wollen (oder Sie können) bei Längen, die es nicht schwer zu verwalten, bis Sie erreichen etwa 4MB.

    Wenn Sie Daten haben, die erfordert, dass SÄURE-level-Integrität, sollten Sie überlegen, die Aufteilung der Lösung, mit der high-integrity-Daten Leben in der relationalen Datenbank und die dynamischen Daten, die das Leben in einer nicht-relationalen datenspeicher.

    InformationsquelleAutor der Antwort Data Monk

  5. 6

    Selbst wenn Sie für einen Benutzer hinzufügen benutzerdefinierter Spalten, es wird nicht zwangsläufig der Fall sein, dass die Abfrage auf die Spalten auch durchführen zu können. Es gibt viele Aspekte, die in query-design, die es Ihnen ermöglichen, gut zu, von denen die wichtigste ist die richtige Spezifikation, auf welche gespeichert werden soll, in den ersten Platz. Also, grundsätzlich ist es so, dass Sie zulassen möchten, dass Benutzer die create schema-ohne Gedanken an technische Daten und die schnell in der Lage, ableiten von Informationen aus diesem schema? Wenn ja, dann ist es unlikley, dass eine solche Lösung wird die Waage auch vor allem, wenn Sie zulassen möchten, dass der Benutzer numerische Analyse der Daten.

    Option 1

    IMO diese Vorgehensweise gibt Ihnen-schema nicht wissen, was das schema ist, das sich ein Rezept für eine Katastrophe und ein Alptraum für den Berichts-Designer. I. e., Sie müssen die meta-Daten, um zu wissen, welche Spalte gespeichert, was Daten. Wenn die Metadaten Durcheinander gebracht, es hat das Potenzial, Schlauch Ihre Daten. Plus, es macht es einfach, die falschen Daten in der falschen Spalte. ("Was? String1 enthält die Namen von Klöstern? Ich dachte, es war Chalie Sheen Lieblings-Drogen.")

    Option 3,4,5

    IMO, Anforderungen 2, 3 und 4 zu beseitigen, eine variation eines EAV. Wenn Sie brauchen, um Abfragen, Sortieren oder Berechnungen auf diesen Daten, dann eine EAV ist Cthulhu 's dream und Ihre team-Entwicklung und DBA' s nightmare. Die EAV wird erstellen Sie einen Engpass in Bezug auf Leistung und wird nicht geben Ihnen die Integrität der Daten, die Sie benötigen, um schnell auf die Informationen, die Sie wollen. Abfragen schnell drehen, um Kreuztabelle gordischen Knoten.

    Option 2,6

    Dass lässt wirklich eine Wahl: erfassen von Spezifikationen und erstellen Sie dann aus dem schema.

    Wenn der Kunde möchte die beste Leistung bei den Daten, die Sie speichern möchten, dann müssen Sie gehen durch den Prozess der Arbeit mit einem Entwickler, um Ihre Bedürfnisse zu verstehen, so dass es gespeichert wird, so effizient wie möglich. Er könnte immer noch in einer Tabelle gespeichert werden, getrennt von den übrigen Tabellen mit code, der dynamisch ein Formular basierend auf dem schema der Tabelle. Wenn Sie eine Datenbank, die ermöglicht, dass die erweiterten Eigenschaften auf Spalten, könnten Sie sogar mit jenen zu helfen, die Formular-generator verwenden, schön, labels, tooltips etc. so, dass war alles, was notwendig ist, fügen Sie das schema. Entweder Weg, um erstellen und ausführen-Berichte effizient, müssen die Daten richtig gespeichert werden. Wenn die Daten in Frage, die haben viele Nullen, einige Datenbanken die Möglichkeit der Speicherung dieser Art von Informationen. Zum Beispiel, SQL Server 2008 verfügt über eine Funktion namens auf die Spalten mit geringer Dichte, die speziell für Daten mit vielen Nullen.

    Wenn dies nur eine Tasche von Daten, auf die sich keine Analyse, Filterung oder Sortierung zu tun, würde ich sagen, dass einige variation eines EAV könnte den trick tun. Angesichts Ihrer Anforderungen, die effizienteste Lösung zu sein, um die richtigen Vorgaben, auch wenn Sie speichern diese neue Spalten in separaten Tabellen und erstellen von Formularen, die dynamisch aus Tabellen.

    Spalten Mit Geringer Dichte

    InformationsquelleAutor der Antwort Thomas

  6. 4
    1. Erstellen Sie mehrere UDF-Tabellen, eine pro Datentyp. So hätten wir Tabellen für UDFStrings, UDFDates, etc. Wahrscheinlich würde das gleiche tun wie #2 und das automatische generieren einer Ansicht jederzeit ein neues Feld Hinzugefügt wird,

    Nach meinen Recherchen von mehreren Tabellen basiert auf den Daten-Typ nicht zu helfen, Sie in der Leistung. Vor allem, wenn Sie Massendaten wie 20K oder 25K Datensätze mit 50+ SEF. Die Leistung war die schlechteste.

    Sollten Sie gehen mit einzelne Tabelle mit mehreren Spalten wie:

    varchar Name
varchar Type
decimal NumberValue
varchar StringValue
date DateValue

    InformationsquelleAutor der Antwort Amit Contractor

  7. 4

    Dies ist eine problematische situation, und keine der Lösungen scheint die "richtige". Aber option 1 ist wahrscheinlich das beste, sowohl in Bezug auf Einfachheit und in Bezug auf Leistung.

    Dies ist auch die Lösung, die in einigen kommerziellen enterprise-Anwendungen.

    BEARBEITEN

    andere option, die jetzt verfügbar ist, aber nicht existieren (oder zumindest nicht ausgereift) wenn die Frage ursprünglich war gefragt, ist die Verwendung von json-Felder in der DB.

    viele relationale DBs unterstützt nun json-Felder (das kann eine dynamische Liste der sub-Felder) und damit Abfragen auf Ihnen

    postgress

    mysql

    InformationsquelleAutor der Antwort Ophir Yoktan

  8. 2

    Habe ich Erfahrung gehabt, oder 1, 3 und 4 und alle Sie entweder chaotisch, wobei es nicht klar ist, was die Daten wirklich kompliziert mit einer Art von weiche Kategorisierung zu brechen, die Daten in dynamische Arten der Aufzeichnung.

    Ich würde geneigt sein, zu versuchen, XML -, sollten Sie in der Lage sein, zur Durchsetzung schemas gegen den Inhalt der xml-Daten zu überprüfen usw eingeben, die helfen, halten Unterschied sets von UDF-Daten. In neueren Versionen von SQL server können Sie den index für XML-Felder, die helfen sollte, die sich auf die Leistung aus.
    (siehe http://blogs.technet.com/b/josebda/archive/2009/03/23/sql-server-2008-xml-indexing.aspx) zum Beispiel

    InformationsquelleAutor der Antwort Jon Egerton

  9. 2

    Wenn Sie SQL Server verwenden, übersehen Sie nicht die sqlvariant-Typ. Es ist ziemlich schnell und sollten Ihren job tun. Andere Datenbanken haben etwas ähnliches.

    XML-Datentypen nicht so gut aus performance-Gründen. Wenn Sie die Berechnungen auf dem server, dann bist du immer wieder deserialisiert diese.

    Option 1 schlecht klingt und sieht cruddy, aber leistungsmäßig kann Ihre beste Wette. Ich habe Tabellen erstellt mit den Spalten benannt Field00-Field99 vor, denn Sie können einfach nicht schlagen die Leistung. Müssen Sie möglicherweise zu prüfen, LEGEN Sie Ihre Leistung auch in dem Fall ist das auch den ein zu gehen. Sie können immer Blick auf diese Tabelle, wenn Sie wollen, dass es ordentlich Aussehen!

    InformationsquelleAutor der Antwort Tim Rogers

  10. 1

    Habe ich es geschafft diese sehr erfolgreich in der Vergangenheit mit keiner dieser Optionen (option 6? 🙂 ).

    Ich ein Modell erstellen, mit dem die Nutzer spielen mit (speichern als xml und setzen über eine eigene modelling tool) und aus dem Modell generierten Tabellen und-sichten zu verbinden, die Basis-Tabellen mit benutzerdefinierten Daten-Tabellen. Also, jeder Typ würde eine base-Tabelle mit den wichtigsten Daten und eine user-Tabelle mit den Benutzer definierten Feldern.

    Nehmen Sie ein Dokument als Beispiel: typische Felder werden name, Typ, Datum, Autor, etc. Dies würde in der core-Tabelle. Dann würden die Nutzer definieren Ihre eigene spezielle Dokument-Typen mit Ihren eigenen Bereichen wie contract_end_date, renewal_clause, blah, blah, blah. Für das benutzerdefinierte Dokument-es wäre die core-document-Tabelle, die xcontract Tisch, trat auf einen gemeinsamen Primärschlüssel (also die xcontracts primary key ist auch außen auf der primary-key der core-Tabelle). Dann würde ich das erzeugen einer Ansicht zu wickeln Sie diese in zwei Tabellen. Performance bei der Abfrage war schnell. weitere business rules können auch eingebettet werden in den Ansichten. Dies funktionierte wirklich gut für mich.

    InformationsquelleAutor der Antwort Kell

  11. 0

    SharePoint verwendet die option 1 und hat eine angemessene Leistung.

    InformationsquelleAutor der Antwort Nathan DeWitt

  12. 0

    In die Kommentare, die ich sah, Sie sagen, dass die UDF-Felder-dump importiert die Daten, die nicht richtig zugeordnet durch den Anwender.

    Vielleicht eine weitere option ist, um die Anzahl von UDF ' s von jedem Benutzer und Sie zu zwingen, um die Wiederverwendung von Feldern und sagen, dass Sie können verwenden Sie 6 (oder einige andere, ebenso zufällige limit) benutzerdefinierte Felder tops.

    Wenn Sie konfrontiert sind mit einer Datenbank Strukturierung problem wie dieser ist es oft am besten, um zurück zu gehen, um das grundlegende design der Anwendung (import-system in Ihrem Fall) und ein paar mehr Beschränkungen auf.

    Nun, was ich tun würde, ist die option 4 ("BEARBEITEN") mit dem Zusatz von einem link zum Benutzer:

    general_data_table
id
...


udfs_linked_table
id
general_data_id
udf_id


udfs_table
id
name
type
owner_id --> Use this to filter for the current user and limit their UDFs
string_link_id --> link table for string fields
int_link_id
type_link_id

    Nun stellen Sie sicher, um Ansichten um die Leistung zu optimieren und erhalten Sie Ihre Indizes Recht. Diese Ebene der Normalisierung macht die DB-footprint kleiner, aber Ihre Anwendung komplexer.

    InformationsquelleAutor der Antwort Wouter Simons

  13. 0

    Unserer Datenbank Kräfte eine SaaS-app (helpdesk-software), in denen die Benutzer über 7k "benutzerdefinierte Felder". Wir verwenden einen kombinierten Ansatz:

    1. (EntityID, FieldID, Value) Tabelle für Suche die Daten
    2. ein JSON-Feld in der entities Tabelle enthält alle entity-Werte, verwendet für anzeigen der Daten. (auf diese Weise brauchen Sie nicht eine million BEITRETEN, um die Werte-Werte).

    Könnten Sie weitere split #1, um eine "Tabelle pro Datentyp" wie diese Antwort schlägt vor, auf diese Weise können Sie auch einen index für das SEF.

    P. S. Paar Worte zur Verteidigung der "Entity-Attribut-Value" - Ansatz hält jeder bashing. Wir haben #1, ohne #2 Jahrzehnte, und es hat Prima funktioniert. Manchmal ist es eine unternehmerische Entscheidung. Haben Sie Zeit, um schreiben Sie Ihre app und die Neugestaltung des db-oder Sie können durch ein paar Dollar auf einem cloud-Server, die sind wirklich Billig in diesen Tagen? Übrigens, als wir mit #1 Ansatz unserer DB hielt Millionen von Entitäten, auf die Sie zugreifen, indem Sie 100s von tausenden von Nutzern und einer 16GB dual-core-db-server war eigentlich ganz gut (wirklich ein "r3" vm auf AWS).

    InformationsquelleAutor der Antwort Alex

Schreibe einen Kommentar