Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2006
    Posts
    7

    Question Exchanging data between branches - Database Design Help

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use GUIDs, not IDENTITY columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2006
    Posts
    7

    Question

    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.
    Last edited by dfine; 06-17-06 at 23:09.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2006
    Posts
    7
    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...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    at this point, based on the research you've done, i would say that you know more about GUID performance than i do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2006
    Posts
    7

    Question

    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?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    An IDENTITY integer is 4 bytes. A GUID is 16 bytes. GUIDS are thus twice the size of IDENTITYs, not four times the size.
    i've read this three times and it still doesn't make sense, but maybe that's me ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Uhm.....uh......hmmm.....must be a bug in Excel....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2006
    Posts
    7
    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!

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jun 2006
    Posts
    7
    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.

  14. #14
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Why not just include the branchID in the primary key?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  15. #15
    Join Date
    Jun 2006
    Posts
    7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •