Query to select b where b.id is in comma separated list of a

Question

Update: Component Code (not normalized) can be found here: Link

For my First J4 Component i've now implemented Multi Cat Option by saving the selected categories into a comma separated list (text) into the database. On edit it works so far. In the List of Items i would also like to show the selected Categories for each item. The queries i've tried so far are all not working. Is it correct that for the IN operator the comma-separated list has to be exploded inside the query, right? Unfortunately it is not working as expected because of my mmisunderstanding on how to correctly build a subquery for this task: a table ==> #__extension b table ==> #__categories

a.categories contains comma separated list of id's from b table

a.id   | a.categories   |     | cats.id    | cats.title
------------------------      -------------------------
1      |1,2,3           |     | 1          | category A
2      |2,3             |     | 2          | category B
...                           | 3          | category C
                               ....

// inside my getListQuery function of my Model:

// Select the required fields from the table.
        $query->select(
            $db->quoteName(
                [
                    'a.id', 'a.name', 'a.alias', 'a.access',
                    'a.catid','a.categories','a.published', 'a.publish_up', 'a.publish_down',
                    'a.language'
                ]
            )
        );

...
$categoriesSubQuery = $db->getQuery(true)
 ->select($db->quoteName('cats.title'))
 ->from($db->quoteName('#__categories', 'cats'))
 ->where( $db->quoteName('cats.id') . ' IN (' . $db->quoteName('a.categories') . ')');

$query->select('(' . $categoriesSubQuery . ') AS ' . $db->quoteName('categoriesArr'));

...

Gives me Only the First item from the categories field (var_export):

     'name' => 'ITEM A',
     'alias' => 'a',
     'access' => 1,
     'catid' => 0,
     'categories' => '19,20',
     'published' => 1,
     ...
     'categoriesArr' => 'CATEGORY A',
     ...

I understand that the SubQuery could be the solution to "get all Categories from comma separated list of category id's" but i simply dont know how.

Main Goal is to have an Array of Objects / Strings of each category

Update: So based on the comments i'm on the way to normalize my structure and implement a #__component_item_category table including id,item_id,category_id - now in the Item Model i need to do the following step by step, is it ok to do so?

$categories = $this->categoriesToArray($categories);

        $db = Factory::getDbo();
        $query = $db->getQuery(true);

// Get all registered categories for this item from normalized table $array

// unset all still given/found categories from $array foreach $array

// check for leftover categories and delete them (if array > 0) foreach where itemid and category  (or IN?) $db->delete

// if categories > 0 --> Store the new categories (foreach)

Is it OK to do that all (db queries) in the model? i don't think so - a public function in the ItemTable Class would be better and implement class by use statement and a call of this function right?