SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    334
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Choice of format for primary key

    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 ?
    Tim Dawson
    Isle of Mull, Scotland

  2. #2
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,256
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)
    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. #3
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    334
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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 !)
    Tim Dawson
    Isle of Mull, Scotland

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ramasaig View Post
    It may be I'm making more fuss about the human readability than it deserves.
    yup
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •