Feature Request - Normalise muc_room affiliations

Feature Request

As you know, affiliations to a MUC room are stored within the 'opts' field of table muc_room like this:

 {affiliations,[{{<<"user89">>,<<"mydomain.org">>,<<>>},{member,<<>>}},
                {{<<"testuser">>,<<"mydomain.org">>,<<>>},{member,<<>>}},
                {{<<"richard">>,<<"mydomain.org">>,<<>>},{member,<<>>}},
                {{<<"rich">>,<<"mydomain.org">>,<<>>},{member,<<>>}}]},

My feature request is for room affiliations to be stored in a separate table instead. I have two main reasons for requesting this.

Firstly. It helps people who are tightly integrating ejabberd with other systems. For example, at the moment I can replace muc_registered with a view to another database. I do the same with 'rosterusers'. These are clean hacks that remove a whole class of integration complexity. Unfortunately, the current database schema does not allow the same trick with MUC affiliations - because these are stored within a text field as above. It would be tricky to directly manipulate this data using external tools so I must go via the API which is comparatively slow and adds system complexity.

Secondly. I am less certain about this - but the current database schema looks inefficient. It does not look scalable or resilient to store such critical information formatted like this directly into a text field of the database. At very least it is a horror show in terms of data normalisation and prevents database level validations / integrity rules. I am hoping to have tens of thousands of people affiliated to some muc rooms (even though the room would have a much smaller limit on concurrent participants in the room). The current schema appears to be a frightening way to store this amount of data.

It looks like a legacy thing and I wonder if a little refactoring was possible? I have zero Erlang and am unable to understand the code changes that would be needed.

What version of ejabberd are you using?
17.04

What operating system (version) are you using?
Alpine Linux

How did you install ejabberd (source, package, distribution)?
Source

What did not work as expected? Are there error messages in the log? What
Working as expected - this is a feature request.

was the unexpected behavior? What was the expected result?
It was unexpected to find affiliations stored in a text field!!

ps. Recent updates have been terrific, the new API is a massive step forward.. thanks