无法为包含多个邮政编码的月度报告设计ERD

Question

This is an ERD for a food bank system. I'm unsure how to handle zip codes at the moment. I'm not sure on necro policy here, but here's my old question just in case: Need help designing ERD for food bank

Ok so the food bank gives food out to agencies. Each month the agency fills out a report like this one: http://communityfoodbank.net/AgencyAccess/SubmitAReport.aspx They need to know how many people were fed from each zip code. Now, this part is all counted outside of this system. They serve food 4+ times a month, tally it, and submit this report. I want to save the stats in the system.

How should I go about this? It was suggested in my original question that I make zipcode part of the PK on MonthlyReport table. However with all the agencies and all the zip codes they serve I imagine that would be rough on the database. Is this still recommended with new information in mind?

How would I store a monthly report and log a number served for each zip code, keeping in mind more zip codes will likely be added as the agents grow.

Update

With this current setup, the MonthlyReport table will generate over 9000 rows a month. 30 rows per month per agency. If I write the reports to an excel file it will create about 600 files monthly. I can't figure out the best way to do this.

Answer 1

This looks good. Don't worry about being "rough on the database"

With this design, You will need to get hundreds of thousands of rows before you see a noticeable performance hit (like > 1 second).

You definitely want the zip codes on the Reporting table. In a normal design it would be attached to the agency, but for reporting, you need historical accuracy. This prevents problems when an agency adds or drops a zip code.

You could have the current zip codes tied to the agency, and copy them to the monthly report via trigger or manually.

I don't see any other issues here.