SQL-Tabelle zu verknüpfen... ist es besser eine Tabelle verknüpfen oder einer getrennten Spalte?

Meine Datenbank hat zwei Tabellen, eine enthält eine Liste von Benutzern, die andere eine Liste von Rollen. Jeder Benutzer gehört zu einer oder mehreren Rollen, und natürlich jede Rolle mehrere Benutzer.

Habe ich über zwei Möglichkeiten zum verknüpfen der Informationen. Die erste ist, um eine Dritte Tabelle enthält die ID aus beiden Tabellen. Ein einfacher join werden dann wieder alle Benutzer gehören einer Rolle oder alle Rollen, zu denen ein Benutzer gehört. Jedoch, da die Datenbank wächst, werden die Datensätze zurückgegeben, die durch diese einfache Abfragen wird exponentiell wachsen.

Die zweite Methode ist, um eine Spalte hinzuzufügen, um die Benutzer-Tabelle, in der eine durch Trennzeichen getrennte Liste von Rollen gelagert ist. Dies eliminiert den Bedarf für die Dritte Tabelle verknüpfen, die möglicherweise einen positiven Effekt auf die Vergrößerung der Datenbank. Der Nachteil ist, dass SQL nicht die Fähigkeit haben, verwenden Sie getrennte Listen. Die einzige Möglichkeit die ich gefunden habe, zum verarbeiten der Informationen ist die Verwendung einer temporären Tabelle und einer benutzerdefinierten Funktion.

Ist das betrachten meiner Ausführungspläne, die "table scan" - event ist die eine, die die meisten Ressourcen. Macht es Sinn, dass die Beseitigung einer Tabelle aus der Gleichung würde die Dinge beschleunigen. Die Funktion nimmt weniger als 1% der Ressourcen.

Diese tests wurden durchgeführt auf eine Datenbank mit weniger als 20 Datensätzen. Wie die Größe der Datenbank wächst, wird die Tabelle durchsucht wird länger dauern, so dass vielleicht begrenzen Sie ist die beste Wahl.

Wenn Sie das separierte Liste ist ein guter Weg zu gehen, warum ist niemand, es zu tun?

Mir bitte sagen, welche ist Ihre bevorzugte Methode sein (auch wenn es anders von meinen beiden) und warum.

Danke.

Nie testen Sie auf einer kleinen Menge von Daten, wenn Sie erwarten eine große Menge gelagert werden. Abfragen, die funktionieren auf kleine Datenmengen sind oft Hunde, die auf großen Datensätzen. Die reson youo habe die Tabelle scan war wohl, dass Sie hatten nur 20 Datensätze, Datenbanken, die nicht dazu neigen, Indizes verwenden, selbst wenn Sie existieren, für die eine Tabelle, die kleinen.
Sie sagen: "Aber, da die Datenbank wächst, werden die Datensätze zurückgegeben, die durch diese einfache Abfragen wird exponentiell wachsen." Ich kann mir nicht vorstellen, wie diese Aussage wahr sein könnte, auch wenn Sie hatte so viele Rollen wie Benutzer. Eine Abfrage der Rollen eines Benutzers ist begrenzt durch die Anzahl der Benutzer; Umgekehrt ist für Benutzer, die eine Rolle spielen. Ein Kreuzprodukt query wäre dein dataset im Quadrat: ein Polynom erhöhen, nicht zu einem exponentiellen.

InformationsquelleAutor RichieACC | 2010-01-21

  1. 10

    Wenn Sie eine durch Trennzeichen getrennte Liste für die Suche nach Benutzern mit einer bestimmten Rolle wird sehr teuer geworden: effektiv, müssen Sie einen VOLLSTÄNDIGEN scan der Tabelle, und schauen Sie sich alle Werte dieser Spalte in jeder Zeile, jeder versucht, um zu sehen, wenn es mit einer bestimmten Rolle.

    Einer separaten Tabelle (normalisiert, viele zu viele relation) ist der Weg zu gehen, und mit der richtigen Indizierung werden Sie nicht haben vollständige scans passiert.

    zB: 

    User:  UserId, Name, ....
