The application makes heavy use of data. It is a Real Estate Lead Management System each lead could make our users thousands of dollars, and accidentally returning other people's leads would be a sure-fire way to lose our customers trust. We make heavy use of the entity pattern on the client. This requires us to get collections of data and store them as collections on the client. My thinking when I was designing the database was that if I had the account id on each table, that would make getting all of the data easier without returning data from other account, with fewer or more performant queries. I realize that there are other ways to handle this, but we were on a very short deadline and had to build a full app with ~50 tables for a beta launch in 3 Months. That being said, we also have many queries that heavily use joins and group by methods to prevent (n+1) trips to the database. Is it simply that analyzing lots of data is something that requires a larger database? The big problem is that we only have 45 Active customers currently. The app is fast and feels great, we are just pushing the limits of the memory of the database.
This is the pattern that I did. I know it's not normalized for the keys but everything I have read did not seem like it would cause this issue. But I'm not a database specialist and would appreciate any advice.
Account Table: id first_name ...etc Record Table: id account_id address ...etc Analysis Table: id account_id record_id expected_return_on_investment ...etc Comps Table: id account_id record_id analysis_id cost ...etc