4

I'm designing a payroll system I have designed a database with several tables and this question is related to following tables

EMPLOYEE (Employee_ID [pk], Name,... etc) LOCATION (Location_ID [pk], Name, Address, ...etc)

These two tables have many to many relationship as one employee can work in several locations and in a location, several employees are working.

These two tables are related through ATTENDANCE table.

ATTENDANCE (Employee_ID, Location_ID, Shift_Type, In, Out)

Shift type would be either "Day" or "Night" and "In" / "Out" fields are DateTime fields. The shift type is decided based on the date in the field "In" . For example if an employee's In field is lets say -06/22/2014 07:30:00 PM then the shift type is Night and shift is belongs to 22/06/2014. (his Out time would be 23/06/2014 07:30AM; that is on next day)

THE CONDITION is that, an Employee can't work same shift on same date twice even in two locations. But an employee can work Day in a location and Night in another location.

My initial primary key selection ATTENDANCE table was (Employee_ID + In) but that is wrong as an employee can work same shift in same day if the In time is slightly different in two occasions.

So now I know the primary key should be some thing like (Employee_ID + Shift_Type + Shift_Date). But the problem is I don't have a Date field in the ATTENDANCE table. But I have In field which is a DateTime field.

Hopping that my understanding about the issue is correct...,

My question is, with out adding another field to ATTENDANCE table as Shift_Date, is there a possibility to get the Date part of the field In, when creating a PRIMARY KEY for this table ?

Improve this question
3
  • It's my personal opinion that composite primary keys are a terrible pain to deal with and they cause more problems than they solve.
    – Dan Pichelman
    Commented Jun 22, 2014 at 19:19
  • 3
    I would create an auto number Attendance_ID field as my primary key then consider using constraints to enforce the business rules to ensure one employee isn't in two places at the same time.
    – Dan Pichelman
    Commented Jun 22, 2014 at 19:20
  • A primary key is the wrong tool. The purpose of a primary key is to establish identity. It enforces uniqueness, sure, but that's just so that identities don't become indistinct.
    – Walter Mitty
    Commented Jun 23, 2014 at 11:26

1 Answer 1

Reset to default
8

You can use a date field as a primary key, but you can't key on PART of a field. You'd have to make a separate field with just the date.

This particular condition isn't something that would normally be enforced by building it into the primary key of a table anyway. Your "Employee can't work same shift on same date twice even in two locations" is a business rule, something that isn't necessarily going to be the same forever. (Goodness knows, there are entirely too many places out there that make their employees do split shifts...) A more typical way to handle this would be to just generate an auto-increment field for the primary key, and then enforce your business rules somewhere closer to the user interface.

Improve this answer
2
  • 3
    Well said. A primary key is there to uniquely identify a record, not to enforce a business rule.
    – Carson63000
    Commented Jun 23, 2014 at 4:46
  • And who says an employee can't work the same shift more than once in a day? Some disaster comes along, everyone leaves. It becomes safe again, they return. Or in a small town, there's a fire and some employees dash off to the fire station.
    – Loren Pechtel
    Commented Jun 23, 2014 at 5:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.