Choice of format for primary key

Databases
1

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 ?

2

Hi Tim,

In this sort of situation I usually go with option 2 and just add an auto-incrementing numeric column as the primary key. You can always add an additional column to act as your human-readable reference. As for sorting, if you’re looking to get the records in the order they were created, presumably you have a ‘created’ field which stores a datetime of when the booking was made, or something similar that you could sort on to achieve the same result?

3

Thank you. One of the benefits one gets from creating a Thread is that it forces one to think about the problem a bit more. I was beginning to think that Option 2 was going to be the only one that makes much sense, but rather hoping there might be a compromise.

I suppose I could use ‘13046’ which would increment OK up to (say) ‘13152’ by year end and jump to ‘14001’ for bookings next year. There may be some snags. I’ll think more. It may be I’m making more fuss about the human readability than it deserves.

Yes, I do have a ‘booking_date’ column which has a time stamp, so I could sort by that as you say.

Some of my problems have no doubt arisen from working with incomplete data as I transfer it from my working flat file to MySQL still under development ! (e.g. I didn’t copy the time stamps across !)

4

yup :slight_smile: