Re: Newbie question about db normalization theory: redundant keys OK?

"Dr. Dweeb" <spam_at_dweeb.net> wrote in message news:476593b3$0$21933$157c6196_at_dreader1.cybercity.dk...

> > Would you a) tell the university that the data cannot be modelled and
> > you can't hold it in a database or b) use an artificial key?
> >
>
> This makes my student number 7613861 artifical?
> This makes my employee number 644923 artificial?
>
> One is used by my university, the other by my employer. They may be
> arbitrarily assigned, but they are unique and indicate me alone.
>

Artificial in the sense of man made, yes. The assignment of those numbers to you
was purely an arbitrary act, and not based on any naturally observable charactersitics you have.

The assignment of an as yet previously unused number to you might have been done by a clerk in the registrar's office or human resources department. More likely, it was done by application software lying somewhere in the processing chain between data entry and insertion into the database.

It's when the application software relies on the DBMS to come up with a previously unused number that things start to get muddy. As long as the assignment has been done before the DBMS ever sees the data, the distinction between "natural" and "artificial" is moot, and the database design might as well consider those two numbers to be "natural keys" of you, in two different roles.

"Artificial" and "surrogate" are often used interchangeably. They shouldn't be. Received on Mon Dec 17 2007 - 14:44:37 CET