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.
Best Answer
I find this paradoxical- searching by a multi-valued field IS one of the reasons to apply normalization, as it simplifies a lot the searches. You can do what you mention, but without extra information that justifies that decision, that is not the simplest approach from a pure relationship model. Because you say:
I will first suggest how I would do it:
Normalized schema
I don't get exactly how levels come into play- if it is a property of blacksmiths or items, but you can modify the join table to make it part of the primary key or not, and/or modify the indexes to search them faster. Or maybe the items columns have to be separated into metal, name and type, etc.
To get all items for blacksmith id1:
To get items with a specific level;
Using joins is not really that complicated, and it will allow you more flexibility when you change the number of levels, or the number of items, and you can generate a json output from your application code.