Correct way of storing many-to-many SQL relation when intersection is often required?

Question

I have a growing list of machines, each connected to a different type of equipment and can do different things, and I'm trying to build some sort of queuing system for that. My first idea so far is to use a database table machines storing the available machines, from which I'll be able to select required machines based on a specified capabilities. So the table would look something like this:

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

Where the code would have a list of parameters, and execute a code searching for all of them. For example executing SELECT * FROM Machines WHERE equipment2=1 AND network1=1 AND network2=0 AND status LIKE 'FREE'.

This works in theory, but has two problems I would like to avoid:

1. Each field needs to be defined in the code, when adding a new column as well
2. Some fields are mutually exclusive. I.E I don't want a computer connected to network 2 to run some of the tests in some situations, and some equipment prevents the machine from doing some basic functionality (even those that don't require and equipment), so I must add in the code a specific exclusion for it. E.G. AND network2=0 above.

My next idea was to use a Junction Table. So the tables would be:

Machines: machineId machineName status

Equipments: equipemntId equipmentName exclusive

And then I'll have a junction table

MachinesEquipments: machineId equipmentId

But then if I want a machine which has equipment2 and access to network1, but doesn't have access to network2 (since it's marked exclusive), and is also FREE, it gets too complicated for me to know how to do it, or even if it's the correct way to...

Is this the correct way for that many-to-many? Which SQL pieces am I missing?

Answer 1

Your second, normalized version is the right way to go. You can resolve your queries using a having clause (perhaps with a where clause as well). For your example, the query is something like this:

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"

I don't know how network is stored in this model, but including it is probably just a simple tweak on this query.

For networks, you can add additional clauses such as:

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

to handle the networks.