0

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
  • Sounds like redundant info. Databases frown on redundant info. – Rick James Feb 29 at 0:35
  • Turns out we had a very bad query that ran fast in local, and staging. When we deployed it to production it took 3 hours. This is what ended up stressing the system. – Shawn Pivonka Mar 6 at 15:53
  • So... Are you "closing" this Question? If so, either (1) delete it since it provides no useful info for others, or (2) discuss that 'bad' query and possibly self-answer your question. – Rick James Mar 6 at 16:59
0

Welcome.

There can not be a best and perfect database design (always). It all depends upon your (project) use case, coding simplicity and expandability.

Any best database design will have its own (exclusive) disadvantages too.

Nothing negative about your described design (until you listed - though not all of your ~50 tables). But you shall think of account as a single entity where in the record details goes into the account table itself. Referring

account.first_name, account.address1 account.city

looks good unless your account holders have multiple addresses.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.