Model for Delegates & Memberships

[danieljames-dj]

This GitHub issue is to discuss the model for Delegates & Memberships (memberships is same as existing team_members table, but with little optimizations and maybe a slightly better name).

Delegates Table (name: delegates)

Column name	Column type	Details
id	bigint(20)	Primary key
user_id	bigint(20)	Used as a foreign key to reference users table
status	varchar(191)	This will represent the status of the delegate - like 'trainee', 'junior', etc
region_id	bigint(20)	Used as a foreign key to reference regions table
start_date	date	Start date of this delegating status
end_date	date	End date of this delegating status
created_at	datetime	Date & time at which the row is created
updated_at	datetime	Date & time at which the row is last edited
country_iso2	varchar(191)	Used to represent the country
location	varchar(191)	The location where the delegate will be delegating
metadata	varchar(191)	This will be a stringified JSON which will have some optional metadata like in_probation, probation_start_date, probation_end_date, etc

The above structure is same as the structure discussed here, but I've added one more column metadata which currently have three optional data like in_probation, probation_start_date, probation_end_date, etc, but we can have much more in future like pending_dues, number_of_competitions_delegated, etc.

Memberships Table (name: memberships)

Column name	Column type	Details
id	bigint(20)	Primary key
user_id	bigint(20)	Used as a foreign key to reference users table
status	varchar(191)	This will represent the status of the membership - like 'member', 'senior_member', 'leader', 'vice_leader', 'chair', 'vice_chair', 'secretary', etc
team_id	bigint(20)	Used as a foreign key to reference teams table
start_date	date	Start date of this membership status
end_date	date	End date of this membership status
created_at	datetime	Date & time at which the row is created
updated_at	datetime	Date & time at which the row is last edited
metadata	varchar(191)	This will be a stringified JSON which will have some optional metadata like translation_locale, main_role, etc

The above structure is similar to current structure, but we don't have team_leader and team_senior_member columns, instead we have status column.

The metadata columns of both tables looks bit odd because they don't have a proper type, and JSON is not a type of relational DB. We might have to discuss how to approach the metadata column. There are many options:

1. Have it as a column in relational DB, type will be string and value will be stringified version of JSON.
2. Have all the metadata as separate columns in the same table. (this will make the number of columns too much for the tables)
3. Have a separate table in MySQL delegates_metadata (or memberships_metada) with columns delegate_id, key, value.
4. Have a NoSQL DB to store the metadata.

Please give your ideas on how to proceed with this.

[SAuroux]

Actually JSON is a supported data type in most relational databases these days, this also appears to be the case for MySQL: https://dev.mysql.com/doc/refman/8.0/en/json.html

But I think that having separate tables delegates_metadata and memberships_metadata as you have already listed as an option above would be a cleaner way of storing metadata as you have envisioned.

The same, however, also applies to the status columns in the tables in my view. I think that the status columns in both tables should be replaced by status_id and all available status values should be maintained in separate delegates_status and memberships_status tables.

[danieljames-dj]

Thanks @SAuroux for your input. I completely agree with your suggestions.

I've quickly created a diagram with the input and attaching it with this message.

@gregorbg can you please approve this design so that I can start working on the implementation?

[dunkOnIT]

Disregard this if you've already spoken about the design with Gregor - but it may be worth doing a meeting to talk through and approve the design.

[danieljames-dj]

@dunkOnIT sure will try to get some time of Gregor. Yesterday during dinner, had a very short discussion with Gregor on this and he suggested an idea similar to yours here (your idea was UI related, but Gregor's idea was the same thing but in model).

I've created a model after considering that as well:

I'll try to get some time from Gregor to discuss on this further.

[danieljames-dj]

I had a discussion with Gregor about the model during WC2023, and he gave me many improvement suggestions. I've applied those and here is the updated model:

@gregorbg Could you please confirm if I've done it in the way you suggested or did I miss anything? Once I get a yes from you, I'll start working on this. @viroulep I would like you too to have a look as you have given a lots of input in the Delegates & Regions model.

[gregorbg]

Now with some time after Worlds having passed, I have some additional thoughts about the implementation. Let me preface this post by saying that the overall idea and design is fantastic! :star_struck: Definitely a great job coming up with this :)

The overall structure is absolutely fine and you have my general Yes! I have a few very detailed / nit-picky thoughts about individual columns in specific tables. But the overall "big picture" does not need any more changes :+1:

1. I am unsure about the purpose of the status column in the roles table. Detailed information like "Is it a Senior Delegate or a Junior Delegate or (in terms of a rank) a (Standard) Delegate?" should definitely be answered by the delegate_metadata. That way, the status column suddenly boils down to "Is it a (functional) Delegate or not?" (where "functional Delegate" is meant as the overall function, in contrast to Team Member, or RO Member, or whatever). But the question "Is this person a (functional) Delegate" can also be answered by the fact that the metadata is of type delegate_metadata. Similar logic applies to "Team Member VS Team Senior Member VS Team Leader". That should go into teams_metadata. And the fact that the metadata is of type teams_metadata tells us that it's a (functional) Team Member. So that makes the status seem unnecessary. Are there any other arguments in favor of this column?
2. The friendly_id in the groups column should probably go to the metadata specific for Teams/Committees. Do Delegate Regions or Banned competitors or Regional Organizations have a friendly ID?
3. By contrast, the hidden property from the teams_metadata should (a) be called is_hidden to be congruent with is_active and (b) should probably live in the top-level groups table. There are a lot of non-TeamCommitteeCouncil-entities that should probably be hidden from the public.

[dunkOnIT]

Based on this model, it looks like it would be fairly straightforward to add memberships for other entities - for example, membership with an RO. Am I correct about that?

[gregorbg]

Yes, correct.

[danieljames-dj]

Thanks @gregorbg for reviewing it. Since this is very huge project, and since there can be significant time gap between every subtask (as I'll be busy some days in upcoming months), I'm planning to split this into as many subtasks as possible. I'll explain my short term action plan:

1. Create groups & roles tables with implementation of one group (Delegate Probations). I've already created the PR here. Delegate Probations looked like the easiest group, I took that for creating the tables.
2. Two parallel subtasks: 2.1. Insert all the delegate regions into groups and map delegates under region instead of senior delegate (similar to or almost same as what is done here). At this point, the UI remains same, except that in user edit, instead of senior delegate, region has to be selected. 2.2. Migrate councils to new structure and create 'Add role' UI with which a user can be added a role in council. In future, the 'Add role' UI will be used to add roles in delegates, teams and committees as well.
3. Extend 'add role' UI to add delegate roles, but the table structure don't change - still delegate details stay in users table.
4. Migrate active delegates to roles table with start date as '2100-01-01'.
5. Insert subregions into groups and enable 'Regional Delegate' appointment, but regional delegates & subregions won't be shown in the UI yet.
6. Implement UI to show regional delegates & subregions as well.
7. Use SQL queries to update start date of active delegates, update regions/subregions of every delegates and insert past delegates.

By the above steps, we will be done with 'Delegate History' project. There are definitely many more subtasks pending, but I'll plan them after implementing the above subtasks.