Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
So I'm working on a new project in order to make our test reports (used in field testing) as part of our Filemaker database for entry and report printing. We have many different kinds of test reports (at least 10-15) that don't really share data other than the Work Order # and by relationship, the customer. Each report would probably have about 10-30 fields in it for data (all text or numerics with very little, if no calculations).
I know there are two schools of thought on this but for performance/development's sake is it better to:
- Create a new Table for each Test Report (along with their accompanying data entry/print layouts with only the pertinent data for that specific report available in the table
- Create a single Table for all test reports, using a surname for each of the fields in order to differentiate which Test Report they are used on.
My inclination is to go the single table route as it keeps the relationships cleaner but my concern is that with every report that is created, there would be a considerable amount of fields that are empty - would this affect performance for us? (For reference, running FMS 12, soon to be 13 on a Mac mini Server with GB ethernet using a Data Separation model).
The goal is that my team would be able to hit (my custom created) button to make a new test report, choose the report type (which will change the layout appropriately) then enter in the data from the data entry layout and be able to print out the accompanying form.
Thanks for your help in advance!