Est-il possible de créer des champs parallèles en SQL ?

Question

J'ai un tas de X dans une base de données MySQL. Certains X ne vont pas ensemble, et je veux une table qui contienne toutes les paires de X et un booléen qui dise si elles sont correctes ou non.

Cette partie est assez simple :

CREATE TABLE good_x (
    X_id_1 int(10) not null ,
    X_id_2 int(10) not null ,
    is_good tinyint(1) 
    ) ;

Ma question est la suivante : Étant donné que Xsub1 et Xsub3 vont ensemble.

SELECT is_good from good_x where X_id_1 = "Xsub1" and X_id_2 = "Xsub3" ;

Mais qu'en est-il si les données arrivent en Xsub3, Xsub1 ? Je pourrais avoir deux entrées dans le tableau :

Xsub1,Xsub3,1
Xsub3,Xsub1,1

Mais il faut alors tenir compte de deux choses. Dans mon cas, il s'agit d'un vocabulaire contrôlé, je ne m'attends donc pas à beaucoup de changements. Mais si je veux ajouter un troisième X, Xsub5, vous avez alors six entrées à gérer.

Xsub1,Xsub3,Xsub5,1
Xsub1,Xsub5,Xsub3,1
Xsub3,Xsub1,Xsub5,1
Xsub3,Xsub5,Xsub1,1
Xsub5,Xsub1,Xsub3,1
Xsub5,Xsub3,Xsub1,1

Ce qui est difficile à maintenir.

Existe-t-il un moyen d'ajouter une série de X, deux ou plus, et de les associer au booléen, de sorte que je n'aie pas à avoir des tables séparées pour les doubles, les triples, les quadruples, les quintuples, etc. Est-ce possible dans n'importe quel SQL, et si oui, quelle est la syntaxe ?

Answer 1

Stockez chaque groupe valide dans un tableau comme celui-ci :

CREATE TABLE good_x (x_id CHAR, group_id INT);

Il faut l'alimenter avec des regroupements comme celui-ci :

INSERT INTO good_x(x_id,group_id) values ('Xsub3',1);
INSERT INTO good_x(x_id,group_id) values ('Xsub1',1);

et pour 3 :

INSERT INTO good_x(x_id,group_id) values ('Xsub3',2);
INSERT INTO good_x(x_id,group_id) values ('Xsub1',2);
INSERT INTO good_x(x_id,group_id) values ('Xsub5',2);

Exécutez maintenant un SELECT comme celui-ci :

SELECT 1
FROM   good_x
WHERE  x_id IN ([your list of values])
GROUP BY group_id
HAVING count(1) = n;

Answer 2

Je pense que vous pouvez utiliser la clause IN.

SELECT is_good 
  from good_x 
 where X_id_1 in ("Xsub1","Xsub3","Xsub5")
   and X_id_2 in ("Xsub1","Xsub3","Xsub5") 
   and X_id_3 in ("Xsub1","Xsub3","Xsub5") ; 

Et si vous ne voulez pas les mêmes valeurs dans différentes colonnes, vous pouvez ajouter

   and ( X_id_1 <> X_id_2
   and   X_id_1 <> X_id_3
   and   X_id_2 <> X_id_3 )

Bien entendu, cette solution n'est pas idéale lorsque vous avez trop de colonnes ou de valeurs différentes.

Answer 3

Si vous classez vos X d'une manière ou d'une autre (par exemple, en fonction de leur PK numérique artificiel), vous ne devrez stocker qu'un seul ensemble de X, et non toutes les permutations. Le coût de cette solution serait la nécessité d'ordonner les X avant la requête.

En revanche, la requête "avec quoi X est-il compatible ?" sera plus coûteuse (il faudra 2 index et 2 requêtes).

Vous pouvez utiliser une solution tout à fait générale, comme celle-ci :

create table compatible (
  group_id number not null,
  x_id number foreign key references x(id),
  primary key (group_id, x_id)
);
create unique index ... on compatible(x_id, group_id)

Pour enregistrer le fait qu'un nombre quelconque de X sont compatibles, vous créez autant d'enregistrements dans la base de données compatible avec la même table arbitraire group_id . Vous pouvez déterminer efficacement si certains éléments sont compatibles en interrogeant leur group_id et vérifier s'ils correspondent (utiliser exists ). Vous pouvez trouver efficacement tous les éléments compatibles avec un X particulier. Vous n'êtes pas contraint par la taille du groupe d'éléments compatibles et vous ne vous souciez pas des permutations.

Answer 4

Une bonne solution dépend un peu de la nature de la relation is_good et des valeurs X.

Si les valeurs X peuvent être ordonnées, vous pouvez utiliser l'ordre pour stocker une relation par paire avec un seul enregistrement au lieu de deux. Il suffit de stocker min(x1, x2) en x_id_1 y max(x1, x2) en x_id_2 à chaque fois que vous l'insérez. Utilisez ensuite la même approche lorsque vous interrogez les paires : select ... where x_id_1 = min(x_sub_1, x_sub_2) and x_id_2 = max(x_sub_1, x_sub_2) .

Si le is_good est symétrique et transitive, et s'il existe un ensemble relativement restreint et stable de valeurs X, une autre approche pourrait consister à utiliser la logique bit à bit. Chaque bit représente une valeur X distincte, et chaque enregistrement du tableau contient un schéma binaire qui indique que tous les bits X ayant une valeur "1" se trouvent dans un ensemble is_good Les relations entre les deux parties sont très étroites. L'interrogation consisterait simplement à trouver l'enregistrement avec les bits appropriés : select ... where x_bitfield & my_query_bitfield = my_query_bitfield .

Answer 5

La solution la plus simple, en utilisant votre conception actuelle, est de s'assurer que lorsque vous les stockez dans les paires de la base de données, vous les stockez dans l'ordre.

Par exemple, pour les paires "Xsub1" et "Xsub3", les stocker dans l'ordre alphabétique. Ensuite, lorsque vous les recherchez, vous devez les classer par ordre alphabétique dans la base de données WHERE clause comme celle-ci :

SELECT * FROM
good_x
WHERE x_id_1 = 'Xsub1'
AND x_id_2 = 'Xsub3'

Cela évitera les doublons et ne nécessitera qu'un minimum de traitement préalable.

Je pense que la solution de BD est meilleure, si vous ne voyez pas d'inconvénient à une refonte.