As discussed in today's engineering meeting, we would like to simplify the ce_organizers table so that certain operations like limit, count, and pagination are easier.
We currently store tuples of (event, user, role), so there can be multiple rows for the same event+user combination, one for each role. The new proposal is that we make the "roles" column a bit set, and store all roles in a single row.
The motivations for this are:
- Limit, count, and pagination would be much easier
- We don't need timestamps (created_at, deleted_at) for each role
- Rename the column
- Remove it from the index
- Adjust logic in OrganizersStore
- Write the patch that changes the schema in the code. Review it, but DO NOT MERGE IT. Only give it a +1, explicitly writing in a comment that the +1 means you are approving the change (and not that you have a working mouse).
- Merge the patch and wait for it to reach beta
- Immediately rename the column on beta
ALTERs to run on beta
Before merging the patch: nothing. Note that DB errors are expected in the short period between when the patch reaches beta and the queries below are run.
After merging the patch (noting that beta runs MariaDB 10.6, so we can use RENAME COLUMN):
ALTER TABLE ce_organizers DROP INDEX ceo_event_user_role; ALTER TABLE ce_organizers RENAME COLUMN ceo_role_id TO ceo_roles; ALTER TABLE ce_organizers ADD UNIQUE INDEX ceo_event_user (ceo_event_id, ceo_user_id);