Xrhrft

My database attempts to describe some items, their sources and a variety of restrictions on who may use them as well as a number of intrinsic properties about the items.

It looks a little bit like:

There are 17 of these restriction tables and relationships. I put them there as some or none of them may apply to a particular item, in an almost arbitrary fashion.

Some of the restriction tables might have as few as 3 different rows where others are around 20-30. Not giants by any stretch. Each restriction is completely independent of the others.

The problem I have is that when I establish a filter to say "show me all the items not affected by restriction 1, 3, and 7" I wind up with rather ridiculous amounts of joins which I know I should avoid. What I think I'm doing is saying "gather all the items for which the restrictions provided by the user do not apply, gather all the items that should be included by checking each restriction relationship (left joins) and also gather all the possible items where no such restrictions exist at all (union)"

SELECT DISTINCT i.id, iwt.wlID, i.sdesc, iaft.affectID, iaft.amount, NULL AS affID FROM item i INNER JOIN itemRes1Table iwt ON i.id=iwt.itemID 

LEFT JOIN itemRes2Table iaft ON i.id=iaft.itemID

LEFT JOIN itemRes3Table igt ON i.id = igt.itemID 



... similar left joins for other restrictions ...



AND (igt.itemID IS NULL OR igt.gID = '.$_SESSION['gID']



... similar ands for other filters ...



UNION 

SELECT DISTINCT i.id, iwt.wlID, i.sdesc, NULL AS affectID, NULL AS amount, iafbt.affID FROM item i INNER JOIN itemRes1Table iwt ON i.id=iwt.itemID 



... repeated joins from above to get the items without the restriction ...

My limited research shows me that mySQL, which I'm working in, does not necessarily support automatically optimizing inefficient or unneeded joins, not that I'm certain it'd be helpful here anyway.

The list of available restrictions within a list very rarely update, but can be updated. I had considered doing something like storing a bit flag value in an integer within the item to eliminate the tables altogether and programatically calculating which restrictions were applied .... moving the logic out of the database and into the controller, but then filtering must also fall to the controller unless I can dream up a way to query if a value is stored within a stored integer (for example 2 is clearly in 2048 but not in 1023, power of 2 factors are valid and use and to join them bitwise)

I considered converting many to enums, but I'm not sure how to then include the idea that some or none of the enum values may apply, this seems exactly the problem relationships are trying to address.

I'm getting long in the tooth. I'm sure people have tackled this issue before, so even a point in the right direction on improving this monstrous looking schema would be appreciated.