Detective mostly shines in rotational play, especially when your co-workers are all sims from other played households. Played households can also get selected as crime scenes by the game, but the actual crime is kept so vague that the mechanic feels immersive and imagination-fueling to me. The game won't trample on your story. (Saying this as a very controling player).
For a single household the career can be fun, too, but probably getting repetitive. I mainly voted for this because I like the idea of a college-trained detective who has never walked the beat and will have to prove themselves to their co-workers. You start at a higher salary, but will have a high requirement of cases to solve for your next promotion.