Role:  RoleId, Name, ....
UserRole:  UserRoleId, UserId, RoleId

    (UserRoleId ist optional, kann man auch die PK sein, UserId+RoleId, werde ich nicht in die Diskussion hier von Surrogat-vs-compound-keys hier)

    Möchten Sie einen index auf (UserId, RoleId), die EINZIGARTIG ist, zu erzwingen, keine Duplikate. Dies wird auch helfen, mit Fragen, wo Sie versuchen, um zu sehen, ob ein bestimmter Benutzer eine bestimmte Rolle (WHERE userId = x UND roleId = y)

    Wenn Sie schauen, bis alle Rollen, die ein Benutzer hat, werden Sie wollen, ein index, der nur Benutzer-id.

    Umgekehrt, wenn Sie schauen, bis alle Benutzer eine bestimmte Rolle hat, ein index, der nur roleId wird die Geschwindigkeit bei, bis. Wenn Sie nicht tun, diese Abfrage, oder tun Sie es sehr selten, dann nicht mit diesem index wird die Arbeitsgeschwindigkeit etwas für insert/updates, denn es ist eine Sache weniger zu tun. Dies ist die sorgfältige Gratwanderung, das Datenbank-tuning.

    InformationsquelleAutor gregmac

  2. 8

    1. Einen table scan bedeutet, dass Sie don ' T haben keine Indizes, oder die Abfrage nicht zulässt, dass Sie verwendet werden. In einer Sicherheits-Datenbank sollten Sie nur selten, wenn überhaupt zum download der gesamten Liste von Benutzer/Rollen, es sei denn, dies ist für einen admin-Anwendung. Sie müssen diese Adresse in Ihrem design.

    2. Getrennte Listen gegen first-normal-form (1NF) und fast immer zu Problemen in der langfristig. Was passiert, wenn Sie abrufen möchten, und alle Benutzer in einer bestimmten Rolle? Wie schreibst du die Abfrage? Don ' T gehen diesen Weg. Normalisieren es.

    3. Wenn Sie mit den richtigen Spaltentypen (also nicht varchar(4000) oder varchar(max) überall), der Speicherplatz sollte das wirklich kein Problem sein. Ja, es wächst "exponentiell" - so what? Datenbanken sind gute bei dieser Art von Skalierung. Es sei denn, Sie versuchen, führen Sie diese auf eine 10 GB Festplatte, es ist nicht etwas zu befürchten. Und wenn Sie sind versuchen, führen Sie es auf einer 10 GB Festplatte, werden Sie wahrscheinlich haben größere Probleme zu befürchten.

    Kurze Antwort: verwenden Sie eine durch Trennzeichen getrennte Liste. Normalisieren sich.

    Ein upvote für Sie. Getrennte Listen sind fast immer schlecht, es sei denn, Sie wird NIE wollen, um eine einzelne Stück, dass die Daten (ein sehr seltener Fall).

    InformationsquelleAutor Aaronaught

  3. 6

    Die erste option. Das nennt man eine viele-zu-viele join-Tabelle. Dies führt in Ordnung, wenn Sie erstellen die entsprechenden Indizes.

    Gehen nicht mit der zweiten, der "denormalised" - option.

    InformationsquelleAutor Mitch Wheat

  4. 4

    Könnte man eine separate Tabelle, oder Sie können gehen Sie zurück zu Höhlenmenschen mit Meißel. Die Wahl ist bis zu Ihnen.

    Er ließ heraus, dass seine hosting-plan, Kosten pro Tisch.
    ah... jetzt ist es klar
    +1 für die caveman Bilder
    Waren Sie (wie ich) vorstellen, Sie stehen, um mit großen Felsbrocken in den server-Raum, suchen langweilig?
    Ziemlich viel, ja, auch wenn meine server-Raum als eine wirkliche Höhle.

    InformationsquelleAutor Hogan

  5. 2

    Einer separaten Tabelle ist der Weg zu gehen, sonst bist du versuchen zu arbeiten, um Ihre Datenbank-engine. Eine separate Tabelle ist korrekt normiert, der im Allgemeinen als eine Anwendung erweitert, die besser ist es sich normalisiert, desto leichter finden Sie es, mit zu arbeiten. Was greg gesagt oben ist auch absolut Recht.

    InformationsquelleAutor Paddy

  6. 0

    Obwohl ich würde empfehlen, die normalisierte Methode, dass jeder suggeriert. Ich glaube, dass eine enum-basierte Rolle-system würde es ermöglichen, dass Sie eine Ziffer für die "Rollen" - Spalte, und lassen Sie Sie zu vermeiden, erstellen Sie eine andere Tabelle.

    Hey, @Eudämonie! Danke für die Antwort! 7 Jahre nach der Veröffentlichung der Frage. Ich weiß gar nicht für das Unternehmen arbeiten mehr 🙂 Zwar ist dies sicherlich eine sinnvolle option, es ist eine Anforderung, die Rollen sind festgelegt. Dies mag zwar gelten in einigen Fällen kann es nicht in viele andere. In dem besonderen Kontext dieser Frage, wäre es nicht möglich gewesen, eine tragfähige Antwort.
    haha ich spürte den Drang, zu reagieren, nehme ich an. Vielen Dank für das feedback auf die Antwort!

    InformationsquelleAutor Lenon

Schreibe einen Kommentar