Re: How to ID a record

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 08/28/04 

Date: Sat, 28 Aug 2004 22:23:06 +0100

Hi Ray,

1: Think of it this way:

Is it conceivable that this database will ever have to handle a person
without a SSN (a visitor from abroad, perhaps)?
Given the nature and functions of the database, is it a big deal if
someone provides a false or incorrect SSN?
If a mistake is made when entering a SSN, would it be hard to correct
when discovered months or years later?

If the answer to any of these is yes, it's probably better not to use
SSN as the primary key.

2: Again, it's a matter of the practicalities of the real-world domain
that your database is modelling and the functions it must serve. In many
jurisdictions registration numbers can be re-used or transferred from
vehicle to vehicle. It's also possible for two vehicles of the same make
and model to have the same registration number but in different
jurisdictions: does the database need to handle cars from other
countries or states? And James Bond isn't the only person to have used
false license plates.

3: if the CarLog table stores which employee had which car on which day,
then the fields it needs are EmployeeID (or SSN if that's what you
decide to use as the primary key for the employees table), CarID (or
registration if that's what you decide to use), and the date, all three
with a unique index. You don't need a separate "ID" field.

By the way, don't call fields things like "Date", "Time", "Name": these
are also the names of common properties and/or functions, and the
resulting ambiguity can cause problems.

On Sat, 28 Aug 2004 15:30:54 +0100, "Ray" <not@needed.com> wrote:

>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. 

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.

Relevant Pages

  • Re: is there an equivavlent to auto_increment in ingres ?
    ... search requires more than two probes, no matter how large the database. ... the gap in the sequence is not filled in and the sequence ... ssn CHARNOT NULL REFERENCES Personnel, ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (comp.databases.ingres)
  • Re: Table Design Question
    ... no matter how large the database. ... the gap in the sequence is not filled in and the sequence ... ssn CHARNOT NULL REFERENCES Personnel, ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: MD5 question
    ... I have a database of employee information used at a company I ... > I have a SSN field with of course each employee's SSN. ... > that two different SSN's would generate the same MD5 hash? ...
    (sci.crypt)
  • Re: SSN encryption
    ... >> We want to encrypt social security numbers in a database. ... address and SSN are always excluded. ... exposed if there were a breakdown in the other security precautions. ...
    (sci.crypt)
  • Re: Critique of Robert C. Martins "Agile Principles, Patterns, and Practices"
    ... SSN, by department, by city.... ... I'd make sure that a well structure SQL statement was used against a well-normalized database. ... Then I would hide that SQL statement behind an interface so that the rest of my application was unaware of it. ... employee where SSN=?" ...
    (comp.object)
Loading