I'm currently developing an inventory and POS system, and I have a few files that store the financial transactions: purchase (inventory reduction), orders (inventory increase), and one for miscellaneous financial transactions (supplies, copies, donations, etc.). I've created a reports file to show the end-of-month and YTD financial summary, which is related to the above files. But, I would like to show a monthly purchase summary, and I'm not sure if this should go in the reports file or the purchase file. Since these two reports are different from each other, my sense is that if they both go in the reports file each report will have a bunch of extra fields that don't pertain to it, which probably isn't good database design.

So, my general question is, should reports go in a separate file or the file that has all/most of the data? Or is there a rule of thumb for when a report should be in a separate file?

Main factors I consider:

1) what are the relationships between the files the info is in? (1-to-many, many-to-many, etc.)

2) how hard is it to process information from this file vs from other files?

One generality: if the relationship from file 1 to file 2 is one-to-many, I usually put the report in file 2.

Modern DBs store data using sparse array techniques. A record with most fields empty occupies less memory and disk space than one with data in most fields. So extra fields do not radically increase resource usage. However, making a separate file to store this one-to-one related data actually increases resource usage because of extra disk thrashing, memory usage, and CPU time resolving relationships.

Conclusion/general rule: When reports share the same relationships to other files, put them in the same file.