[EE6] Channel entry status organization is inefficient and is easily broken

[PaulBaileyAtChan]

EE6 stores channel entry status in several places. The statuses themselves are stored in the exp_statuses table, with columns for status_id, status (name), status_order, and highlight (colour).

Each channel entry stores its status in the exp_channel_titles table. Both status (name) and status_id are stored. Storing the status name here, in addition to the ID, might be convenient in some circumstances, but it's not efficient, and it results in channel entry statuses being easy to break.

For example, changing the name of a status (in the exp_statuses table) doesn't change the status information for any of the entries marked with that status. Despite still being marked with the same status_id, the status column retains the old, defunct status name. Confusingly, this defunct status name is displayed when listing channel entries. When editing an entry with this defunct status name, the status drop-down has no value — presumably since the defunct status name doesn't match any of the currently available statuses. Similar debris is also left behind when a status is deleted.

So, changing the name of a status, or deleting a status, breaks any existing entries with that status — and gives no indication that this has happened. A quick and dirty solution to this would be, upon a status having been changed or deleted, to update this information in all entries marked with that status. A better solution would be to:

• Remove the status column from exp_channel_titles, since it's redundant given the status_id column.
• Where necessary, pull the current name of each status from the exp_statuses table. This would make sure that changing the name of a status would cause it to change everywhere, including for channel entries.
• Deleting a status should still result in its removal from all entries.

A related suggestion is to take management of statuses outside of channel management. Currently, statuses are added/removed/changed as part of managing a specific channel. Adding a status to a channel then makes it available to other channels. This is also potentially confusing, especially when editing a status. It's not clear or intuitive that changing or removing a status for a specific channel will also change/remove the status across all channels. Statuses should be managed outside of channel management, to make clear that they're common to all channels.

Most importantly, a properly robust status system should keep everything current, across all channel entries, if/when a status is changed or deleted.

[intoeetive]

Unfortunately exp:channel:entries tag (as well as some other code, including third-party) accepts the status by name, and for historical reasons we can't just let it go. EE however is also using status_id to relate to status. The main issue here is that the text representation of status does not get updated when status gets renamed

We also need to indicate the "old" status name with the entry if status has been removed - the best way to do that is probably including an alert on Status tab