Quelle est la bonne façon de stocker une relation SQL many-to-many lorsque l'intersection est souvent requise ?

Question

J'ai une liste croissante de machines, chacune connectée à un type d'équipement différent et pouvant faire différentes choses, et j'essaie de construire une sorte de système de file d'attente pour cela. Ma première idée jusqu'à présent est d'utiliser une table de base de données machines stockant les machines disponibles, à partir desquelles je pourrai sélectionner les machines requises en fonction de capacités spécifiques. Le tableau ressemblerait donc à ceci :

machineId machineName status equipment1 equipment2 network1 network2 1 Test FREE 0 1 1 0

Où le code aurait une liste de paramètres, et exécuterait un code en les recherchant tous. Par exemple, en exécutant SELECT * FROM Machines WHERE equipment2=1 AND network1=1 AND network2=0 AND status LIKE 'FREE' .

Cela fonctionne en théorie, mais présente deux problèmes que je voudrais éviter :

1. Chaque champ doit être défini dans le code, lors de l'ajout d'une nouvelle colonne également.
2. Certains domaines s'excluent mutuellement. Par exemple, je ne veux pas qu'un ordinateur connecté au réseau 2 effectue certains tests dans certaines situations, et certains équipements empêchent la machine d'effectuer certaines fonctionnalités de base (même celles qui ne nécessitent pas d'équipement), je dois donc ajouter dans le code une exclusion spécifique pour cela. PAR EXEMPLE AND network2=0 ci-dessus.

Mon idée suivante était d'utiliser une table de jonction. Donc les tables seraient :

Machines: machineId machineName status

Equipments: equipemntId equipmentName exclusive

Et puis j'aurai une table de jonction

MachinesEquipments: machineId equipmentId

Mais alors si je veux une machine qui a equipment2 et l'accès à network1 mais n'a pas accès à network2 (puisqu'il est marqué exclusif), et est également FREE je ne sais pas comment m'y prendre, ni même si c'est la bonne façon de faire...

Est-ce que c'est la bonne façon de procéder pour le many-to-many ? Quels sont les éléments SQL qui me manquent ?

Answer 1

Votre deuxième version, normalisée, est la bonne. Vous pouvez résoudre vos requêtes en utilisant un having (éventuellement avec une clause where également). Pour votre exemple, la requête est quelque chose comme ceci :

select m.machineid
from machines m join
     machinesequipment me
     on me.machineid = m.machineid join
     equipment e
     on me.equipmentid = e.equipementid
where m.status = 'free'
group by m.machineid
having sum(e.equipmentname = 'equipment1') > 0 and   -- has "equipment1
       sum(e.equipmentname = 'equipment2') = 0;      -- does not have "equipment2"

Je ne sais pas comment network est stocké dans ce modèle, mais l'inclure n'est probablement qu'une simple modification de cette requête.

Pour les réseaux, vous pouvez ajouter des clauses supplémentaires telles que :

sum(e.equipmentname = 'network1') > 0

pour gérer les réseaux.