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