Janeway stuck on a ship for potentially 70 years and meant to use a dildo or the holodeck
Picard stuck on a ship for 15 years and meant to use a blow up doll or the holodeck
Kirk stuck on a ship for 5 years and meant to use a blow up doll or the holodeck
Archer stuck on a ship for 4 years and meant to use a blow up doll or the holodeck or Porthos
Yeah running Starfleet (where you can be away for years) like a present day military with human 18th century fraternisation rules makes a lot of sense...right?
In one of Archer's alternate timelines, we know what happens. The crew find mates from other places and incorporate them into the crew. And some onboard relationships happen as well amongst the original crew. Starfleet seems pretty relaxed about relationships in the chain of command except for the rare case like Janeway. So apparently it is ok for an XO (Spock and Uhura, Chakotey and Nine, Worf and Dax, Riker and anything that breathed, T'Pol and Tucker. Once you get the captain's pip it's time to get monastic or switch ships (Captain Freeman and Admiral Whatshisname)
I kind of see why Riker remained first officer for so long. Wanting the Enterprise had nothing to do with it.