Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    7

    Question Primary Key. Design Suggestion Requested

    I am creating a table that will hold rates.

    Big picture question is:
    For the PK, do I use an autonumber for the PK, a column that uniquely ID's that record, or a composite Key of multiple fields.

    Columns for this table

    Year (Year that the rate applies)
    Rate (percentage that a person may get)
    Salary (minimum salary for employee to be eligible for rate)


    Couple of issues:
    -This table will be duplicated out to 20 of our sites.
    -This table is updated annually
    -Rates will be the same at the Company headquarters as in the field


    Good database design dictates that the table should have a primary key so that the other tables that depend upon that table can "lookup" the value via a relationship.


    Here is the dilema:
    If I use an autonumber primary key, and insert scripts are sent out to the field, there is no guarantee that recordID 20 equals recordID 20 at corporate.

    If I use a column that uniquely ID's the column, either a GUID, or a named rate like SINGLE-50-20000, that is a little rough to maintain IMHO. and the person that is going to be inputting the data will have to be trained on how to input the rate.

    If I use a composite key, it is not normalized enough, is it?

    Please send all suggestions, none are silly, just want to get other opinions.

    Thanks

    -Rich

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Primary Key. Design Suggestion Requested

    Composite keys may be perfectly normalized. Single-column keys are preferable for their simplicity, that's all. In this case, it would appear that the key for this table could be (year, salary) - i.e. for a given year and minimum salary, the table holds the applicable rate. Introducing a surrogate autonumber key or named code would serve no useful purpose, and would require the addition of a unique constraint to ensure that 2 rates don't get set up for the same year and salary value. Go for the composite, natural key.

  3. #3
    Join Date
    Nov 2002
    Posts
    7

    Re: Primary Key. Design Suggestion Requested

    Thanks Tony, I'm back on track. Funny how a person can get wrapped around the axle on such a small table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •