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.
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.
I may have removed too much detail to be clear so here is a concrete example. Though this is for a video game it is a problem I've had in other designs.
Object 'a golden earring named Wind Whisperer' It is a level 50 armor, weight 3. Locations it can be worn: ears Special properties: magic metal Races allowed: Human Elf Dwarf Halfling Gith Drow Sea-Elf Gnome Genres allowed: rogue aberrant This armor has a gold value of 235233. Armor class is 15 of 15. Affects mana by 20. Affects hp by 20. Affects damage roll by 3. Affects hit roll by 3. Affects save vs breath by -1. Affects cost of vindur gong by -5. Affects intelligence by 1.
Working from memory on the sizes of choices here, but relatively close.
A character in the game has a race(1 of 19), class(1 of 13), gender(1 of 3) and alignment value (1 of 3). Classes are categorized into genres, and it is possible that they could be arbitrarily reogranized (about 6 categories). There are other restrictions but these would be the most common.
The typical use case is that a user is trying to select equipment for their character. They choose see all available items for a particular wear location (some items may be worn in multiple locations from a choice of 20ish) either unfiltered or with some arbitrary combination of filters.
Items have properties such as where it is found (may be multiple sources), restrictions, affects (things that modify character statistics by a value) or are affected by properties (bestow the affect to the character in a yes/no sort of way). The list of affects number around 40-50 and affected by properties is around 30 or so. Both lists are relatively frequently updated. This doesn't appear in my sample schema but the relationship of each of these is 1 to 0..*.