The C4S is probably the looker of the 996 family IMHO, but tastes vary. Faults with the whole family are relatively rare compared to most other performance cars, but there are a couple of things to be aware of, the IMS bearing is worth replacing if you ever need to replace the Rear Main Seal (which can leak) or the clutch (if fitted) with a ceramic unit for peace of mind.
The Turbo is probably less likely to explode, but more likely to consume fuel. Equally pretty to the C4S, but in a more aggressive kind of way.
Either is a great car to own, I have a C4s and do eye up the Turbo from time to time but very happy with what I have.
You also have convertible and Targa options to consider (but no Targa for the C4S or Turbo) in the 996 family, and again IMHO I think a high spec'd Targa could be a rare and valuable car in the future, they are also great cruising Porsches... As are the convertibles.