An update on this : Tim Johansson is not working anymore on this project, I am doing it for the GSoC :)
First, I notice that all the current proposed move/redirect solutions will always display the old link at the bottom of the article. I first saw category moves as a great opportunity to perform in core the category moves bot owners do every days on WM projects, most of the time to comply to a naming convention; however these current solutions do not allow this. Surely, clicking on the category link would redirect the user to the page with the standardized name, but we might want to directly follow the category redirect for display instead, dont you think ? (And this might be a $wgResolveCategoryRedirects global boolean)
Also, if we decide to turn cl_to into a cat_id, it will require us, when inserting a new category into an article, to fetch the cat_id corresponding to the title we got from the wikitext to update category_links.
- Change cl_to to two columns: cl_to_id and cl_final_id. cl_to_id
would contain the id of the category that it's actually included in, whereas cl_final_id would be the id of the category it's included in once all redirects are resolved.
- When querying what category something is in for the purposes of
category pages, etc., use cl_final_id, not cl_to_id.
- When changing an existing redirect (e.g., deleting it), or changing
an existing category into a redirect, just do UPDATE categorylinks SET cl_final_id=$newdestination WHERE cl_to_id=$changedcat. This part will be slow for large categories, perhaps unacceptably so for very large ones. This is comparable to deleting large pages at present and may need to be treated similarly.
I'm wondering why you need that cl_to_id. To show the closest membership of a category ? (If Category:A redirects to Category:B, be able to display on title=Category:A&redirect=no the pages that directly belongs to A ?) Do we need this ? I would say that knowing the final destination is enough ?!
And here again we have the "problem" of finding what cl_final_id is, knowing a title. We do need to fetch a row in the Page table, to know if this is a redirect. And if it is, a join between redirect and category is needed to get the cat_id
I am thinking of a table containing for each category page title the cat_id it refers to. When updating a page, a query joining that table and the category table could retrieve A) the cat_id needed to update category_links B) possibly the cat_title to display the "proper" links at the bottom of the article.