I am working on a FM project for a construction firm.
They need to track task lists for multiple jobs.
Each job will have a list of tasks.
Each job also has a list of job codes, and each task is associated with one of the job codes.
Job codes also have types and sub-types for which I have set up cascading value lists, although I'm not sure that makes a difference to my main question.
For the most part, the layouts are "per-job", the user will choose which job they want to see data for. It's not clear yet if they will want to see data from multiple jobs at once. (I'm assuming that they will, but maybe not.)
When a job is finished, we want to be able to remove the job, all of its tasks, and all of its job codes from the system and archive it away.
My question is whether I should use separate FM files for each job.
In a single-file solution:
-I would have one table for the "job" entity - one record per job.
-all of the job codes would live in a single table and would need a "job" field.
-all of the tasks would live in a single table and would need both a "job" and a job-code field.
-archiving a job would require a script (I guess?) to walk through the task list and copy out any records associated with that job. Likewise for the job-code table.
-deleting a job would require a cascading delete function which would delete the job, and anything associated with that job - i.e., all of the tasks and job codes for that job.
In a multi-file solution:
-each file would have its own table for tasks, and another for job codes.
-I'd need one other file with a master list of current jobs, and I think, a relationship to tables in the individual job files.
-archiving would be a simple matter of moving the individual file
-removing the job from the system would be a matter of removing the link from the master file to the individual files.
-I'm assuming that in order to view data from multiple jobs, I'd need to set up a layout in the master file.
-Switching from a layout in one of the individual job files to another (individual job file) would require jumping to a layout in another file (I think). Is that easy to do? I suppose I could run all layouts from the master file ... not sure if that would be trickier.
The multi-file solution feels cleaner, simpler and less complex to me, but I'd like other opinions before I start diving in.