Store and search leveled categories with mysql json

Question

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.

Answer 1

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

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:

Please suggest your own structures you think would be most effective.

I will first suggest how I would do it:

Normalized schema

CREATE TABLE blacksmiths (
  blacksmith_id int unsigned auto_increment PRIMARY KEY,
  blacksmith_name varchar(300) NOT NULL,
  blacksmith_lvl int unsigned NOT NULL
);

CREATE TABLE items (
  item_id int unsigned auto_increment PRIMARY KEY,
  item_name varchar(300) NOT NULL, # or level1, level2, level3?
)

CREATE TABLE blacksmith_items (
  item_id int unsigned,
  blacksmith_id int unsigned,
  level unsigned int,
  PRIMARY KEY(item_id, blacksmith_id),
  KEY(blacksmith_id),
  CONSTRAINT item_id_fk  (item_id) REFERENCES items (item_id),
  CONSTRAINT blacksmith_id_fk  (blacksmith_id) REFERENCES blacksmiths (blacksmith_id),
);

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:

SELECT items.name FROM blacksmiths
JOIN blacksmith_items
USING (blacksmith_id)
JOIN items
USING (item_id)
WHERE blacksmiths.blacksmith_id = 1;

To get items with a specific level;

SELECT items.name FROM blacksmiths
JOIN blacksmith_items
USING (blacksmith_id)
JOIN items
USING (item_id)
WHERE blacksmiths.blacksmith_id = 1
AND blacksmith_items.level = 2;

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.