Say, we have a set of records that should be ordered with a non-trivial ordering method, e. g. natural sort or just in some specific sequence, defined by a user. We have to introduce a special column intended just for ordering.
Is there a more or less common convention for naming such columns? What names do you use?
I have seen Sequence used as well as SortOrder. Personally I like Sequence better, as it implies uniqueness.
While we try to avoid these kinds of constructs, as you're finding sometimes it's the shortest and best solution.
We usually call it SortSequence or UserSortOrder but I haven't seen any particular convention around nomenclature (generally because ordering is generally derived from other data and not an explicit thing...) Long as the name makes sense and (if you do it elsewhere) is consistent that's what I'd be striving for.
I personally use any logical name for the fields. I have not seen anywhere what the name should be for a particular field, however, I try to make sure that name is not one of the mysql reserved keywords so that I don't to use backtick characters ` all around.
Also, MySQL isn't case-sensitive normally. So "mytable" and "MyTaBlE" are the same to MySQL. Under some platforms, I believe you can configure MySQL to be case sensitive, but doing so creates a potential portability problem if you ever want to port your database to other platforms.
Resource to naming convention:
I use SortOrder for columns that represent a relative, but arbitrary, means for ordering and Sequence for values that should be sequential and not have gaps. Thus, I might have values of 10, 20, 99 for SortOrder but never for Sequence. I might have a SortOrder for say a list of currencies so that certain currencies appear at the top, but a column called Sequence on order or invoice line items.
I'd use SortOrder over sequence, for the sake that sequences are by some vendors (Oracle, PostgreSQL) to provide auto increment/identity functionality. SortOrder is more obvious/explicit about what the column is being used for.
As others have indicated, there is no real standard for naming such columns.
My preference tends to be "Ordinal" (or rank), but I think as long as someone can look at the table definition and they can figure out what data a column represents, then I think you are good.