views:

31

answers:

1

Hi Guys,

Sorry in advance as this question is similar (but not the same!) to others.

Anyway, I need to be able to generate surrogate keys in more than one location to be synchronized at a later time. I was considering using GUIDs, however these keys may have to appear in the parameters of a URL and GUIDs would be really complicated and ugly.

I was considering a scheme that would allow me to use integers, providing better performance in the database, but obviously I cannot simply use auto numbers. The idea is to use a key with two meanings - the High-Low Strategy as I believe it is called. The key would consist of the source (where it was generated, generally 1 of 2 locations in this business case) and the auto incremented value. For instance:

1-000000567, 1-000000568, 1-000000569, 1-000000570, ...

And for another source:

2-000000567, 2-000000567, ...

This would also mean that I could store them in the database as integers (i.e "2-000000567" would become the integer "2000000567").

Can anyone see any issues with this? Such as indexing or fragmentations that may occur? Or perhaps even a better way of doing it?

Just to confirm, there is no business meaning in this key, the user will never see it (except perhaps in the parameters of a URL) nor use it.

I look forward to your opinions and appreciate your time, Thanks a million :)

A: 

This explains the hilo algorithm which you refer to: http://stackoverflow.com/questions/282099/whats-the-hi-lo-algorithm

It's the often-used solution to "disconnect" problems such as yours. For i.e. if you're using Hibernate/nHibernate, it's one of the recommended primary key options.

Ted
Thanks for your response :)
Tony Day