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 (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
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 ?