Re: What happens after 2^32 autonum keys are exhausted?

Hi Elmer,

http://wck2.companieshouse.gov.uk/6bf285cff2398ecc278924b04013f954/wcframe?name=accessCompanyInfo

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"Elmer Fudd" <bitbucket@xxxxxxxxx> wrote in message news:ovgohpxrg.jmgrqt0.pminews@xxxxxxxxxxxxxxxxxxx
Joe and Tony,

Joe, do you know of a (free) web source for federal EINs? Tony, a (free)
source for British company registration numbers? How about websources for
other European company registrations?

On Tue, 07 Aug 2007 10:52:15 -0700, --CELKO-- wrote:

Similarly, I have indicated that I am prepared to use a database generated primary key despite the risk of a hardware or software error or a fault in the space-time continuum. <<

But you do not have to have that risk.

When I watch the register at the supermarket they don't show me the primary key and I would use the description even if they did. <<

They actually do. The POS systems will print out the store number,
the cash register station number, the time stamp and finally a local
sequence number within that context.

I am willing to bet that the only primary keys that anyone at Safeway knows are the computer generated ones that identify products that are exclusive to their store. <<

NO! You would be surprised how many 10-digit UPC codes a clerk
knows. They also know the manufacturer's id that is part of the bar
code. Next time you buy produce, see if the clerk knows five digits
for a vegetable or fruit when it is in season. If they don't know
what okra is, they use a laminated look-up *** with a color picture
and a bar-code for validation and verification.

I say that I live in Melbourne rather than 37.47S 144.58E although I know that Melbourne is not a unique primary key and that there are people who say the same the thing but live at 28.12N -80.65W. <<

You are depending on "Melbourne, Victoria, Australia" or "Melbourne,
Florida, USA" to be determined by context. It works fine for
Australia versus USA; but not so good for "Springfield, USA" where
the SIMPSON'S cartoon show is located (http://www.geocities.com/
ymscrusader/Simpsons.html). The name was picked because it is so
common (34 of them!).

I am not convinced that I should add a Social Security Number to my Address Book data. <<

How about to your payroll system, so you do not go to jail? You are
willing to accept a risk in your address book that you would not in a
payroll.

Of course I want that [industry standard identifier]. However that does not provide any guidance on how to select a primary key when your ideal primary key does not exist. <<

My advise has always been to first ask the subject area expert what
they do. That sounds obvious, but I worked on one project where the
programmers would not talk to two full time staff librarians about a
classification system for audio and video media.

Next, Google it. I just found the AdsML standard for the advertising
industry; I had no idea it exists and that I can get it in electronic
media. In the old days, it was a bitch to locate things; now it is a
mouse click away.

Finally, if this is the 1-2% of time when you have no standard
identifier and have to invent one, follow the data design (data
design, NOT database design!) rules I give in SQL PROGRAMMING STYLE
and some other places.

But Oracle or IBM or some other organization that I trust
because I am using their database, have promised me it is a unique
identifier [sic]. <<

It is not an identifier; identifiers are repeatable. If you take the
fingerprints of the same guy over and over, you get the same answer.

And it is only LOCALLY unique to a particular installation of a
particular release of a particular product on a particular piece of
hardware. Not a lot of room for growth there. Planning for failure
to grow is fine for your cell phone speed dial, but not so good for a
phone company.

Isn't using that much better than a key composed of all the columns in a row especially if the value of any of those columns is likely to be changed? <<

That happens soooo rarely that you should worry about being hit by
lightening more. The parts of a multi-column key are usually stable
-- if either "Melbourne" has a frequentally changing longitude or
latitude, we are in a lot of trouble! There are usually three or
fewer parts to worry about in practice. Also, where is the non-key
data in the row? You do not often have a table that is only a key.

If there is an industry standard key, you have to include it and make
it both NOT NULL and UNIQUE, so your data model is not screwed up.
Why do I want more index and storage overhead?

Finally, if it is really that hard for you to type a long list of
column names, set up a macro in your text editor and use a pull-down
list.





.