Avoiding Duplicate Identity Columns When Combining DataBases 2

[Auguy]

Looking for some table setup advice. I am working on a small employee time record project. The program will be installed on three computers not linked by a network. I plan on using identity columns for the various employees, departments, etc. records and as the method to link them together. Some time down the road they will be installing a network and I would like to merge the tables into one database and not worry about any conflicts with the identity columns from the various separate databases. I thought of two methods. One is to use a calculated column based on the computer (#1, #2, #3) where the database is installed and an identity column in the table (i.e. #2 x 1000000 + identity value), the other is to change the seed values on each computer so that they would never be duplicated when combined into one database. Any thoughts my ideas? Any other methods out there that you've used before?

Auguy
Sylvania/Toledo Ohio

 

[Olaf Doschke]

Using uniqueidentifier is a well known practice for that matter.

Even when using an offset or different seeds, once you combine, you'll have the effort of once reseeding. Ill logic (it's tempting to reidentify the computer from the ID) depending on the offset stops working. Thereofre numeric idnetity fields are always bad for combining them.

If you still want or need to go for integer identity fields, you could centralize a counter, eg in the internet, if computers are not even in the internet, uniqueidentifier is really the best thing to use.

http://tek-tips.com/viewthread.cfm?qid=1722318

That's different in different systems, depends on your OS and the database, but they all have this. UUID, GUID, comes in different flavors, but it's mostly the same.

Bye, Olaf.

 

[Olaf Doschke]

Don't ask me, how that link to this thread comes into my post. Forget the last sentence, sinc you aks for MS SQL Server it's uniqueidentifier and use of a default value of newid() and you're done.

Bye, Olaf.

 

[gmmastros]

If you go down the GUID/unique identifier road, then you are better off using NEWSEQUENTIALID().

http://technet.microsoft.com/en-us/library/ms189786.aspx

The benefit is that data will be added in a sequential way so that your table does not get as fragmented (which is bad for size and performance).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks to both of you for the advice!

Auguy
Sylvania/Toledo Ohio