Thinking about my database structure and would love some words of wisdom.
I have PROJECTS and Projects contain Messages
Each project will likely have a couple hundred messages.
Would it be better to do the search on the server side for Messages associated with the project?
Or… should I just pull the projects list of messages?
Once it’s launched, the messages data type will have tens of thousands of messages and it seems like searching all messages may be a mistake when the project will only have a few hundred messages - none at the beginning of the project and it’s liked that 90% of projects have sub 100 messages.
Thanks! Yeah, Projects is my main data type actually with over 30 fields. One of them is messages.
I guess the question is, when I am pulling up the projects messages from the project view… Should I “Do a search for messages where Project = This Project”
Or, is it faster at scale to say “This projects list of messages”
Perhaps these resources may help shed some light on dB modelling for performance:
(short answer: “Do a search for messages where Project = This Project”)
Because you expect each project to have hundreds of messages, “do a search for messages where project=this project” will be the most scalable solution.
If each project had <100 messages, the projects list of messages might provide a slight performance advantage, but it will end up being too slow as that list grows beyond 100.
Thanks everyone, I appreciate the insight. I’ve read through a bunch of stuff but it was missing context for me… So I would read - Do a search on the database… unless the list on the object is small. Well… what’s small? lol