I need to build a database, and I'm not sure what structure to use (what data to put in separate tables, or one table, or something else...). I thought maybe I could get some suggestions based on the data I need to store and access:
-Pool of about 1,000 funds. Each fund has a score, and new data is imported every quarter.
-List of about 100 clients. Each client has about 40 funds in its portfolio that come from the Pool of funds (clients can have the same funds).
So each quarter, I will import the same 1,000 funds (maybe a few extra/less) with new scores into the database. I then need to generate a report for each Client that shows the funds they have and what their new scores are. I also need the ability to look at data from past quarters.
So how should I do this? Should I have 1 table with all the funds, and add 1,000 rows to it every quarter? Or should I make a new funds table every quarter, and call it "Funds QX 20XX"? Or should I have 1 table for each Fund, and add a new row to each one every quarter with the new score? I need help! lol