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.

yes -- you basically have no way of detecting duplicate clubs

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).

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

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.