I have three tables: cost per hour, cost per travel and direct expenses
Cost per hour and cost per travel have each of them four columns: employee, worked hours, price per hour and total cost.
I calculated the total cost as worked hours*price per hour. I then added a query for total cost as being SUM of all the employes total cost in the cost per hour and cost per travel tables.
I need to extract this total cost from the two tables in the direct expenses table and I want the values in direct expenses table to be automatically added, updated, deleted when I modify the total cost in each of the two tables cost per hour and cost per travel.
My question is which method is the best to achieve this: master/child relationship with foreign key in the mysql database, inner join of the two tables or the Insert a record into another table script form phprunner manual.
I used the Insert a record into another table script but there is a problems: if i edit a record in one table then the record in the other table is not updated or deleted but inserted on a new row...