vbwyrde
2013-02-19T11:53:00Z
I discovered a serious problem with the way World Event Sequencing works (or doesn't work), actually. It's a big old bear in fact.

Here's what the data in the World Event Date (or sequence number) field looks like:

1
2
3
4
3.1
3.2
A
B
C
10
11
12
12.1
12.2
1/1/200 NK
2/2/200 NK
2/3/200 NK
etc

The way I would prefer to sort them using T-SQL is:

1
2
3
3.1
3.2
4
10
11
12
12.1
12.2
A
B
C
etc.

I tried a whole bunch of query methods to get the sorting to work, but Golly Jeepers! No way. Natural Sort, which is what you call it when it sorts the way humans would expect, is really not only hard, but depending on the data the users enter, it can even be impossible to achieve. Computers simply don't know what humans mean, as it turns out. The more I look into this, and try various suggested solutions against my dataset, the more convinced I am that it's not worth the ambiguity and effort to get this to work. I think the thing to do is not offer the users the ability to enter this data by variable sort options, such as sequence number, or date, which is what I had at first.

The problem arose when I found out that SQL Server only offers data ranges going back to January 1, 1753, but I'm working on a game system where the user creates their own worlds and so the dates could be any date such as 1/1/200, or what have you. I opened the field up as a Varchar to let them put in dates like 1/1/150 New Kingdom, for example. Some users (myself included) began using the field as a simple sequence such as 1, 2, 3, 4.... and then that expanded to include decimals. So someone could enter a World Event that took place between 1 and 2, and would sequence it as 1.5. What I did not realize at the time was how difficult the sort problem would be in the end. So, what I'm thinking of doing is starting that over and putting in a "Event Sequence" column as a float instead, and having a separate "World Date" column as varchar so they can write in the date if they want, but the sort order will be in accordance with the "Event Sequence" column. While that introduces a lot of changes to the database and related code, I think the fact is that there is no good sorting solution for the data as I have it now, and allowing the users to enter data as I have been will only open a Pandora's box for them. 

This may take some time to fix, but I think its better to do that, than leave it in the current flexible, but dysfunctional state.