Say I got this sql schema.
Table Job:
id,title, type, is_enabled
Table JobFileCopy:
job_id,from_path,to_path
Table JobFileDelete:
job_id, file_path
Table JobStartProcess:
job_id, file_path, arguments, working_directory
There are many other tables with varying number of columns and they all got foreign key job_id which is linked to id in table Job.
My questions:
Is this the right approach? I don't have requirement to delete anything at anytime. I will require to select and insert mostly.
Secondly, what is the best approach to get the list of jobs with relevant details from all the different tables in a single database hit? e.g I would like to select top 20 jobs with details, their details can be in any table (depends on column type in table Job) which I don't know until runtime.
Thanks in advance.
Regards,