It seems like this one surely needs some splitting out if we aim for 5 storypoints per ticket.

In T387957#10612851, @KSiebert wrote:

It seems like this one surely needs some splitting out if we aim for 5 storypoints per ticket.

Hmmm I had assumed that actually creating the tables was easy, but maybe it's more complex in the context of a MW extension?

The tables have already been created, but we might need to adjust the schema some.

This is asking for a very different schema then the one we already have https://gerrit.wikimedia.org/r/plugins/gitiles/mediawiki/extensions/CommunityRequests/+/refs/heads/master/sql/ and it's also missing a bunch of fields.

I think the only changes we need are the new translations table, and perhaps a few tweaks to existing tables.

audience is a free-form entry field (i.e. not from a pre-configured list), so it needs to be a binary field, I guess living in the translations table since it's meant to be translated.

Following the discussion at T388219, it seems we want to DROP the community_requests_votes table, and instead ALTER community_requests_focus_areas to have a crfa_vote_count (int).

In the current schema we're storing the actor id, in the proposed schema we're not ... do we need it? We can get who created the wish from the initial revision of the wish page itself, I think

In T387957#10623407, @Cparle wrote:

In the current schema we're storing the actor id, in the proposed schema we're not ... do we need it? We can get who created the wish from the initial revision of the wish page itself, I think

Yes, it is a field in the old schema and is shown on the wish page. Staff can also change the wish proposer, for example when they import wishes from an older wishlist.

Regarding description and audience, do we need to store those? They're only ever displayed on the wish page itself, so if we wanted to keep things as small as possible they could just be left to the wish template to handle. (For focus areas, we've got description and short_description, with the latter being the only one that's displayed elsewhere; perhaps we'd do something similar for wishes if we do want to show that content elsewhere because the description can be big and contain headers, images etc.).

title and other_project do get shown on the wish tables, so they're needed.

In T387957#10626890, @MusikAnimal wrote:

In T387957#10623407, @Cparle wrote:

In the current schema we're storing the actor id, in the proposed schema we're not ... do we need it? We can get who created the wish from the initial revision of the wish page itself, I think

Yes, it is a field in the old schema and is shown on the wish page.

Yeah, that's why I asked ... we have a foreign key to page in community_requests so we can get the actor with something like select rev_actor from revision where rev_page=<community_requests.cr_page_id> and rev_parent_id=0

Staff can also change the wish proposer, for example when they import wishes from an older wishlist.

Ah ok - so if Jack (for example) imported an older the wish then the initial revision of the wish page itself would have Jack's actor id? Ok, I guess we need to keep the actor id then, added it to the schema

(found an example here - https://meta.wikimedia.org/wiki/Community_Wishlist/Wishes/Quickly_Add_Infobox)

Regarding description and audience, do we need to store those?

Yeah I guess not if we're only showing them on the wish page. Removed

cr_id int PK
cr_page_id int ... FK to tbl page

The current schema has cr_page (int) which doubles as the foreign key to page and the primary key, since it's already guaranteed to be unique. I'm not certain if that's ideal or if we want to add a new autoincrementing primary key, or maybe it doesn't matter?

cr_focus_area_page_title string ... this links to tbl community_requests_focus_areas via tbl page (see T388207)

community_requests_focus_areas unlike community_requests does have a primary key, so here we can use that. The current schema uses cr_focus_area (int) which corresponds to community_requests_focus_areas.crfa_id. Having the field be the focus area page ID is also fine, but again raises the question of if we need primary keys for tables that refer to a page.

Either way in the database we certainly don't want to store references to pages by their title, since those can change (as well as the unnecessary storage footprint). It should use only the page ID and JOIN on page to get whatever other data is needed.

There other small differences from the current schema that I assume are unimportant and don't need to be changed, such as cr_page_id vs cr_page. I would generally prefer the former; I only refrained from using the _id suffix in foreign keys to follow MW Core "convention", i.e. actor.actor_id and revision.rev_actor, etc.

Other changes aren't mentioned here but that's fine as they are implied, such as dropping community_requests.cr_audience.

Minor detail, but community_requests_wish_page_translation could perhaps be shortened to just community_requests_translations (plural form to match the other tables).

All the above seems reasonable ... I can't remember the reason why I used title instead of the page_id, maybe it was so we wouldn't have to join page to get the title for making the link. Do you remember @Samwilson ?

I can't remember, but perhaps it was in case the focus area page doesn't exist yet? But in that case, we'd just not store anything. I think storing the page ID is the way to go.

Updated to use page id

Change #1139950 had a related patch set uploaded (by MusikAnimal; author: MusikAnimal):

[mediawiki/extensions/CommunityRequests@master] sql: update schema to include translations, remove votes table

https://gerrit.wikimedia.org/r/1139950

Change #1140077 had a related patch set uploaded (by MusikAnimal; author: MusikAnimal):

[mediawiki/extensions/CommunityRequests@master] WishlistIntake: add config settings for projects, wish type, and status

https://gerrit.wikimedia.org/r/1140077

Change #1139950 merged by jenkins-bot:

[mediawiki/extensions/CommunityRequests@master] sql: redesign schema, starting with a clean slate

https://gerrit.wikimedia.org/r/1139950

Change #1140077 merged by jenkins-bot:

[mediawiki/extensions/CommunityRequests@master] WishlistIntake: add config settings for projects, wish type, and status

https://gerrit.wikimedia.org/r/1140077