For some years we've had an on-line booking system for our B&B business using a text file database. It's worked well, but for many reasons it's time to update it to MySQL. Insofar as there was an index on the flat file, it was always the date, although we had a 'Booking Reference' in the format 'year-number' e.g. '2013-46'. A booking could encompass several days (and several people).
For the MySQL system I intend to tie everything to the 'booking', which will become the primary key for the 'bookings' table. Other tables will be 'availability' (essentially a calendar) and 'guests', which will both have the booking reference as a foreign key.
Initially I've carried forward the Booking Reference from the old system, but the format '2013-46' doesn't work well as it won't sort numerically and it's hard to increment (I've done it by splitting it into two parts). It's advantage is that it means something to a human. I'm sure I'm not the first person to have had this problem, and I'm wondering how others have solved it.
The options seem to be:
1. Struggle on with what I've got
2. Use a purely numerical index like '99046' (will sort and increment, but doesn't convey much to a human)
3. Use a pseudo decimal format like '2013.046' (this means something to a human, will sort (I think), but has the same issues with incrementation as the current format, possibly worse because of the need to retain the leading zeros in the decimal portion).
But perhaps there's something better I've not thought of ?