AnsweredAssumed Answered

Time card relationship diagram

Question asked by Invectus on Dec 7, 2009
Latest reply on Dec 7, 2009 by philmodjunk


Time card relationship diagram


Hi guys,


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)  


The trouble: 

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