Was there really a 'revolving door' of writers during the first two seasons of TNG? What was that about? Didn't they realize the episodes would suffer for this?
Gene Roddenberry was increasingly ill and drug-dependent by that point in his life, and he surrounded himself with a circle of supporters, including his attorney Leonard Maizlish, who reportedly shut out the other co-creators and producers, rewrote every script themselves without regard for the others' wishes (in fact it was unethical for Maizlish, who wasn't a Writers' Guild member, to rewrite the scripts), and generally created an unwelcoming environment for them. So one by one, the other original producers decided they couldn't continue to work under those conditions and walked away.
As Roddenberry's health declined further, his involvement in the show became more peripheral and so things were able to stabilize.
Also what was Roger D. Moore's role during TNG, DS9, and VOY?
That's Ronald D. Moore. He began as a freelance writer who sold the spec script "The Bonding" to TNG in its third season, and was soon hired as a story editor, the lowest tier within the writing staff. He worked his way up through the ranks from there: executive story editor in season 4, co-producer in seasons 5-6, and producer in season 7. He then became a supervising producer on DS9 starting with its third season, and by the final two seasons had reached the rank of co-executive producer. After DS9 ended, he briefly made a lateral move to co-executive producer of VGR, but quickly found the working conditions on that staff to be intolerable and left after writing only one episode, "Survival Instinct," and contributing the story to the next, "Barge of the Dead."
You can see how much the hierarchy and taxonomy of producers has expanded since the '60s, when a show typically just had one story editor, one producer, and one executive producer.