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

[rocklee]

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.

 

[The_Doc_Man]

Search this forum for the topic "Meaningful Keys" and also for "Meaningless Keys" - there have been maybe a gazillion threads on it. OK, maybe a few dozen...

Given your multi-source data, I would make an autonumber primary key for the table and then make the individual key information indexed, dups allowed. But that's just me.

To me, the biggest problem by far with making a complex key such as you described is the size of it. Yes, in theory you can have long, complex keys. But where it hits you is search, join, or other operational speeds that require key-finding, key-shuffling, and (Heavens forfend) Relational-Integrity based operations such as a cascade delete.

If you really want a PK for your table, synthesize one because of sizing issues. Use the other keys from your various sources if you have reason to try to backtrack the source of a particular record. But DON'T make them part of a compound primary key. Can you say CCCCRRRRAAAAWWWWLLLLLIIIINNNNGGGGG searches?

 

[rocklee]

Thanks Doc Man for your quick response.

I understand what you mean if the organisation ID changes, but what if it never changes?

Or would it be better to add a foreign key :

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

 

[Atomic Shrimp]

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

There's nothing wrong with storing join dates etc foreign keys for club subscriptions, etc, in your table - in appropriate individual fields - and you will be able to use them to collate your records any which way you like.

There' just no reason to make them into a compound primary key, and lots of reasons not to.