Creating numeric keys based on unique object names in a database across systems?

I have a database which uses unique text keys to link between objects. For example, when users are inserted they are inserted with the text key "{date}-{system}-{user}" where each element of the key is alphanumeric. This is then checked against a look up table to see if it exists, if not it is inserted into a table and an auto-increment integer key created. Otherwise the same integer key is re-used.

Then, if a unique attribute of that user is inserted, it is matched and assigned the user's numeric key as the foreign key. For instance, a document unique to this user is inserted with a user key "{date}-{system}-{user}" and a unique text key for the document "{date}-{system}-{user-{document}" from which an integer key is generated.

In this way, there is a numeric key associating these two entries and the document has a unique key for attaching attributes to it. These are used in various queries.

The problem is the keys, of course, are not the same across systems depending on how and when the elements were inserted. So, my initial thought was to use 开发者_开发技巧a hash as the generated key, but int and bigint are fairly limited, I think.

I'm not sure how SQL server will handle long strings of hashes for foreign keys, I have read it does not do it well in terms of performance. Otherwise, I could just use the text that is creating the key itself.

How would you go about making sure the int, bigint or string (if plausible) keys are unique across systems, assuming the text key is unique? A hash? A GUID? How could hash collisions be handled if a bigint (63 bits?) bit key were used, if at all?

Thanks!

John, you could, if possible, can you rethink your key architecture, because this is the ideal situation to use a GUID for a key. That way you can generate the unique key for any user on any system and be 99.999999999999.......% guaranteed that there will not be any clashes. And this is only 16 bytes too. You can always keep your existing key structure as another column, but only for other purposes perhaps, not to identify or make a record unique. This I would suggest will only be the case if you application uses this information to perform certain tasks, if not, get rid of it completely.

The nice thing about using a GUID as a key in this scenario, is that it is platform independent, ie. you can use it on Oracle, SQL Server, unix, windows ....