Table SQL reliant... est-il préférable d'avoir une table de liaison, ou délimité de la colonne?

Ma base de données a deux tables, l'une contient une liste d'utilisateurs, de l'autre une liste de rôles. Chaque utilisateur appartient à un ou plusieurs rôles, et bien sûr, chaque rôle aura de multiples utilisateurs.

Je suis venu à travers deux façons de lier l'information. La première est d'ajouter une troisième table qui contient les ID des deux tables. Une simple jointure sera ensuite de retour à tous les utilisateurs qui appartiennent à un rôle, ou tous les rôles auxquels appartient un utilisateur. Cependant, comme la base de données augmente, les ensembles de données renvoyées par ces requêtes simples va croître de façon exponentielle.

La deuxième méthode est d'ajouter une colonne à la table des utilisateurs, dans lequel une liste délimitée par des virgules de rôles est stocké. Cela permettra d'éliminer la nécessité pour la troisième table de liaison, ce qui peut avoir un effet positif sur la croissance de base de données. L'inconvénient est que SQL n'a pas la capacité d'utiliser des listes délimitées. Le seul moyen que j'ai trouvé pour traiter de l'information, est d'utiliser une table temporaire et une fonction personnalisée.

Est la visualisation de mes plans d'exécution, le "tableau d'analyse de l'événement" est celui qui prend le plus de ressources. Il est logique que l'élimination d'un tableau à partir de l'équation serait d'accélérer les choses. La fonction prend moins de 1% des ressources.

Ces tests ont été réalisés sur une base de données avec moins de 20 dossiers. Comme la taille de la base de données augmente, les analyses de la table prendra plus de temps, donc peut-être de limiter eux est le meilleur choix.

Si à l'aide de la liste délimitée par des virgules est une bonne façon d'aller, pourquoi personne ne le fait?

