Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: What is your opinion of ID keys created based on time/date/random numbers?

    Hi folks,

    I'd like to get some advice based on the above question, is it ok to create records with primary IDs based on time/date/random number/other factors?

    Here's a case scenario of why this might occur :

    I have a main database that collates information from several different organisations that uses their own databases. The information it collects is about the different self-improvement groups that each organisation creates for their staff. To simplify things lets call these groups "clubs" :

    [Club] - Club ID, Name, Description
    "C01, Yoga, Mild exercise"
    "C02, Basketball, Med exercise"
    "C03, Rowing, Heavy exercise"
    "C04, Microsoft Office, Learning how to use MS Office"

    [Organisation] - Org ID, Name, Description
    "01, Schroeders PLC, German banking"
    "02, Barclay, British bank"

    Each organisation also record attendance for each group which the main database also collect. Because each clubs created in different organisation may have the same ID (if auto-increment was used) this causes a problem at the main database. So if I do this :

    "C[organisation ID][date][number], name, description"
    "C[000001][10102009][0001], Yoga, Mild exercise"
    "C[000001][10102009][0002], Basketball, Med exercise"
    "C[000001][10102009][0003], Rowing, Heavy exercise"
    "C[000001][10102009][0004], Microsoft Office, Learning how to use MS Office"

    Is there anything wrong with this? Its basically a way to collate clubs and attendance from different organisations.

    Thanks for your help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rocklee
    Is there anything wrong with this?
    yes -- you basically have no way of detecting duplicate clubs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    Could you elaborate?

    Club ID - "C[organisation ID][date][number]"

    By having a multi-source generated ID each record can still be unique since they will belong to different organisation (organisation ID), created at different date and have different running numbers (or random numbers could be generated).

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rocklee
    Could you elaborate?
    suppose some organization accidentally sends you the same file twice

    your proposed scheme would accept all the groups and give them new rows with new PK numbers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    3
    These club IDs are primary keys and should data be imported into the main database duplicates would be rejected anyway.

    There wouldn't be duplicates unless the same organisation is importing their data twice, their clubs wouldn't be the same as clubs from other organisation because of the [organisation ID] created within the club ID.

Posting Permissions

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