Hard drives for desktop computers can be divided into two basic flavours: ATA and SCSI. Despite a bewildering array of standards and terminology, each has simply been enhanced over the years for greater flexibility and speed while maintaining the ability for new devices to be plugged into older controllers and new controllers to support older devices.
ATA or AT Attachment is the "correct" name for what is more commonly called IDE. Enhanced IDE or EIDE was Western Digital's implementation of the newer ATA standards. Because of successful marketing, EIDE has become a generic name like Biro and Hoover. The ATA standards include ATA, Fast ATA, Fast ATA-2, Fast ATA-3, Ultra ATA and ATA-4. A related standard is ATAPI which describes how CD-ROMs and tape drives connect to AT controllers. This added functionality has eroded the main advantage of SCSI which was flexibility.
The SCSI standards include SCSI-1, SCSI-2, Fast SCSI, Wide SCSI, Differential SCSI, Ultra SCSI and SCSI-3. Initially SCSI could transfer only 8 bits of data at a time, Wide SCSI doubles this to 16 bits, doubling transfer rate. Differential SCSI uses a different type of cable allowing a total cable length of 25 metres, as opposed to the standard 6-metre length.
Not every change in the standards for SCSI and ATA produced increases in speed. The Ultra standards for SCSI and ATA are meant as bridging standards, Ultra SCSI bridges between SCSI-2 and 3 and Ultra ATA between ATA 3 and 4.
See the chart for an approximate speed comparison of the various standards. (Please note, it is difficult to locate accurate information about the standards, but between the confusion and marketing hype I think I've got it correct.)
Even if you have a controller that can transfer 40Mb/sec of data, the real bottleneck is the clunking, magnetic, mechanical dinosaur you know as your hard drive. Even if it complies with the newer, faster standards, it is unlikely to be able to convert information from magnetic to electronic form at speeds that match the controller.
The key question, of course, is what should I get for my computer? ATA is always better value for money -- the controllers are simpler and many more are sold than SCSI, so they are cheaper to produce. Support for ATA is built into the BIOS of every PC, making ATA much easier to configure.
SCSI is still the premier technology. It is faster, supports more devices on the one controller, and is slightly more versatile (well, you can use it for Scanners too). SCSI controllers usually have an external plug that allows devices to be added to your computer without opening the box.
For a desktop computer, I would go with ATA. For servers and some high-end workstations, SCSI tends to be the preferred option. If you really need performance from your hard drive you could consider RAID -- or Redundant Array of Inexpensive Disks. RAID, despite its name, is not cheap. It achieves superior performance by simultaneously reading or writing to several disks. The term "redundant" is used because some types of RAID use techniques that ensure that if one drive fails no data is actually lost. If you can afford it, RAID is the best choice for servers.