I need a small database for a fraud investigation for payroll regarding an employee. Under normal business rules, one paycheck can only be related to one time card, and one time card can only be related to one paycheck. When processing payroll, one paycheck can have many different pay days (e.g., seven days a week), but one particular pay day can only have one paycheque (Note: I am ignoring exceptions to this which have valid explanations — e.g., two paychecks are issued for the same pay day, one for regular hours only, another for overtime hours only). So, normally there's a one-to-many relationship between paychecks and time cards. It would be a mistake to simply summarize the non-normalized data by making a flat file or spreadsheet listing all paychecks, one per row, and time card details on each row of each paycheck, with a column for each pay day and paycheck detail, as it would cause problems when doing finds or reporting. FileMaker should be used instead.
What if fraud is involved for a particular employee? A paycheck could relate to more than one time card, and a time card could relate to more than one paycheck, a many-to-many relationship.
I resolve the many-to-many relationship by creating two, one-to-many relationships, using a join table called PAYDAYS or TIMECARD_DETAIL. A paycheck can relate to many pay days, but a pay day can only relate to one paycheck. As well, a time card can relate to many pay days, but a pay day can only relate to one time card. I have this ERD:
PAYCHECKS::_kpPayCheckID l-----< TIMECARD_DETAIL:_kfPayCheckID
TIMECARDS::_kpTimeCardID l-----< TIMECARD_DETAIL::_kfTimeCardID
Then I can run a report, or add a TIMECARD_DETAIL portal on each of the TIMECARDS and PAYCHECKS layouts, add the relevant fields to the portals, and see, among other things, which pay days (if any) have more than one paycheck, and which paychecks (if any) are paying for the same pay days. As I mentioned above, the results could reveal that no fraud is occurring at all, or to what extent it is. A very big task when all paycheques spanning over several years need to be investigated.
Am I on the right track? Thanks in advance.