Primary key

[lenni]

Which of the following would NOT be a good primary key?

Student Number
Social Security Number
An e-mail address
A postcode

My answer is postcode, since people might have the same postcode.
WHat does other think?

 

[Jeff Boyce]

All! If you can, avoid using spaces in fieldnames.

We can only guess what data is actually stored, and may have no idea how the
values are assigned.

What is your definition of "good"?

You'll find an ongoing discussion in this newsgroup about using natural keys
vs. arbitrary keys.

Since each of the 4 you listed could be assigned to more than one person
(via data entry error, identity theft, multiple folks sharing either email
or postal code), I'd have to say NONE. Is this a homework assignment?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jerry Whittle]

E. All of the above!

Well the Student Number wouldn't be bad IF the powers that be don't ever
change it.

Social Security Number? For the most part, only US residents have one. That
means if someone from another country needs to be in your database, there's
going to be a problem. Then there's laws about using SSNs for things not
related to Social Security; the occasional duplicate numbers released; and
fake SSN numbers.

An e-mail address. People do change their email addresses for various
reasons. What will you do if the E-mail address is used both as the PK and a
way to contact the person and they change their email address?

Postcode is the worse for an individual. As you noted, many people can share
the same postcode.

Of the list given, the Student Number is the only one that I would consider
for a PK candidate. If forced by my boss, I might go with SSN and have my "I
Told You So" card handy to play.

IMHO the best PK is an autonumber. It's unique and does not have a "meaning"
that could change.

 

[Jamie Collins]

Which of the following would NOT be a good primary key?

Student Number
Social Security Number
An e-mail address
A postcode

My answer is postcode, since people might have the same postcode.
WHat does other think?

Ask your teacher whether they meant 'primary key' in the data
modelling sense or the SQL-language meaning of PRIMARY KEY. If they
intended the SQL-language meaning then they are most likely pulling
your leg because it's implementation-specific -- e.g. for Jet it
determines physical ordering on disk -- and the choice is arbitrary
e.g. in Jet do you want to exploit clustering to optimize BETWEEN
queries in which case a postcode (assuming UK) would be favoured, or
optimize concurrency in which case Student Number would be favoured
(assuming values generated close together in valid-time are far apart
in terms of value e.g. a random INTEGER autonumber would probably
qualify but an incrementing INTEGER autonumber almost certainly would
not).

Note that Social Security Number (assuming US) can be duplicated;
further, an email address can be legitimately reassigned to another
person(s). And a postocode is the least vulnerable to identity theft
<g>.

Jamie.

--

 

[lenni]

Thanks all for your help. I really appericated.

Lenni