Hey All!

I am currently designing a database model for (at first sight) a simple order entry program. The problem I'm currently facing is the exchange of data between the databases of each branch of the company. The company consists of five branches where each the same program and, hence, same database is installed. The data between these branches have to be exchanged (i.e. customers, users).

Now my question is, how should I design the database that it will be possible for exchanging data between the branches and still the primarykey remains unique and references to this primarykey still work (i.e. entity Order refers to a specific Customer)?

For example with the entity Customer and its primarykey CustomerID. Branch A add customers in its database, and also branch B add customers in its database. Both databases contains CustomerID of value 2, but the customer of each database with this ID represents a different customer (sounds logical, right?). How to exchange the data from branch A to branch B?

One limitation I have to add, there's no high-speed connection between the branches. Let say the exchange should still be done with a dial-up 56k modem (yes, it sucks, i know, but I am currently doing a project in a third world country... )

I hope I described my problem clear, please let me know if you need more information... thanks in advance!

use GUIDs, not IDENTITY columns

Hey R937,

thanks for providing me a solution. I actually already considered this option, but using GUIDs as primary keys affect the performance pretty badly, right? But actually, I am not talking about tables with more than 10.000 rows... So how big will the drop in performance be instead of using identity columns? Is there a way to use these GUIDs but that the drop of performance wouldn't be so huge?

Another question considering this issue, how should I design the database in order to easily identify whether a certain row has already been replicated (date and source) with the main server and vice versa? It is not I haven't thought about it myself, but I would like to have your opinion hoping it is a better one.

what drop in performance are you talking about?

and what makes you think that it's huge, even if there is a drop in performance, which i'm not saying there is

I did some browsing on using GUIDs as primary keys, and several resources (incl. Microsoft) say that "using uniqueidentifier keys might be relatively slower than indexes using an int key" (MSDN).

Others (Harkins) say GUIDs require more overhead due to its 16bit values which are 4 times bigger than integer values, for example. GUID values also "take longer to search and sort".

I also tried to find some tests which provide more information about the degree of decreasing performance. This article performed a test and found when inserting 1million records at once, with GUIDs would require 10 times more time.

Inserting such amount of records at once would never be the case in my situation, and still I don't find the performance that bad when counting per inserted row. So I'm not worried about the performance when inserting records. I am just wondering how 'bad' the performance will be when using this GUIDs as primary keys and using them in JOIN-queries. Can you perhaps tell me how the performance would be?

Thanks...

at this point, based on the research you've done, i would say that you know more about GUID performance than i do

hehe, ok but thanks though for supplying your info. I think I'll use GUIDs as primary keys as it will provide a solution to my problem. Despite it is slower than int keys.

Maybe others have an idea how the performance will decrease when using GUIDs as primary keys? especially in JOIN queries?

Are GUIDs larger than ints?
An IDENTITY integer is 4 bytes. A GUID is 16 bytes. GUIDS are thus twice the size of IDENTITYs, not four times the size. But lets put that in perspective: It is eight bytes larger, or the equivalent of adding a char(8) column to your table. So the additional space required is negligable.

Is using GUIDs bad?
Consider that when you set a database up for replication, if your tables do not already contain a unique GUID, then Microsoft ADDS a hidden GUID to each table. So much for MS not recommending GUIDs...

The article you referenced is much disputed. The affect of GUIDs on inserts depends heavily upon whether they are part of the clustered index or not. Also, it is arguable that the impact of GUIDs on inserts is recovered through efficiencies gained in other processing, particularly in in cross-database applications such as the one with which you are involved.

Go with the GUIDs.

i've read this three times and it still doesn't make sense, but maybe that's me ...

Uhm.....uh......hmmm.....must be a bug in Excel....

hehehe, I must say I've read the same line as well over and over again...

butteh, i decided to use integers as ID's and to divide them over my branches. so branch A can have ID 1 until 1.000.000, branch B from 1.000.001 untill 2.000.000. this length is more than enough. the benefit of using this is that it is still readable (for the IT-developer). Maybe a small issue, but using GUID doesn't totally suit me as well. Thanks for the info though Blindman...

Now i am trying to configuring the Merge Replication of SQL server. wish me luck!

Sorry, but I can't recommend the approach that you are taking. Surrogate keys, by definition, should have no direct relationship to the data they represent. By using the numbering convention you are adopting, you are violating a subtle database design principle. Experience shows that when you violate these principles for short-term gains you more than likely end up with bigger problems later on.
GUIDs were designed for exactly the purpose you intend.

hmmmmmmmmm, i already had my doubts on using surrogate keys. but i'm curious, what kind of problems could i encounter later on?

and... now i am questioning if i should implement using GUIDs as PK's for tables where it is actually not needed, because a normal PK (i.e. OrderID and BranchID) will due. What would you recommend, Blindman?

thanks alot for helping me out.

Why not just include the branchID in the primary key?

because including the BranchID with the CustomerID as the primary keys isn't correct. A customer is not related to a specific branch. A customer can buy something at branch A and later also at branch B.