I honestly believe that it usually detracts from the game. Some games do it well, but often enough it's just tacked on as "more content" or it doesn't mean anything. There's always the way the Witcher series handles it, ie the "Let's do it" method, which is entertaining at least. Personally I liked the way it was handled in Divinity: Dragon Commander, where you pick the princess you marry, for whatever reason (love, political power, wealth), and you get to decide through your dialogue with your wife whether love blossoms forth.