HDs have many different kinds and reasons for failure.
Some manufacturars have had really bad luck.
Some users have tortured HDs with heat.
IBM (now Hitachi) drives had a bad run in the model 75GXP (i think) IDE drives back in 2000 or so. I had several of them and they all failed by 2003. They would go into a seek failure or something and just click incescently.
I have had two modern SATA drives fail in an iMac G5 first generation edition. Those failed due to heat I'm pretty sure.

Most of the data was recoverable by adding the drive to the PowerMac and copying the data to a new drive. Some data was not recoverable.
Asside from that, and I am responsible for about 20 spinning HDs now (up from 10 or so in 1999), I have had no failures that couldn't be overcome by using DiskWarrier and Norton Utilities. In two cases, besides the above, I never used the corrupted volume again. In one of the failures the drive just became very very slow. The other was just messed up and wasn't formattable after I used DiskWarrier to copy the data off it.
My personal recommendation is to go for a SATA II drive with the biggest cache you can find (16MB I believe). Beyond that, go for size, noise, or highest spin rate, depending on your needs. The 10,000 RPM Raptor drives are pretty neat but noisy I understand and low capacity.
Keep an eye out for Native Command Queuing. This is a good feature if supported by your controller card (2 year old feature so should be in new cards). This lets the drive accept a command even as it is still responding to a previous command.
While shopping you can also keep an eye out for the best capacity/$. The best price point moves as the technology gets better. Also keep in mind that your housing is limited to 4 drives and after that you have an expense to add an external drive, additional controller card etc.
I also recommend care when considering any software RAID striping arrangement. This makes the drives MORE fragile and should only be used with meticulous out-of-box backups. RAID mirroring is a good deal but you should test and learn how your system's RAID may be recovered in the case of a failed or removed drive. Some software RAID systems really limit what you can do with one of the two drives from a broken mirror. Also note, software RAID drives are or can be totally useless in a different OS, OS version, or in a different computer. Be careful. Test before you commit your data.
You can get an external drive box and run SATA cables to the box from the Mac-Pro or PowerMac. This is a good way to connect full time drives to your computer. It is faster and more reliable than USB2 or firewire. Beware that there are at least 2 different kinds of SATA cables used for external drives. You you to pay attention.
MacGurus: MultiDrive SATA Enclosure Kits
This is a cool looking SATA card for the Mac Pro. I haven't ever used this model. Feedback would be nice:
MacGurus:Sonnet Tempo E4P PCIe SATA Host Card
If you are using an external box for full time drive operation, make sure it has good ventilation and fans. The external no-fans boxes are good for backups but I recommend not leaving the drives spinning. In fact, unplug all of the connectors for better lightning protection.
I'd go with 500GB 7200RPM Seagate SATA II at this point.
ST3500641AS for $269.95+shipping.
500GB Barracuda 7200.9 SATA Internal Hard Drive See SKU 6757522 Seagate Technology ST3500641AS at PC Connection
Also available cheaper from less well known distributers.
I'd also not use them in a software RAID. If you can get a RAID subsystem that uses hardware RAID and has a decent recovery and migration mechanism then do that. Keep an eye on price though.
YMMV!!!!!
Tadd