I have leveled category triplets column, that tag the specific blacksmith with items he can produce:
blacksmith_id (primary) | blacksmith_name (varchar) | item_tags [json] | blacksmith_lvl (int)
Single tag structure looks like this:
Level 1 (category) : Level 2 (sub-category) : Level 3 (sub-sub-category)
For example, blacksmith with id 1 can produce following items:
Iron:daggers:curved
Iron:daggers:poisoned
Steel:daggers:curved
Golden:swords:curved
... etc
Each blacksmith can be tagged with multiple item tags.
I chose to go with JSON since item data that blacksmith produces is just informative, and does not have any specific value other than finding the specific blacksmith by his skills, neither does it change. So i see no use in creating multiple joined tables with row for each item that blacksmith uses.
What would be the best (time / resources) way to store and later search on by specific category levels to get list of blacksmits using mysql json.
My ideas so far:
1. Using WHERE JSON_CONTAINS
["iron:daggers:curved","iron_daggers:poisoned","steel:daggers:curved"]
In this case the only option would be to search for complete item hierarchy Level1:Level2:Level3
2. USING WHERE JSON_EXTRACT
{"i0":{"lvl1":"iron","lvl2":"daggers","lvl3":curved},"i1":{"lvl1":"iron","lvl2":"daggers","lvl3":"poisoned"}}
For searching just on specific levels, but i dont know if it is possible to iterate through all items without specifying the key (i0, i1) to find for example just blacksmiths which lvl2's are daggers and how time consuming this is for mysql.
Please suggest your own structures you think would be most effective.