Cassino, excellent reply. I'll firstly note that it is odd, your objection to the mutual Ghost-Psychic effectiveness; I was following what you had said... until I played some Pokémon Ruby Version and fought several Duskull at Mt. Pyre with my Kadabra, to find that Psybeam was super-effective. How do you explain that? (The same was true when I later fought a trainer with a Dusklops and a Banette.) I'm not necessarily disagreeing with your assessment of things, however I was intrigued by the battles: they certainly appeared to bear out my former understanding of the Psychic-Ghost relationship. Perhaps this all has something to do with the type effectiveness changes made in Generation II? It seems more and more I find that this has confused many people.
In any case, to get to the matter of Water-types. I agree that, indeed, Water-type Pokémon have only two real weaknesses; however, these weaknesses must be acknowledged as being rather severe weaknesses. Yes, Grass-types are, in general, relatively weak, and but, for example, Electric-types are an extreme danger for Water-types. This can be rectified by a secondary type of Ground, of course, however that simultaneously heightens the threat from Grass-types and, considering examples like Quagsire and Whiskash, lowers speed and attack power.
Water-types also learn comparatively few useful attacks to Pokémon of other types; the most powerful Water-type attacks (arguably Surf, Hydro Pump, Scald, and Dive are the principal contenders) are relatively bland, with few additional effects, and effectiveness against only a select few types. While Ice-type attacks (in general, most useful) can indeed be learned by many Water Pokémon, and thus can be effective in battles against otherwise domineering Dragon-types, Dragon Pokémon can, in my view, be considered a more well-rounded and powerful type, given that most Dragon-types can learn useful Electric attacks such as Thunder and Thunderbolt, which, considering the factors of Water-types' typically-low speed and Dragon-types' typically high speed, would almost certainly lead to any Water Pokémon without Ground as a secondary type being defeated in one move. The same dilemma arises when faced with my preferred types: Psychic and Ghost. Ghost- and Psychic-type Pokémon are typically very fast, and can learn a wide variety of Electric and Grass attacks, as well as powerful moves such as Psychic and Shadow Ball which, despite being neutral in regard to most Water-types, can in most cases win a battle in as few as one or two moves. Factoring in the various debilitating effects of paralysis, confusion, sleep, etc. which the majority of Ghost- and Psychic-type attacks can inflict with ease, I stand by them, for sure.
Psychic- and Ghost-types may indeed be weak against Bug- and Dark-types, but the vast array of moves which are available to be learned by Ghost and Psychic Pokémon allow them to easily overcome these dangers. For instance, a Ghost- or Psychic-type equipped with moves such as Hypnosis and Confuse Ray can quickly incapacitate even Dark types (made simpler by the high speed of Ghost and Psychic Pokémon), and provided moves such as Night Shade, or moves of other types (which, as I say, can be easily learned by many Ghost- and Psychic-types) are known, then Dark and Bug Pokémon needn't be terribly threatening (particularly when one keeps in mind the relatively low defense of Bug- and Dark-types).
I maintain that Ghost- and Psychic-type Pokémon are the two most all-around powerful types, in terms of stats, moves, etc. Dragon is a close third in my book.
