Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Unanswered: Being delusional about replacing identity with self generated id

    SLQ Server 2005

    Hi Guys,

    Well, the big re-write of our app in Russia is an official failure and it has come back to us (after 2+ years at $20K per month). I suggested we get the data model straightened out before we do anything else.

    Here's a notion that I'm expecting to have to stomp, but I thought I'd run it by you smart guys first.

    Basic structure will be Admin DB as a hub and each customer (because they all request this anyway for 'security') will have a separate DB for their Disability/Leave data.

    As an additional layer of 'security' it was requested that, say, the unique id of a person would be unique across all client databases. Hence if the interface queries medical info on Person 123 for Customer B there is no possiblity of accidentally returning Person 123 from Customer H, because 123 only exists as a primary key in the Person table in Customer B's database. Each available key number is used uniquely across the admin & customer databases. (As you may guess, we've had several instances of company a's data going to company b.)

    We're new to the idea of splitting out customers into separate DB's but our plan is to keep the customer DB's identical in every aspect except data.

    Is there a functional way to ensure a key's uniqueness across our whole structure (Admin DB as Hub and multiple client db's)? Are we nuts to want this?

    Note I did look at some info on 'creating your own identity' key thinking that maybe we could keep an 'identity' in a table in the Admin DB for each client table that needs this feature. But it sounds like I'll end up with issues for sure when bulk inserts are run unless the insert is run on the table in the Admin DB.

    I can't imagine anyone is going to try to answer this, but it made me feel better to type it all out here!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Multiple client databases?

    Why?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    Hi Brett! Hope you're doing well.

    Most of our customers set a requirement that the data specific to their employees and employee's medical info be in a separate database. Right now we have a mix of single customer DB's and multiple customer DB's. We thought that we should just model on the one client/one db for the future and just keep all the objects in the client db identical to make maintenence fairly straightforward.

    All custom objects, for whatever client, will be in the Admin db.

    At least that has been the plan.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why should they care where the data resides as long as you provide a security model to it?

    It's like data modeing in reverse
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    BOL has this to say about the uniqueidentifier datatype:
    Quote Originally Posted by BOL
    uniqueidentifier

    A globally unique identifier (GUID).
    Look up uniqueidentifier and the newid function. If Microsoft entrusts merge replication to it, it is probably going to be good enough for your needs. Otherwise, you would end up making all your primary keys some blend of identity value and customerID.

  6. #6
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    Thanks MCrowley.

    I think the GUID could be a way to do this.

    I was also thinking of what used to be called in my former workplace 'smart id's' where you make up an id with an identity + other needed info. I hadn't played with that in SQL Server. I'm assuming the 'smart id' field would autofill off of a trigger running after the insert.

    Thanks, I'll look into the GUID info.

  7. #7
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    Brett,

    I don't know, but I do know that those companies smart enough to ask about our security, always ask to be in their own db.

    We don't make the sale or write the contract, but we do what we're asked.

    Go figure.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I had a fun little lesson with synchronizing data (without replication) to databases and the uniqueidentifer datatype not too long ago. While no 2 machines will create the same id, it is possible to have the same id created on the same machine in the same table unless you put an unique constraint on the column as well. a serious wtf.

    don't ask. not my design but I had to fix it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    Ouch! We'll keep that in mind.

    I thought I saw something about that while I was Googling on creating an Identity key. You might not have had a unique, non-unique experience there.

    That last sentence is going to hurt for a while.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    If you are planning on using uniqueidentifier as a pk column, you should have a look at newsequentialid() since you are on 2005 (it's not available on 2000).

    you can use it for a default value on the column and it will behave like an identity column (always increasing). that way it won't fragment your table if your pk is clustered.

  11. #11
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    While researching this afternoon we found this. It looks like it has potential.

    Thanks!

  12. #12
    Join Date
    Mar 2006
    Posts
    4
    Since you going to have a master database as well as the company ones why not just have a master key generation table in the master database.

    SystemIdents
    ID (identity field)
    UsedFor (just a small text field to hold where its used)

    Then whenever you need a new id, just request one from the master database with a reason.

    I am not too keen on GUIDs i would much rather say customer 34673 than customer 1A5FED452DE198A5B..... etc.. lol

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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