worldcubeassociation.org
worldcubeassociation.org copied to clipboard
thewca
Delegate Table Restructuring
This is the draft PR to discuss the Delegate table restructuring. The following are the new proposed table structure. Please go through it and give your suggestions.
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 |
| country_iso2 | varchar(191) | Used to represent the country |
| location | varchar(191) | The location where the delegate will be delegating |
| 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 |
Regions Table (name: regions)
| Column name | Column type | Details |
|---|---|---|
| id | bigint(20) | Primary key |
| name | varchar(191) | Name of the region |
| friendly_id | varchar(191) | Friendly ID to easy access |
| parent_region | bigint(20) | Used as a foreign key to reference regions table |
| is_active | tinyint(1) | Whether the region is still active |
The migration files to create the tables are available in the commit, please check that as well.
(@moralsh was supposed to send this PR, but I'm doing this as he is busy right now) cc: @moralsh @Jambrose777 Related issue: Issue #4222
@campos20 sorry I missed to change to country_iso2. I've changed it now. I hope it looks good now.
@thewca/software-team Could you please review the above table? If everything is fine, me and @moralsh will start working on it soon.
Do we not also need an is_active column for the subregions? The concept was quite new when the PR was created, but if we rearrange Senior regions it may also be conceivable to rearrange subregions every few years.
With that change in mind, the regions and subregions tables look almost identical. What about merging them into one joint regions table with a column like parent_region to implement hierarchies? This would allow us to (very theoretically!) introduce more nesting levels than just "Senior Delegate" and "Regional Delegate" in the future, at the cost of having a recursive table structure.
If we decide to go down that path, we could also drop the subregion_id from the Delegates table and just leave the one region_id table. The way I see it, any given Delegate would only have one of these two columns filled out at a time anyways.
What do you think?
Do we not also need an
is_activecolumn for the subregions? The concept was quite new when the PR was created, but if we rearrange Senior regions it may also be conceivable to rearrange subregions every few years.With that change in mind, the
regionsandsubregionstables look almost identical. What about merging them into one jointregionstable with a column likeparent_regionto implement hierarchies? This would allow us to (very theoretically!) introduce more nesting levels than just "Senior Delegate" and "Regional Delegate" in the future, at the cost of having a recursive table structure.If we decide to go down that path, we could also drop the
subregion_idfrom the Delegates table and just leave the oneregion_idtable. The way I see it, any given Delegate would only have one of these two columns filled out at a time anyways.What do you think?
I think both of those changes would work out nicely!
The only case where my assumption would fail is when one Delegate implicitly handles two regions, i.e. when a Senior Delegate also acts as a Regional Delegate at the same time.
Does this happen? If no, are we confident enough that this will never happen in the future to structure the tables in a way (based on my suggestions) that definitely prevents this from being implemented in the website code?
The only case where my assumption would fail is when one Delegate implicitly handles two regions, i.e. when a Senior Delegate also acts as a Regional Delegate at the same time.
Does this happen? If no, are we confident enough that this will never happen in the future to structure the tables in a way (based on my suggestions) that definitely prevents this from being implemented in the website code?
Yes it happens, but that’s handled with the Delegate having 2 different entries (actually 3): they’d have one for specifically being a senior, one for being a regional, and one for being a full delegate.
Ah, I hadn't thought of it that way. Thanks for the explanation!
In that case, there are many similarities to the team_members table. Both tables are based on the user_id, there's a start date and an end date and the Delegate status corresponds to the "membership status" (regular/senior/leader). Might be worth unifying parts of this in the Ruby code models then!
In the long run, this could also help us by providing a more intuitive UI for the "Edit Teams" section of the website and then use that same overhauled, more intuitive UI for editing Delegate appointment. But that's future music anyways :D
I agree with Gregor's suggestions. I'll change the structure and update the commit in this PR soon.
I've removed the regions table from this PR, as I've included it in PR #8009.
Since we are changing from structure.sql, this PR doesn't have any relevant code now. Also, looks like delegates model is very similar to team_members model, hence I have created a GitHub issue to discuss the models of both Delegates and Memberships. On favor of that, I'm closing this PR.