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!

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

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.

....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

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.

Thanks for the answers, I shall try and persaude him (again) to ditch the range id method.

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.

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.

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?

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.

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.

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.

I agree exactly

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.