I am having a hard time conceptually designing tables and queries to accomplish a given situation. The database tracks projects and their associated people and costs.
It takes parsed QuickBooks time and associates it with the correct project. The timelogs include employees and contractors so that hours can be compared. I can't find a good way to incorporate contractors and their different cost structures into what I have now, and to report them in a certain way.
Basic reporting needs include People (worker), Cost/Rate, Hours, and Total Cost.
Tables - Fields:
Employee cost starts at a PeopleID and trickles down to a rate based on a job title:
People - PeopleID, Name
JuncPeopleJobTitles - JPJTID, PeopleFK, JobTitlesFK, DateStarted, DateEnded
JobTitles - JobTitlesID, JobTitle, ICBucketFK
InternalCostBucket - ICBucketID, ICBucketName
InternalCostRate - ICRateID, Rate, StartDate, EndDate
Since people are promoted and change jobs (and costs), the job title is found for a particular date with a query that matches a Work table date to the JuncPeopleJobTitles ID it needs. The query looks the same as Work with an additional JuncPeopleJobTitlesFK. I use it in place of the Work table in nearly every situation.
People is connected to Projects by Work (CodeFK is what they did)
Work - WorkID, ProjectFK, PeopleFK, CodeFK, HoursWorked, Date Worked
Projects - ProjectID, ProjectName, lots of other info...
This works perfectly for employees with different job titles, but what about contractors? Some are hourly, some are fixed, some hours are accounted for with parsed QB timelogs, and some are entered manually.
I'm struggling to create a query that can combine employee and contractor time to look like the Work table with a rate. I haven't even begun to figure out how to combine manually entered contractor hours and rates into the above.
If you can make sense of this, any help would be appreciated.