Merci de me dire quel est votre méthode préférée (même si c'est différent de mes deux), et pourquoi.

Merci.

Ne jamais tester sur une petite quantité de données si vous vous attendez à une grande quantité pour être stocké. Les requêtes qui fonctionnent très bien sur des petites quantités de données sont souvent des chiens sur de grands ensembles de données. La reson youo obtenu l'analyse de la table est probablement que vous n'avez eu que 20 dossiers, bases de données n'ont pas tendance à utiliser les index, même si elles existent pour une table de petit.
Vous dites: "Toutefois, comme la base de données augmente, les ensembles de données renvoyées par ces requêtes simples va croître de façon exponentielle." Je ne peux pas imaginer comment cela pourrait être vrai, même si vous avez eu autant de rôles utilisateurs. Une requête de rôles à un utilisateur appartient à est limitée par le nombre d'utilisateurs, et inversement pour les utilisateurs ayant un rôle. Un produit croisé requête serait votre jeu de données au carré: un polynôme augmentation, pas une exponentielle.

OriginalL'auteur RichieACC | 2010-01-21

  1. 10

    Si vous avez une liste délimitée par des virgules, de trouver des utilisateurs avec un certain rôle va devenir très coûteux: effectivement, vous avez besoin de faire un balayage COMPLET de la table, et regarder toutes les valeurs de cette colonne, dans chaque ligne, en essayant de voir si elle contient un rôle donné.

    Un tableau distinct (normalisé, de nombreux de nombreux de la relation) est le chemin à parcourir, et avec une bonne indexation, vous n'aurez pas des scans complets qui se passe.

    par exemple: 

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

    (UserRoleId est facultatif, vous pouvez également avoir le PK être UserId+RoleId, je ne rentrerai pas dans le débat ici de substitution vs composé de touches ici)

    Vous aurez envie d'un index sur (UserId, RoleId) qui est UNIQUE, pour faire appliquer les pas de doublons. Cela aidera également à toutes les requêtes où vous êtes en essayant de voir si un utilisateur a un rôle spécifique (OÙ userId = x ET roleId = y)

    Si vous êtes à la recherche de tous les rôles d'un utilisateur, vous aurez envie d'un index sur nom d'utilisateur.

    À l'inverse, si vous cherchez tous les utilisateurs d'un rôle donné, a un indice sur roleId la vitesse. Si vous ne le faites pas cette requête, ou très rarement, alors ne pas avoir cet indice sera légèrement la vitesse d'exécution pour insérer/mises à jour, car il est une chose de moins à faire. C'est l'attention à l'équilibrage de la loi qui est à la base de données de paramétrage.

    OriginalL'auteur gregmac

  2. 8

    1. Une analyse de la table signifie que vous n'avez pas d'indices, ou de votre requête ne leur permet pas d'être utilisé. Dans une base de données de sécurité, il est rarement, sinon jamais avoir à télécharger l'ensemble de la liste des utilisateurs/rôles, à moins que ce soit pour un administrateur de l'application. Vous avez besoin de l'adresse dans votre conception.

    2. Délimité par des listes de violer la première forme normale (1FN) et presque toujours causer des problèmes dans le long terme. Qu'advient-il si vous souhaitez récupérer tous les utilisateurs dans un rôle particulier? Comment pouvez-vous écrire cette requête? Ne pas aller en bas de cette route. La normaliser.

    3. Si vous êtes à l'utilisation correcte des types de colonnes (c'est à dire pas un varchar(4000) ou varchar(max) partout), de l'espace disque ne devrait pas être un problème. Oui, il va grandir "de façon exponentielle" - et alors? Les bases de données sont bien à ce genre de mise à l'échelle. Sauf si vous essayez de l'exécuter sur un 10 gig de disque dur, ce n'est pas quelque chose à craindre. Et si vous sont essayer de l'exécuter sur un 10 gig de disque dur, vous avez probablement plus de questions à se soucier.

    Réponse courte: Ne pas utiliser une liste délimitée par des virgules. Normaliser.

    Un upvote pour vous. Délimité par des listes sont presque toujours mauvais, à moins que vous ne voudrez plus JAMAIS à récupérer un morceau de données (un cas très rare).

    OriginalL'auteur Aaronaught

  3. 6

    La première option. Il est appelé plusieurs-à-plusieurs table de jointure. Cela permettra d'effectuer des beaux-si vous créez l'index approprié.

    Ne pas aller avec le deuxième "denormalised' option.

    OriginalL'auteur Mitch Wheat

  4. 4

    Vous pouvez utiliser un tableau à part, ou vous pouvez revenir à des hommes des cavernes avec des ciseaux. Le choix est à vous.

    Il a laissé de son plan d'hébergement frais par table.
    ah... maintenant, c'est clair
    +1 pour l'homme des cavernes de l'imagerie
    Étiez-vous (comme moi) de les imaginer debout autour de lui avec de gros morceaux de roche dans la salle de serveur, à la recherche ennuyé?
    Assez bien oui, même si mon serveur chambre comme une véritable grotte.

    OriginalL'auteur Hogan

  5. 2

    Une table séparée est la voie à suivre, sinon, vous êtes en essayant de travailler autour de votre moteur de base de données. Un tableau distinct est correctement normalisés, - en général, comme une demande en expansion, le mieux c'est normalisé, le plus facile, vous le trouverez à travailler avec. Ce que greg a dit ci-dessus est également tout à fait raison.

    OriginalL'auteur Paddy

  6. 0

    Bien que je vous recommande fortement de la méthode normalisée que tout le monde est en train de suggérer. Je crois que le fait d'avoir un enum rôle du système vous permettra d'avoir un chiffre pour les "rôles" de la colonne et vous permettent d'éviter d'avoir à créer une autre table.

    Hey, @Eudaimonia! Merci pour la réponse! 7 ans après la publication de la question. Je n'ai même pas travailler pour cette compagnie en plus 🙂 Tout cela est certainement une option viable, il est une exigence que les rôles sont fixes. Alors que cela peut s'appliquer dans certains cas, il peut ne pas dans beaucoup d'autres. Dans le contexte particulier de cette question, il n'aurait pas été une réponse viable.
    haha j'ai eu envie de me répondre, je suppose. Merci pour les commentaires sur la réponse!

    OriginalL'auteur Lenon