I'm in the process of updating/optimizing a project that was designed on the fly and has grown rather complex as a result. It's a custom project management system, so the Projects table is the heart of the solution. Other "modules" in the solution include Trouble Tickets, Work Orders, Purchase Orders, Submittals, etc. Each of these has a parent Project.
Currently the system is designed so that there is a group of related table instances for each module, but these modules aren't connected. In other words, there is a group of related tables for the Project screens, one for the Trouble Ticket screens, etc. On the graph, the Trouble Tickets spider does not connect to the Project spider. One of the table instances in the Projects spider is for child Trouble Tickets. One of the instances in the Trouble Tickets spider is for parent Project.
My question is this...when designing a more complex solution, is it better to separate different aspects into their own sets of relationships on the graph or is it better to keep things connected? In the example above, would I be better off leaving things as is or should I consider making a relationship from my Projects spider to my Trouble Tickets spider and get rid of the Project's child Trouble Ticket relationship and the Trouble Ticket's parent Project relationship? By connecting the modules this way, I could eliminate additional redundant relationships, but the end result would be a very complex Projects spider on the graph.
What's the best practice in a situation like this? Are there performance considerations? Do many smaller spiders with fewer relationships perform better/worse than a few big ones with lots of relationships? I'd appreciate any links to other discussions or opinions on this topic. Thanks for the advice!