joel garry wrote:

> Agree with natual/surrogate post by Daniel - except, SSN is not
> guaranteed unique! (The problem really being insufficient analysis of
> natural keys).
>
> jg
> --
> @home.com is bogus.
> http://dizwell.com/migforum/index.php?topic=189.0

SSN is in most situations. It certainly meets the needs of insurance
companies. But you might want to do a two-field PK with SSN and DOB.

I like to use it because (A) I've never seen a duplicate and (B) if I
saw one I'd like it to generate an error so I could call security.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
> joel garry wrote:
>
> > Agree with natual/surrogate post by Daniel - except, SSN is not
> > guaranteed unique! (The problem really being insufficient analysis of
> > natural keys).
> >
> > jg
> > --
> > @home.com is bogus.
> > http://dizwell.com/migforum/index.php?topic=189.0
>
> SSN is in most situations. It certainly meets the needs of insurance
> companies. But you might want to do a two-field PK with SSN and DOB.
>
> I like to use it because (A) I've never seen a duplicate and (B) if I
> saw one I'd like it to generate an error so I could call security.

>From http://www.ssa.gov/regulations/artic...960_ag25f.htm: "We
anticipate that the three-card per year limit will impact fewer than
10,000 individuals in any given year. For example, of the nearly 12.4
million replacement SSN cards we issued in 2004, the number of
individuals who requested more than three replacement cards was 3,818."

Gee, now why would thousands of people need more than 3 replacement
cards in a year...?

Of course, there are also situations (generally, government) where
people may refuse to give their SSN. See
http://www.epic.org/privacy/1974act/ (and search for the word "index"
for an odd fact about whether SSN is indexed or not).

Things may change:
http://news.com.com/Congress+may+sla...l?tag=nefd.top

One-to-many:
http://www.ssa.gov/oig/ADOBEPDF/audi...8-00-10047.htm

(I can't seem to find the duplicate ssn links among all the
anti-gummint craziness in the minimal time allotted. But of course if
two people refuse ssn's on religious grounds, you have to deal with
that somehow).

jg
--
@home.com is bogus.
http://www.fincher.org/Misc/humor.shtml

joel garry wrote:
> DA Morgan wrote:
>> joel garry wrote:
>>
>>> Agree with natual/surrogate post by Daniel - except, SSN is not
>>> guaranteed unique! (The problem really being insufficient analysis of
>>> natural keys).
>>>
>>> jg
>>> --
>>> @home.com is bogus.
>>> http://dizwell.com/migforum/index.php?topic=189.0
>> SSN is in most situations. It certainly meets the needs of insurance
>> companies. But you might want to do a two-field PK with SSN and DOB.
>>
>> I like to use it because (A) I've never seen a duplicate and (B) if I
>> saw one I'd like it to generate an error so I could call security.
>
>>From http://www.ssa.gov/regulations/artic...960_ag25f.htm: "We
> anticipate that the three-card per year limit will impact fewer than
> 10,000 individuals in any given year. For example, of the nearly 12.4
> million replacement SSN cards we issued in 2004, the number of
> individuals who requested more than three replacement cards was 3,818."
>
> Gee, now why would thousands of people need more than 3 replacement
> cards in a year...?

Thus my point in calling security.

> Of course, there are also situations (generally, government) where
> people may refuse to give their SSN. See
> http://www.epic.org/privacy/1974act/ (and search for the word "index"
> for an odd fact about whether SSN is indexed or not).

Except not as a condition of employment, or banking, or buying a house,
etc. etc. etc. In the US if you choose not to identify yourself you'd
best be living in a cave and living on grass and berries.

> Things may change:
> http://news.com.com/Congress+may+sla...l?tag=nefd.top
>
> One-to-many:
> http://www.ssa.gov/oig/ADOBEPDF/audi...8-00-10047.htm
>
> (I can't seem to find the duplicate ssn links among all the
> anti-gummint craziness in the minimal time allotted. But of course if
> two people refuse ssn's on religious grounds, you have to deal with
> that somehow).
>
> jg

I'd be interested in knowing from others here that reside in other
countries whether they have a government equally capable of bungling
with something that is theoretically a personal identifier. Any comments?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

David Newman wrote:
> "Mark D Powell" <Mark.Powell@eds.com> writes:
>
> > Jens Lenge wrote:
> >
> > Normally we use a business column or set of column values in the table
> > to be the PK and do not use an artificial key since if a unique
> > business value exists there is no need for or real use of an artificial
> > key.
> >
>
> This is an interesting subject to me. Normally I would fully agree with
> you. But I have been learning the Hibernate persistence library lately
> and they refer to this type of model, one in which actual business
> columns are used in the primary key, as "legacy" and not designed well
> and that an artificial primary key is always preferred. I've always
> felt that if you are going to have something in the table, like an
> employee id or SSN, that is going to have a unique constraint on it anyway
> you might as well make it the primary key. Is this some new way of
> thinking?
>
> --
> Dave Newman

Dave, in my opinion the Hibernate people are full of crap. The problem
with an artificial key is that while it might be handy to use in the
case where the PK value is subject to change the FK to the generated
key does next to nothing to protect the data from business rule
violations. In your example where there is a UK on the business column
you are probably going to end up with child tables that have both a FK
to the PK and a FK to the UK. Why would you need both? When you use
generated keys you often end up with extra indexes becuase you have to
index the generated PK plus you have to index the business column that
the user actually has a value for. Generated keys have plenty of
drawbacks if not applied carefully. The best designs will probably
include both.

As far as the legacy comment goes: rubish. Generated keys were in part
pushed so that you could use native integers as the key as this would
be more efficient. But until version 10 Oracle would not even store
numeric values as native machine types but rather used the number type
for float, integer, number(10) which requires library math.

Design is unfortunately perhaps the most important single aspect of
whether an application will perform well. I say unfortunate because
the rules on how you normalize your data and organize your data stores
into a functional system are not well defined from the following point
of view. A team can take a very structured, rigid approach with one
application and it can work very well. Using the exact same approach
with another problem however can result in a system that does not
perform well. The problem in my opinion being the key word:rigid.
Some flexibility in how the system is designed based on the data at
hand, the data relationships, and how the data is manipulated needs to
be built into the system. You will have trouble getting that if you
follow a rigid rule like every table must have a unique numeric
generated key.

IMHO -- Mark D Powell --