I personally struggle to reconcile realism with a show where sound exists in space and space vessels move as if they were seagoing vessels
The point is that it's not all-or-nothing. All fiction entails
some dramatic license, but that isn't the same as making it total nonsense. It's a question of finding the right balance between plausible and fanciful elements. Ideally, you want to ground a story in enough plausibility that the audience is willing to suspend disbelief about the implausibilities. Indeed, the greater the implausibilities, the more important it is to surround them with believable elements to facilitate the audience's suspension of disbelief. After all, it's called
willing suspension of disbelief, not mandatory. The audience for a magic act isn't going to believe the assistant is levitating if they can see the wires. You have to make the impossibility at least appear convincing.
Piling tons of detailed, real science on that feels like trying to build a skyscraper using the foundations of a sandcastle.
See, that's where you're getting it backward. The science should be the foundation, the starting point. Roddenberry started by consulting with experts to find out what the scientific reality would be, and
then made compromises to the extent that he deemed necessary for the sake of the story, or at least the sake of the budget (for instance using humanoid aliens and Earth-parallel planets because that was the only way the show would be affordable). You have to learn the rules before you can know when it's okay to break them. It was that underlying foundation of credibility that made the more incredible parts acceptable. That was what made TOS and TNG different from the dumber sci-fi shows around them whose creators clearly weren't even trying, and which all pretty much flopped while
Star Trek became legend.
Yes, later Trek productions have seriously eroded the credibility of the franchise, but as I said, I refuse to accept that that's a reason to give up trying and settle for mediocrity. On the contrary, it's a reason to try to do better, and it seems to me that the Secret Hideout shows are doing that now, thanks in large part to Erin Macdonald.
Look -- I've been writing Trek fiction professionally for the past two decades, and I've always striven to make it as scientifically plausible as I could, despite the frequently bad science of the franchise I was basing it on. I've aimed for the high standards Roddenberry aspired to, rather than settling for the lowest common denominator. My entire Trek career is proof that it can be done if you care enough to try.
where relativity doesn't exist
On the contrary, the concept of warp drive arises directly from the equations of the General Theory of Relativity. That's why it's called "warp," because it's based in Einstein's concept of gravitation warping the geometry of spacetime, and the principle that the fabric of spacetime can distort or expand faster than light because it's not a material object. Laypeople mistakenly assume the Special Theory of Relativity is the whole thing, even though it's right there in the name that it's just a special case for unaccelerated motion, the simpler case that Einstein solved first because it was easier.
