Re: How to ID a record

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 08/28/04 

Date: Sat, 28 Aug 2004 13:16:00 -0400

Your questions go to the heart of a "natural" primary keys and "surrogate"
primary keys.

Natural keys are composed of one or more fields in the table that contain
data and that, in combination if more than one, uniquely identify that
record.

Surrogate keys are usually autonumber or other ID type fields.

Which you use depends upon your preferences and what works best for you and
who "mentored" you! I personally use both in my databases, depending upon
how many fields it takes to compose the "natural" key and what I'm doing
with the key field(s).

Google search on these newsgroups will show numerous threads that have
debated this issue. I don't intend to start another one here......
< g > 

-- 
        Ken Snell
<MS ACCESS MVP>
"Ray" <not@needed.com> wrote in message
news:unuMfuQjEHA.2052@TK2MSFTNGP15.phx.gbl...
Hi,
I am wondering about proper or accepted practise for ID'ing records in a
relational database.
My question is this...
If I have a table with a unique feature should I still use an ID column?
Example 1:  (Note: SSN = Social Security Number - assumed unique to every
person)
My database has a table called Employees, it has fields called "id" "fname"
"lname" "SSN" - do I need the "id" field or will the "SSN" field do for
ID'ing the record?
Example 2:
My database has a table called Cars, it has fields called "id" "make"
"model" "registration" - do I need the "id" field or will the "registration"
field do for ID'ing the record?
Now is it sensible to refer to the two tables in a master table like...
Example 3:
My database has a table called CarLog, it has fields called "id"
"employee_SSN" "car_registration" "date"
And again do I need the "id" field or should I index off the
date+SSN+registration?
I am thinking that in these circumstances can I delete the ID field each
time to make the database smaller?  In a lot of cases I can find uniqueness
in the record without an ID field, is this what I should be looking to do
each time?
Any comments appreciated.
Ray,
Dublin, Ireland.
-- 
---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"
Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

Relevant Pages

  • Re: Key attributes with list values was Re: What are the differences ...KEY
    ... Jane Harper is married. ... And a constraint that states that single people cannot become divorced. ... database, or users, for that matter, to distinguish between them. ... That's the whole point of keys. ...
    (comp.databases.theory)
  • Re: Pin generation algorithm question
    ... the keys would be a big ... Suppose that we have a database that contains all valid numbers, ... load among several servers that all need access to this database. ... So the only real problem is which systems are accessing this crypto box. ...
    (sci.crypt)
  • Re: Database design, Keys and some other things
    ... >> Or 'the database has no opinion as what Donald Trump's e-mail address might ... some keys can be wrong or a data can ... Meaning is not related to just one number. ... > is concerned a VIN is not a surrogate key, ...
    (comp.databases.theory)
  • Re: Key attributes with list values was Re: What are the differences ...KEY
    ... database, or users, for that matter, to distinguish between them. ... That's the whole point of keys. ... But that is true of any constraint. ... keys can change, then either updates must be singular, that is, must affect ...
    (comp.databases.theory)
  • Re: Pin generation algorithm question
    ... > You have to secure a number of keys in this instance, ... > tokens in the database with a secret key cipher, or better a keyed hash, and ... Assume that an attacker can monitor requests and observe the ...
    (sci.crypt)
Loading