Do I need a primary key?

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Database is for a shop that fixes heavy equipment. I have
tables for Jobs, non-stock parts, stock parts, and labor.
The question is about the labor table. Records consist of
the work order #, employee Id, reg hours, reg rate, ot
hours,ot rate description and date. Time must be entered
daily and some of these jobs take weeks so I have a lot of
duplicate records. Is there a way to insert something
like a line number or other id to make a key column? I
was thinking something like a line number by job number
but I do not know how to insert line numbers to tables. Or
do I even need a key? Will I be able to move up to SQL
server without one?

Thanks
SLow
 
OK, so I need a key. Any ideas on how to generate line
numbers that I can store in the table to form a compound
key, or do I just plunk and autonumber field in there.

Thanks
SLow
 
Not sure why you would have any duplicate records in the labor table if
labor is entered daily, and you have date to distinguish each employee's
daily entries, employee ID to distinguish different employees on the same
day, and work order # to distinguish the jobs. Do you charge out an
employee at different rates on the same job/same day for different duties,
perhaps? Do some employees enter partial hours more than once a day?

If not, Date/EmployeeID/WorkOrderNum would seem to form the natural primary
key. If so, add a Date/Time DateCreated column. That in combination with
the other three columns should ensure uniqueness.

It's not uncommon to use an autonumber field for the primary to avoid a
compound primary key, but create a unique index on the fields that form the
"real" primary key to prevent duplicates.
 
The duplicates come from two sources, rush jobs that we
pull someone aside to do and then send them back to the
original job, and anal customers who want a description of
time billed for every single task so they can quibble over
the bill. Many of the machines we fix cost a million plus
new and are proportionately expensive to repair. When
they get a bill for 50K they want to know where it went
and will bicker about 25 bucks. About 10% of the records
still come up duplicate using workorder,date,employee.
Thanks for your advice, I went with the autonumber/index
idea and am changing some data entry procedures.

SLow
 
Back
Top