Re: Surrogate Key Semantics

:) - so true.

Its a pet hate of mine, I've been in IT for a long time now and get fed up with people who forget the business and the goals of the business and give a 'IT solution' rather than a 'Business solution'. All too often projects are late or fail simply because its too detached from the business - i've seen it happen so many times.

Gives us all a bad name.

Luckily with the old school retiring or finding it difficult to get a job in the recent climate, probably one of the reasons mentioned above, we are getting better results and good business solutions. Still some way to go though.

Going back to the specific post, I'm an expert in Microsoft SQL Server and it doesn't, not sure any of the main stream ones do either, DB2, Oracle, Sybase or MySQL, plonk a auto surrogate key on there; so you need to do it yourself. Its been a big problem in the past where people use the primary key everywhere as the foreign keys instead of reducing the data down to a 4 byte surrogate key and foreign on that, lots of problems - makes the database bigger, sometimes massively; performance reduces - you need bigger kit, more tapes to back the bigger databases up, managability is harder etc...

Again, luckily people have learn't that lesson and the widespread use of integer (4 byte) surrogate keys is in use in the SQL Server world, an example...

create table blah

    natural_key varchar(120) not null primary key,     ai_id int not null identity unique,     ....
    )

create table blahdetails

    ai_id int references blah( ai_id ),
    .....
    )

Tony.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Roy Hann" <specially_at_processed.almost.meat> wrote in message 
news:4a6dnf9AP7Kn3h3eRVnyjQ_at_pipex.net...


> "Tony Rogerson" <tonyrogerson_at_torver.net> wrote in message


> news:dlp96f$a9u$1$8302bc10_at_news.demon.co.uk...
>> Talk about the wrong mental model....I think you really ought to get back
> to
>> basics yourself and understand just how this stuff is being used out in
> the
>> real world and stop being such a dam theorist.
>
> Out in the real world my son and his friends use my chisels as a
> screwdrivers.  It works OK, but there are obvious problems and a 
> screwdriver
> would be no harder to use.
>
> Roy
>
>