The option "show_on" to display the field in the front- or backend or both, should also be a seperate column in the #__fields table so that you can filter that way. Right now it is part of the JSON params data. I would expect this to be something like the client ID.

See #12733 for the not used table columns.

As for using #__categories instead of #__fields_groups that was originally done like this, but raised issues as it needed special code in com_categories and other confusing things like many options which is offered by categories that are completely unused in fields.

Why can't we simply hide the unused options with a modified category form XML from com_fields for this? You can simply load a new form definition from /administrator/components/com_fields/models/forms/ which hides those options. I have no problem adding one or two conditionals to com_categories when we then are able to save several hundred lines of code for duplicating this. And saving a DB table wouldn't hurt either, especially since we have to add another one to have the category mapping.

This is already discussed and we decided to use a separate table

assigned_cat_ids which stores the cat IDs as a CSV string. That is unacceptable

Why that? This is supported in databases with the find_in_set function #12642.

Because 1) that is a MySQL specific function and does not work in other RDBMS and 2) this is a string function which needs to look through every single record in the table instead of a simple key lookup and 3) that is not a normalised data structure and thus unacceptable.

So you don't agree on the implementation on #12642? For me adding a table just for a category to field mapping was a bit of overhead.

No, I don't agree with that. There are reasons why RDBMS have indexes and keys and normalised data, some of which I listed above. Such a mapping table is not overhead, as you can see with the #__modules_menu and #__user_usergroup_map tables.

If this feature is more than a toy and is actually supposed to get wide adoption, then we have to do this properly and not fudge this already with the first implementation

Shouldn't be hard to do that and it solves the issue with the find_in_set. So not the worst thing to do indeed ?

I'm not against it, but who is volunteering to do it? As I don't have time in the next couple of days. Think we need to have it ready before the first alpha.

It doesn't necessary have to be before alpha. Alphas imho should be mostly feature complete, but the implementation can still change afterwards.
I may have some time next week to do it. Can't promise though.
If I remember right the assigned_cat_ids aren't used in a lot of places.

I disagree that this can be done after alpha. This is a pretty big change of the fundamental data model. An alpha to me means, that the data model is complete and the basic functions are implemented the way they should be. You might have to fix bugs and you might still be implementing additional features, but the base features and the data model need to be ready and we are not at that point yet.

I guess we can close this one as we have a pr.

Aye