DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Sequential Primary Keys for every table in DB

  1. #1
    Amy Guest

    Sequential Primary Keys for every table in DB


    What is the consequence of having a sequential id for every table in the database
    even though there is a field that is a good candidate for a primary key (like
    a social security number for a customer)?

    What is the benefit?


  2. #2
    Some Dude Guest

    Re: Sequential Primary Keys for every table in DB


    My 2 cents...

    Let's look at your example of using a social security number for the primary
    key in a table...

    1) lets add a customer with SSN 111-11-1111...

    tb_customers
    ----------------------------------
    customer_id = 111-11-1111
    first_name = bob
    last_name = smith

    2) lets add a phone number to the phone table for that customer

    tb_phones
    ----------------------------------
    phone_id = 1
    customer_id = 111-11-1111
    area_code = 555
    phone_number = 555-5555


    Now... In this scenario if you find out at some point that the SSN was input
    wrong, then you have to update it in the tb_customers table AND the tb_phones
    table... that may not seem like that big of an issue, but imagine the customer_id
    being a foreign key in hundreds of tables... then it becomes a major problem.

    If you had used a sequential id to represent the customer as the customer_id,
    then you could just change the SSN field and leave the rest of the tables
    alone...

    Another benefit of using sequential numbers in each of your tables to identify
    the records is that you allow yourself the ability to change any of the interface
    fields (fields that might be displayed in a report or an application) at
    any time and not have to worry about messing up the integrity of your data...

    Some Dude




    "Amy" <akotsmith@hotmail.com> wrote:
    >
    >What is the consequence of having a sequential id for every table in the

    database
    >even though there is a field that is a good candidate for a primary key

    (like
    >a social security number for a customer)?
    >
    >What is the benefit?
    >



  3. #3
    Missy Guest

    Re: Sequential Primary Keys for every table in DB


    "Some Dude" <some@dude.com> wrote:
    >
    >Now... In this scenario if you find out at some point that the SSN was input
    >wrong,


    My two bob worth...

    WOULD YOU NOT BE ABLE TO USE CASCADE UPDATE TO CORRECT SUCH AN ERROR?

    Are you talking about using AUTOMATICALLY sequential keys as the identifier
    for every table?

    My personal opinion is that identifiers are best left as natural as possible
    thereby greatly assisting the users (DBA's) comprehension of the whole database.

    In summary I do not beleive there is any value in simply adding sequential
    indentifiers to EVERY table of the database...NO.

    Identifiers are (once again in my opinion) THE most important thing in the
    relational database and as such they require a little more effort in consideration.


    Consider for example the differences between OBJECTS and EVENT tables;

    Example:

    Object being a VEHICLE
    Event being a TRIP

    For both of these forms of data it is possible to find a natural key, something
    that will uniquely identify it among all other possibilities;

    VEHICLE - Rego Plate, or perhaps Chasis VIN Number, & Engine Number

    (in actual fact these are all a little simplified, in the vehicle industry
    you would need a combination of identifiers to pin a second hand vehicle
    down, but in a non specialised database a Registration Plate would normally
    suffice)

    TRIP - Date & Time Started, Data & Time Ended, Where Commenced, Destination...
    Stops along the way etc etc

    With events it all becomes a little more cumbersome and this MAY indicate
    (depending on the DBA's better knowledge of a situation, the physical resources
    of the system and the frequency of calls to that table) that perhaps a surrogate
    key would be more useful.

    TRIP 00000001
    TRIP 00000002

    Remember still, surrogate keys need not be sequential. And SSN is in fact
    a Surrogate Key for a PERSON. Strickly speaking HUMAN natural keys are probably
    EITHER an equally long list of birth event based identifiers (DOB, Parents,
    Place) or something wonderfully, but to date not so tangible as DNA!

    Amy,

    You may also want to brush up on the difference between KEYS and INDEXES...perhaps
    there is some misconception between the two that is confusing the advice
    you have recieved.


    Missy

    >
    >"Amy" <akotsmith@hotmail.com> wrote:
    >>
    >>What is the consequence of having a sequential id for every table in the

    >database
    >>even though there is a field that is a good candidate for a primary key

    >(like
    >>a social security number for a customer)?
    >>
    >>What is the benefit?
    >>

    >



  4. #4
    David Satz Guest

    Re: Sequential Primary Keys for every table in DB

    great article on this:
    http://www.sqlmag.com/Articles/Index...rticleID=23449

    not sure if it is secured since I am subscriber; otherwise, there are
    numerous others:
    http://www.google.com/search?q=natur...l=en&lr=lang_e
    n&ie=UTF-8&newwindow=1&start=10&sa=N

    "Amy" <akotsmith@hotmail.com> wrote in message
    news:3e47ff3e$1@tnews.web.devx.com...
    >
    > What is the consequence of having a sequential id for every table in the

    database
    > even though there is a field that is a good candidate for a primary key

    (like
    > a social security number for a customer)?
    >
    > What is the benefit?
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center