Re: Table Design Question

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Zach Wells (no_zwells_spam_at_ain1.com)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 08:43:27 -0400

Joe Celko wrote:
>>>I'm interested in what you're implying here - care to elaborate? <<
>
>
> The regulars are cringing now :)
>

I was waiting for that response. :) I understand everything you're
saying, and in *theory* it all sounds great. However, all of us "idiots"
work in the real world and are forced to make design decisions that
might not fit the nice, neat, complete scenarios you come up with.

For example, what would you make the primary key be for a table of
people in a database is international? SSN won't work since not only
does not every US citizen have one, but no one outside the US has one
not to mention that you can't always get people's SSN for security
reasons. Name obviously won't work. Height? Weight? Oh, I know, why not
require your company to install fingerprint machines in every location
and require ever person that you plan on putting into the database come
into the office and submit their fingerprint? After all, I'm sure when
you simply explain to your boss that this is necessary to have a
"properly" modeled database he'll be more than happy to foot the bill!
The truth is, in the real world, we don't always have keys that are
inherent in the data and having some sort of system for a generic key is
essential. I'm not saying that you should, by default, throw an identity
column in every table with a field called "ID", but I am saying that
sometimes you have to have some sort of key that is not built from the
data. With your vast experience in the industry, I honestly can't
imagine that you've not encountered this situation multiple times. I
know I have.

The simple fact is that in the real world we quite often do not have
complete data to work with. It is not uncommon to have situations where,
for example, you may only get a name and address for someone until they
are a full employee or something like that. Once that has happened THEN
you can get their SSN. In this case, should we have the person’s first
name, last name, middle name, address1, address2, city, state and zip
make up the primary key? Is that *really* preferable to having a generic
numeric key? I know you're quick to claim that when people use the
identity column that they’re trying to go back to the tape days in the
50's and duplicate that. Well, I can tell you that I'm not. I wasn't
alive in the 50's. When I do use an identity column as a primary key, it
is because I can't be guaranteed to have a reliable, complete primary
key within the data. I could care less *what* the identity column comes
up with for the key. I don't care if it is sequential. I don't care if
there are gaps; all I care about is that it is unique.

Zach

Relevant Pages

  • Re: problem using identity column as primary key
    ... If you care about what the values will be in your primary key, ... then a unique constraint on these values. ... I am thinking of creating an identity column to use it as primary key ...
    (microsoft.public.sqlserver.programming)
  • Re: Getting list of recently added IDENTITY items
    ... Almost all of our tables have an identity as the primary key or some ... You are just saying to also put a UNIQUE constraint on the ... evilness of the identity column and so far what you are saying to do is ...
    (comp.databases.ms-sqlserver)
  • Re: UPDATE TOP (1)?
    ... You are saying you want to update any record where the Status is blank, ... don't care which, so long as it is just one? ... never changes, select top 1 and get the primary key of one record, then use ... Any suggestions or guidance? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Tahirih Justice Center-Association with Anti-NGO/anti-environmentalist corporations
    ... I'm saying more than that. ... here are saying about many of the critics who have come here or spoken ... As I've said before, corruption is corruption, corporate malpractice ... care how many rivers they polluted, how many children in China worked ...
    (talk.religion.bahai)
  • Re: dead man talkin
    ... just did not seem to fit (the other saying that plum ... saying that you could not care less that a wonderful ... at great risk an enterprise competing in the molding, ... that your care level about this wonderful town semi- ...
    (misc.writing)