Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    May 2008
    Posts
    33

    Object Id Ranges

    Hello,
    Does anyone know any benift of using a range of Ids for certain objects? For instance, if I have a table of people, then I give them Ids 1-1000, then for a table of cars 1001-2000.

    I can only see problems in this method but my boss is adamant it's very useful as this way different objects can be recognised by their Ids, which doesn't seem useful at all to me.

    Any comments? Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you do not have a database problem, you have a boss problem

    your boss is a database idiot

    he or she might be a good boss and a great person, but obviously slept through all the database classes

    i have learned something very valuable over the 30+ years i've had bosses -- don't argue with them

    boss: "do it this way"
    me: "but that will have problems"
    boss: "no, do it this way"
    me: "yes boss"

    maybe some day, when the system blows up, and the vice-president comes down and asks your boss why it happened, you can say "gee, i knew it was risky, but my boss told me to do it"

    the real solution, of course, is to find a job with a boss that will delegate authority where it belongs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I've done this a couple of times now: jot down your concerns with the design in a pretty formal manner.
    Pass this document on to your boss (or even their boss).

    It's called covering-your-own-ass, and writing your thoughts down in a documented manner is the best way of doing this.
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    ....or of make sure your bosses demands are written down AND your counter proposals, so when it comes to having the necklace party the right person gets the finger pointed at them
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    you do not have a database problem, you have a boss problem

    your boss is a database idiot

    he or she might be a good boss and a great person, but obviously...oh. Whoops. Rudy beat me to the punch.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    May 2008
    Posts
    33
    Thanks for the answers, I shall try and persaude him (again) to ditch the range id method.

  7. #7
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    Something similar happened where I work. They wanted ids prepended with 1, 2, 3, or 4 so it would look like 3288 or 1220. I had to explain that this was not actually a useful thing to store. The idea/data that the extra stuff represents is already there. In your case Cars is in a cars table already. If you pull data from there you know it represents a car. Any report or form you build based on it can display that fact that it is a car in any manner necessary and does not need to rely on the fact that the id was manipulated in a strange manner.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  8. #8
    Join Date
    May 2008
    Posts
    33
    Yep, sounds like you had the exact same issue as me.

    There is one exception I forgot to mention though. We actually deal with a database that includes different objects that connect to each other physically through pipelines. These connections are stored in the pipelines table sepecifically with two columns; a ToId and a FromId.

    As each object's Id is in a particular range they are therefore unique compared to other objects and any object can be connected by a pipeline to another object.

    I'm sure this is just a bad design. The two solutions I imagine are that:

    1. That other all these pipeline-connectable objects can exist in a supertype table so they each have unique ids compared to each other

    2. Each type of object that can join to a pipeline can have it's own PipelinesObjects (where object would actually be the name of the object) to store all these links.

    I've probably gone a bit off topic here though... Perhaps I'll start another thread to discuss this.

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    There's nothing wrong with using well designed identifiers for the things of interest to your business. But "obviously" just assigning arbitrary ranges of numbers is nothing like a well-designed identifier.

    How about the alternatives? Surely cars have chassis numbers and tag numbers? Do the people in question have payroll numbers or customer numbers perhaps?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by dportas
    There's nothing wrong with using well designed identifiers for the things of interest to your business.
    Yes, but by definition those would not be surrogate keys, and this gentleman's boss appears to want to mix the two concepts. Bad idea.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by blindman
    Yes, but by definition those would not be surrogate keys, and this gentleman's boss appears to want to mix the two concepts. Bad idea.
    I agree (about avoiding "intelligent" surrogate keys). But no-one mentioned surrogate keys. Michael mentioned only "Ids" - and unfortunately "Id" is a vastly overloaded and misused term, which is why I try to avoid it.

    I was thinking that Michael might not mean a surrogate key but a business key identifier.

  12. #12
    Join Date
    May 2008
    Posts
    33
    These are indeed surrogate keys and used as foriegn keys in other tables. I realise it is useful to have a more "meaningful" Id, or use a natural key, when identifying an object in the real world.

    There wouldn't be any equivilent of payroll number or chassis number to use in our database, so I'd say the (most) natural key was the name of each object, although these can be subjective at times and can change, hence the use of surrogate keys.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Surrogate keys should have no inherent relation to the data, and should never be exposed to users.
    So there is absolutely no need to define ranges for them, and if you do this then you are certainly using them inappropriately.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    May 2008
    Posts
    33
    I agree exactly

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    wait! this idea could have saved us from the current credit crisis!

    imagine if the the BadLoan table was restricted to ids in the range 1-100.

    then only 100 bad loans maximum would have been made and we wouldn't be in this pickle!

    you guys need to give this boss credit - he's a genius.

Posting Permissions

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