about SCSI, i've used some in a server, and two things: they are fast, and they are expensive. Also, unless you have a high-end server motherboard, you need a SCSI PCI adapter card, which alone could cost well over $150.
Even the fastest SATA2 drive, the WD Raptor that spins at 10k rpm, can't keep up with a high-performing SCSI. Some of them can go 15k rpm. but unless you're filthy rich, SCSI is not an option for you.
Between IDE and SATA, go with SATA. the SATA2 300 is a bit of a gimmick, coz even the fastest drives can't come close to saturating the SATA 150 bandwith, but think of it more like in future, it would be good to have up to 300mb/sec bandwith. So make sure your board has SATA2 connectors, but if buying a drive, there is no real difference in performance between SATA1 and SATA2 at the moment.
And forget IDE. The good drives with 16mb cache are quick, but the cables alone are reason to go with SATA instead, especially if you have a clear side panel and want a good looking system inside with good airflow.
two main things to watch for when picking a drive is rpm speed, and mb cache. the more of each, the better.