Time card relationship diagram
I need some advice on how to integrate a time card table to my current relationship graph.
My filemaker solution has 7 main tables: CUSTOMERS, JOBS, INVOICES, PAYMENTS, EMPLOYEES, TIMECARD and EXPENSES
The JOBS table tracks all service orders placed by a customer
The INVOICES table assigns an invoice to a job so a a payment can be received later once the job is completed
The PAYMENTS table records payments made by the customer once the job is completed. Information contained in this table includes the duration of the job.
The TIMECARD table logs each employee's work hours for a given job. The hours worked by an employee is equal to the duration of the related job
The EXPENSES table keeps track of all business expenses including payroll activities.
Here's an overview of my current relationship graph:
CUSTOMERS -< JOBS (1-MANY|There's a customerID foreign key in the JOBS table)
JOBS -< INVOICES (1-MANY|There's a jobsID foreign key in the INVOICE table)
INVOICES -< PAYMENTS (1-MANY|There's an InvoiceID foreign key in the PAYMENTS table)
EMPLOYEES -< JOBS (1-MANY|There's an EmployeeID foreign key in the JOBS table)
I know that I need a 1-to-many relationship between the EMPLOYEES table and the TIMECARD table.
But how do I automatically report any payment made to an employee as an expense when applying a payment to a customer's account.
P.S.: An employee payment (not to be mistaken with a customer payment) is calculated by multiplying the hours worked (JOB duration) by the rate (determined in the EMPLOYEE table).
Thanks for any help