Unanswered: What is your opinion of ID keys created based on time/date/random numbers?
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, Yoga, Mild exercise"
"C, Basketball, Med exercise"
"C, Rowing, Heavy exercise"
"C, 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.
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).
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.